How to pass variable as a parameter in Execute SQL Task SSIS

Question

I have ssis package in that I m taking values from flat file and insert it into table   I have taken one Execute SQL Task in that creating one temptable   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    2011-09-30   AND  PortfolioId   5  AND  stype in   Index    ORDER BY CompanyName   Now in above query I need to pass  Date    2011-09-30   AND  PortfolioId   5  AND  stype in   Index    these 3 parameter using variable name I have created variables in package so that I become dynamic

User · Answer

SELECT  INSERT  UPDATE  and DELETE commands frequently include WHERE clauses to specify filters that define the conditions each row in the source tables must meet to qualify for an SQL command  Parameters provide the filter values in the WHERE clauses   You can use parameter markers to dynamically provide parameter values  The rules for which parameter markers and parameter names can be used in the SQL statement depend on the type of connection manager that the Execute SQL uses   The following table lists examples of the SELECT command by connection manager type  The INSERT  UPDATE  and DELETE statements are similar  The examples use SELECT to return products from the Product table in AdventureWorks2012  that have a ProductID greater than and less than the values specified by two parameters   EXCEL  ODBC  and OLEDB   SELECT  FROM Production Product WHERE ProductId  gt    AND ProductID  lt      ADO    SELECT   FROM Production Product WHERE ProductId  gt    AND ProductID  lt      ADO NET  SELECT  FROM Production Product WHERE ProductId  gt   parmMinProductID       AND ProductID  lt   parmMaxProductID   The examples would require parameters that have the following names  The EXCEL and OLED DB connection managers use the parameter names 0 and 1  The ODBC connection type uses 1 and 2  The ADO connection type could use any two parameter names  such as Param1 and Param2  but the parameters must be mapped by their ordinal position in the parameter list  The ADO NET connection type uses the parameter names  parmMinProductID and  parmMaxProductID

User · Answer

In your Execute SQL Task  make sure SQLSourceType is set to Direct Input  then your SQL Statement is the name of the stored proc  with questionmarks for each paramter of the proc  like so     Click the parameter mapping in the left column and add each paramter from your stored proc and map it to your SSIS variable     Now when this task runs it will pass the SSIS variables to the stored proc

User · Answer

Along with  PaulStock s answer  Depending on your connection type  your variable names and SQLStatement SQLStatementSource Changes    https   docs microsoft com en-us sql integration-services control-flow execute-sql-task

User · Answer

The EXCEL and OLED DB connection managers use the parameter names 0 and 1   I was using a oledb connection and wasted couple of hours trying to figure out the reason why the query was not working or taking the parameters  the above explanation helped a lot Thanks a lot

User · Answer

A little late to the party  but this is how I did it for an insert   DECLARE  ManagerID AS Varchar  25     NA  DECLARE  ManagerEmail AS Varchar  50     NA  Declare  RecordCount AS int   0  SET  ManagerID     SET  ManagerEmail     SET  RecordCount      INSERT INTO

[sql] How to pass variable as a parameter in Execute SQL Task SSIS?

Examples related to sql

Examples related to ssis