SSIS Execute SQL Task

SSIS Execute SQL task is the very often used task in SSIS. But I found it is bit tricky to call a stored procedure by using OLE DB connection.
Here is a simple example that shows how to call a stored procedure by using Execute SQL task.
Scenario: Execute a stored procedure by passing ProcessName as an input parameter and store the output parameter’s value in a global variable.
Below is the stored procedure that I am going to call from SQL Execute task.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pr_GetFileLocation]
(
@ProcessName NVARCHAR(500),
@Location NVARCHAR(1000) OUTPUT
)
AS
SELECT @Location = [Path] FROM tblDataFeedConfig 
WHERE FeedName = '' + @ProcessName + ''

Step 1: Declare the Location and ProcessName variables. The default value for the ProcessName is “ProductFeed”.





 Step 2:  Drag and drop a Execute SQL task to the Control Flow.





Step 3: Double click on the Execute SQL task. It will bring the task editor window like the below picture. Need to set the properties as below to call the stored procedure.

a) ResultSet - None.

b) ConnectionType - OLE DB.

c) Connection – OLE DB connection.

d) SQLSourceType - Direct Input.

e) SQL Statement – Command to call the stored procedure. This will be explained in the next step.

Step 4:  To execute the stored procedure type the following command in the Enter SQL Query window.

                Exec dbo.pr_GetFileLocation ?, ? OUTPUT. 





Step 5: Click on the Parameter Mapping and add the user defined variables. Here ProcessName is input parameter and Location is the output parameter. This step is very important to get back the value from stored procedure.





Now if you execute the task, the returned value will be stored in the Location variable.