SSIS - How to Pass Parameter values to Sub Query in Execute SQL Task in SSIS Package

Scenario:
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.

Solution:
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
        )


Step 1:
Create a new SSIS Package, After creating Package, create two variables as shown below.
How to create variables in SSIS Package



Step 2:

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



Step 3:
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
You are all set. The query is going to use the variable's values at run time. 

4 comments: