ssis variable

Question

Post reply

ssis variable

Aviad C.

Mr or Mrs. 500

Points: 542
More actions
June 16, 2010 at 12:22 am

#222866

hi, how can I use a variable in ssis. I try to do this code in ssis :
set @app = (select Application from dbo.Table_1 where [db]='aa1')
I declare a variable is ssis type string, but I don't know how to insert him a value from this transact sql.
thank's on the help, aviad.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Aviad C. Mr or Mrs. 500 Points: 542 More actions · Answer 1

hi, I try agin to be more specific...

I Insert a value in my variable called - app.

now, i want to exec this sql statement, that use this variable app :

declare @STR nvarchar(4000)

set @STR = ' if ''Application'' in (select name from sys.extended_properties)'

+' EXEC sp_updateextendedproperty @name=''Application'',@value='''+@app+''';'

+' else EXEC sp_addextendedproperty @name=''Application'',@value='''+@app+''';'

exec @STR

how can I do that ?

thank's

sabotta SSC Eights! Points: 818 More actions · Answer 2

You can use the Execute SQL Task to run a parameterized SQL statement. You configure the task to specify the SQL Statement with the appropriate parameter marker (depends on the connection type being used) and to map the parameter name (again, depends on the connection type) to an SSIS variable.

For instructions on how to configure the Execute SQL Task to work with parameters, and for sample SQL commands, see http://msdn.microsoft.com/en-us/library/cc280502.aspx .

chamchamsaharahman Newbie Points: 9 More actions · Answer 3

hi, how can I use a variable in ssis. I try to do this code in ssis :

set @app = (select Application from dbo.Table_1 where [db]='aa1')

I declare a variable is ssis type string, but I don't know how to insert him a value from this transact sql.

thank's on the help, aviad.

Mercedes Benz CL500 Parts

sabotta SSC Eights! Points: 818 More actions · Answer 4

If I understand you're question correctly, you want to insert a value stored in your SSIS variable (app), into the TSQL statement. I'm assuming that you're using Execute SQL Task to run the statement.

You need to replace @app with a parameter marker in the SQL statement, and add that statement in the General page of the Execute SQL Task Editor. The type of parameter marker that you use depends on the connection type that the Execute SQL Task is configured to use. In the "Working with Parameters and Return Codes in the Execute SQL Task" topic (http://msdn.microsoft.com/en-us/library/cc280502(SQL.100).aspx), see the table in the Using Parameter Names and Markers for examples.

Then, you need to specify a name for the parameter and map the SSIS variable to the parameter name, in the Parameter Mapping page of the Execute SQL Task Editor. The paramenter name that you specify depends on the connection type that the Execute SQL Task is configured to use. In the "Working with Parameters and Return Codes in the Execute SQL Task" topic (http://msdn.microsoft.com/en-us/library/cc280502(SQL.100).aspx), see the table in the Using Parameter Names and Markers for examples.