Get the Stored Procedure out Parameter value in SSIS using ADO.NET connection

Get the Stored Procedure out Parameter value in SSIS using ADO.NET connection

I am new to SSIS and recently I got an assignment to build a SSIS package to load the data in SQL database from flat file. So I created a simple package with dataflow task. Later the requirement slightly got changed and now we need to call some stored proc with OUT PAREMETER those will apply the business logic and transform the data from staging table to main table(s).

In order to do this, I added couple of Execute SQL tasks to execute the stored procedure and get the output parameter value. While I was doing this I came to know that based on the connection type your SQL Statement, Parameter Mapping and some other configuration values needs to define in certain ways. I am not going to talk about those in this post. You can find them on this location.

Let’s start the step by step process to call the stored process in SSIS using Execute SQL task and get the out parameter value.

Step 1 – Add a new ADO.NET connection

Step 2 – Add the new Script task in SSIS package

Step 3 - Set the General properties of Execute SQL task. Few important properties those you need to make sure are defined correctly

SQL Statement – you need to just put the stored procedure name with or without schema

IsQueryStoredProcedure – it should be Ture

Connection Type – It should be ADO.NET

SQL Source Type – It should be Direct input

Step 4 – Set the Parameter Mapping properties of Execute SQL task. Again, make sure that following properties are set correctly for a parameter

Variable Name 

Direction – Choose the correct direction of parameter

Datatype – Choose the correct data type of parameter

Parameter Name – it should be same as the stored procedure parameter name

Parameter Size – For string type parameter set the length otherwise leave it as it is.

Step 5 – Once you complete all the step without any error, you are good to execute the package and if task executed successfully, you should be able to get the stored procedure out parameter value in the SSIS variable that mapped to the out parameter.