We want to execute SQL Query in Execute SQL Task but before we do that we need to pass
parameter/parameters values to Inner Query or Sub Query.
Lets say here is the query we want to run in Execute SQL Task
SELECT id1 FROM table1 WHERE id1 = ( SELECT id2 FROM table2 WHERE col1 = @VarParamter1 ) AND id3 = ( SELECT id4 FROM table3 WHERE col2 = @VarParameter2 )
Create a new SSIS Package, After creating Package, create two variables as shown below.
|How to create variables in SSIS Package|
Drag Execute SQL Task and configure as show below. Select 1 is just dummy value. It will be replaced with value evaluated by expressions at run time.
|Provide Connection Manager, Connection Type and SQL Statement "Select 1" in Execute SQL Task in SSIS Package|
In this step, you need to go to Expressions in Execute SQL Task. Here we are going to build our SQL Query. We will use two variables those we have created to use in sub query.
|Write Expression on Execute SQL Task for SQL statement and use variable values to pass to sub query in SSIS Package|