How to Pass SSIS Variables as Parameters in an Execute SQL Task?

Passing Variables as Parameters in SSIS Execute SQL Task

You're working with an SSIS package that imports data from a flat file into a table. You've included an Execute SQL Task to create a temporary table.

The challenge arises when you need to pass specific criteria as parameters to the SQL statement within the Execute SQL Task. Instead of hardcoding the values in the statement, you want to make it dynamic by using variables defined in the package.

Solution:

To pass variables as parameters in the Execute SQL Task, follow these steps:

1. Open the Execute SQL Task editor.
2. Set the SQLSourceType property to Direct Input.
3. In the SQL Statement field, enter the SQL statement for creating the temporary table, but replace the hardcoded criteria with question marks (?). For example:

CREATE TABLE [tempdb].dbo.##temptable (
date datetime,
companyname nvarchar(50),
price decimal(10,0),
PortfolioId int,
stype nvarchar(50)
)

Insert into [tempdb].dbo.##temptable (date,companyname,price,PortfolioId,stype) 
SELECT   date,companyname,price,PortfolioId,stype
FROM        ProgressNAV
WHERE     (Date = ?) AND (PortfolioId = ?) AND (stype in (?))
ORDER BY CompanyName

4. Select the Parameter Mappings tab in the left sidebar.
5. Click the Add button to add new parameter mappings.
6. For each parameter (Date, PortfolioId, and stype in this case), select the corresponding SSIS variable that holds the dynamic value.
7. Click OK to save the Execute SQL Task.

When the task runs, the SSIS variables will be passed as parameters to the SQL statement, enabling you to dynamically filter the data in the temporary table based on the criteria you specify.

The above is the detailed content of How to Pass SSIS Variables as Parameters in an Execute SQL Task?. For more information, please follow other related articles on the PHP Chinese website!