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

Passing Variables as Parameters in Execute SQL Task in SSIS

Executing dynamic SQL operations often necessitates passing variables as parameters to make queries more versatile. In an SSIS package, you encounter such a scenario when you need to pass external values to a temporary table creation query in the Execute SQL Task.

Question:

Consider an SSIS package where data from a flat file is inserted into a table. Within this package, an Execute SQL Task is utilized to create a temp table called ##temptable with specific columns. Following this, data is inserted into ##temptable based on certain filter criteria. The requirement is to pass these filter criteria dynamically using variables created within the SSIS package.

Solution:

To achieve dynamic parameter passing, configure the Execute SQL Task as follows:

1. Set the SQLSourceType to "Direct Input."
2. In the SQL Statement, specify the stored procedure name along with question marks for each parameter, for example:

CREATE PROCEDURE [dbo].[usp_temptable]
(
    @date DATETIME,
    @portfolioId INT,
    @stype NVARCHAR(50)
)
AS
BEGIN
    INSERT INTO [tempdb].dbo.##temptable (date, companyname, price, PortfolioId, stype)
    SELECT   date, companyname, price, PortfolioId, stype
    FROM        ProgressNAV
    WHERE     (Date = @date) AND (PortfolioId = @portfolioId) AND (stype in (@stype))
    ORDER BY CompanyName
END

3. Click on the parameter mapping option and establish the connection between each stored procedure parameter and the corresponding SSIS variable.
4. Configure the SSIS variables with the desired values that will be passed to the stored procedure during task execution.

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