SSIS - How To Pass input parameter in Execute SQL Task

This blog will explain how to create a variable and how to pass it as input parameter in Execute SQL Task

Create Variable in SSIS Package.

We have a SQL task and this task will update project schedule date based on input date

 

SQL Query:

Update Project SET ScheduleDate = ?

or

sp_updateScheduleDate ?

In above SQL query, we need to pass input parameter for schedule date, for this first we need to create a variable

1.    Open the Execute SQL task Editor -

Right Click on SQL Task à Select Edit à Open the Execute SQL task Editor

2.    Select Parameter Mappings

3.    Click on Add button and Add Variable popup window will be opened and enter below fields

·         Container   -  Specify Scope of variable, by default SSIS package name – eg Update

·         Name - Enter variable Name eg – ScheduleDate.

·         Namespace – Enter Namespace

·         Value Type – Select Data Type for variable (eg DateTime) 

·         Value (by default value) – Today Date – 12/12/2019

After entered above fields and click on OK button

Maps variable as input parameter in SQL Task:

Maps the created variable ScheduleDate (User::ScheduleDate) as input parameter for SQL task and in SQL script, we have only one input parameter symbol ‘?’  So give parameter name as 0, and define parameter size 10 for Date Field

 

If you have more than one input parameter, then give parameter name as 0, 1, and 2.

SQL Query:

Update Project SET ScheduleDate = ?, ProjectName = ?