none
Change a value of a variable

    Question

  •  

    Hi,

     

    I have a simple question: after an error I want to change the value of a variable.

    Since there is NOT a clear way to do it I was using a workaround:

     

    a Execute SQL Task with the following parameters:

     

    Connection Type = OLE DB

    Sql Source Type = Direct Input

    Sql Statement     = Select ? =1

     

    And I was using the variable as Return Value parameter.

    In this way it worked fine.

     

    Now I want to use an ADO.NET connection.

    I have tried in serveral way but I haven't been able to reach the goal.

    Does a way exist?

     

    Thank you

     

    Antonio

    Wednesday, April 16, 2008 5:02 PM

Answers

  • Hi,

     

    to solve the issueI had to use the following configuration:

     

    Execute SQL Task with the following parameters:

     

    Connection Type =  ADO .NET

    Sql Source Type = Direct Input

    Sql Statement     = Select @PIPPO =1

     

    And I used the parameter PIPPO as OUTPUT parameter instead of Return Value parameter.

    In this way it worked fine.

    Using the OLE DB connection the parameter was a Return Value parameter ?!?

     

    Thank you all!

     

    Antonio

     

    Thursday, April 17, 2008 8:06 AM

All replies

  • Have you tried using @ParamName instead of ? as the parameter placeholder? Or you could use SingleRow as the Resultset Type, use SELECT 1 as the SQL Statement, and map the result column to the variable on the Result Set page.

     

    Wednesday, April 16, 2008 5:58 PM
    Moderator
  • Hi,

    I tried both ways but they didn't work.

    Thanks

     

    Antonio

    Thursday, April 17, 2008 7:03 AM
  • if possible, i prefer to use ssis expressions to build my parameterized sql statement.  that way, the sql statement is easier to implement, debug and maintain regardless of the connection manager employed.

     

    hth

    Thursday, April 17, 2008 7:15 AM
    Moderator
  •  

    For the input parameter an expression derived SQL Statement my be an alternative, but be aware of the limitations of each method, I’ve added some notes below. For the output to apply to the variable you obviously need a return parameter, output parameter, or result set. There are differences in how you should address these for different connection types, so changing to ADO.NET means you use names as noted by John.

     

    Have a read of the Books Online topics that covers this in more detail –

     

    Working with Parameters and Return Codes in the Execute SQL Task

     

    Working with Result Sets in the Execute SQL Task

     

     

     

    Expressions vs Parameters (Wiki)

     

    Expressions

    More flexible in that they can parameterise anything, this is just string concatenation. No limitations on what and where the replacement goes, so can parameterise table names.

     

    Parameters

    Not a SQL injection risk as with string concatenation of expressions.

    Not limited to overall length of 4000 characters, the maximum length of an expression, which will include data values.

    Thursday, April 17, 2008 7:36 AM
    Moderator
  • Hi,

     

    to solve the issueI had to use the following configuration:

     

    Execute SQL Task with the following parameters:

     

    Connection Type =  ADO .NET

    Sql Source Type = Direct Input

    Sql Statement     = Select @PIPPO =1

     

    And I used the parameter PIPPO as OUTPUT parameter instead of Return Value parameter.

    In this way it worked fine.

    Using the OLE DB connection the parameter was a Return Value parameter ?!?

     

    Thank you all!

     

    Antonio

     

    Thursday, April 17, 2008 8:06 AM
  •  AntonioCH wrote:

    Hi,

     

    to solve the issueI had to use the following configuration:

     

    Execute SQL Task with the following parameters:

     

    Connection Type =  ADO .NET

    Sql Source Type = Direct Input

    Sql Statement     = Select @PIPPO =1

     

    And I used the parameter PIPPO as OUTPUT parameter instead of Return Value parameter.

    In this way it worked fine.

    Using the OLE DB connection the parameter was a Return Value parameter ?!?

     

    Thank you all!

     

    Antonio

     

     

    hence, my preference to use expressions instead.  fewer headaches and less fuss.

    Thursday, April 17, 2008 8:15 AM
    Moderator
  • I couldn't use the expression in the variable configuration because I wanted to change the variable value after an exception.

    So I was forced to use a dummy component to change the variable value.

     

    To be more precise I used this variable to avoid to log many errrors in the db since SSIS when there is an exception raises several linked exceptions.

    So, after each exception, in the event handler I check the error log variable and if it is 0 I log the error and I set the variable to 1. 

     

    It is simpler to show than to explain :-)

     

    Best Regards

     

    Antonio

     

    Thursday, April 17, 2008 12:51 PM
  •  AntonioCH wrote:

    I couldn't use the expression in the variable configuration because I wanted to change the variable value after an exception.

    So I was forced to use a dummy component to change the variable value.

     

    To be more precise I used this variable to avoid to log many errrors in the db since SSIS when there is an exception raises several linked exceptions.

    So, after each exception, in the event handler I check the error log variable and if it is 0 I log the error and I set the variable to 1. 

     

    It is simpler to show than to explain :-)

     

    Best Regards

     

    Antonio

     

     

    why was it necessary for you to implement a dummy component?  the almighty script task can be used to change variable values at run-time.

    Friday, April 18, 2008 6:15 AM
    Moderator
  • Dummy component because I am using a select to change the value of a variable...

    So it is a Execute SQL component that is used as a workaround and not for its real purpose.

    I agree thta another solution is the Script Component.

    Best Regards

     

    Antonio

     

    Friday, April 18, 2008 8:12 AM