SQL Variable And IS Variable In Execute SQL Task

Hi,

 

I have an Execute SQL Task (OLE DB Connnection Manager) with a SQL script in it. In this script I use several SQL variables (@my_variable). I would like to assign an IS variable ([User::My_Variable]) to one of my SQL variables on this script. Example:

 

DECLARE @my_variable int

, <several_others>

SET @my_variable = ?

<do_some_stuff>

 

Of course, I also set up the parameter mapping.

However, it seems this is not possible. Assigning a variable using a ? only seems to work in simple T-SQL statements.

I have several reasons for wanting to do this:

- the script uses several variables, several times. Not all SQL variables are assigned via IS variables.

- For reading and mainenance purposes, I prefer to pass the variable only once. Otherwise every time the script changes u need to keep track of all questionmarks and their order.

- Passing the variable once also makes it easier to design the script outside IS using Management Studio.

- This script only does preparation for the actual ETL, so I prefer to keep it in one task instead of taking it apart to several consecutive Execute SQL Tasks.

- I prefer to use the OLE DB connection manager because it's a de facto standard here.

 

Could anyone help me out with the following questions:

- Is the above possible?

- If so, how?

- If not, why not?

- If not, what would be the best way around this problem?

 

Thanx in advance,

Pipo