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

Passing Variables as Parameters in Execute SQL Task SSIS

Involving multiple database operations within an SSIS package often necessitates the dynamic setting of SQL parameters. Execute SQL Task in SSIS provides a convenient method for achieving this.

Suppose you have an SSIS package that retrieves data from a flat file and inserts it into a database table, utilizing an Execute SQL Task to create a temporary table using a parameterized query. You aim to make the query dynamic by passing parameters that specify date, portfolio ID, and stock type as variables.

To accomplish this within the Execute SQL Task:

1. Set SQLSourceType to Direct Input

This indicates that the SQL Statement property will directly specify the SQL query.

2. Define Variable Parameters in SQL Statement

Modify the SQL Statement to use question marks (?) as placeholders for the parameters. For instance:

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

3. Map Variables to Parameters

In the Parameter Mapping section of the Execute SQL Task editor, add each parameter from the SQL Statement. Then, map each parameter to its corresponding SSIS variable:

| Parameter | SSIS Variable |
|---|---|
| Date | @Date |
| PortfolioId | @PortfolioId |
| stype | @Stypet |

4. Run the Task

Upon execution, the Execute SQL Task will inject the values assigned to these variables into the query, ensuring the appropriate data is retrieved and inserted into the temporary table.

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