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.

Comments

Post a Comment

Popular posts from this blog

SSIS Merge Join - Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata SSIS

Image
SSIS Merge Join Issue ( Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata SSIS )   I was working on one of my client requirement where I need to split the flat file data based on the conditions and then add join that data with reference data (again a flat file). I used the MergeJoin task to achieve this. Unfortunately I ended up with this error. I tried most of the options available on the internet but none of them worked for me because my mistake was silly I set a property of “Sort” task in one not the other. That’s why MergeJoin was throwing “ Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata SSIS ” error.     Lets first discuss about the solution of my problem Root Cause – The main problem was related to the Sort task. I selected the “Comparison Flags” option in one Sort task but not in other task because of that M...
Read more

jsGrid

Image
I was working one a Asp.Net MVC application where I was using the Web Grid to display the data based on the selected search criteria. It was working great but client asked me to use the jsGrid instead of Web Grid as it has many ready to use features like (Edit, Delete, Search, etc..).  Initially, jsGrid gave me very hard time to display the data in it but after few hit and trial I manage to display the data. After spending couple of hours on jsGrid, I started liking it. In this article, I am going to explain how to use the jsGrid to display the data and some other tactics in very easy steps. I am building the application in ASP.NET MVC. Step 1. Content delivery network (CDN) of jsGrid and jQuery libraries are as below https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.min.css https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.css https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid-theme.min.css https://code.jquery.com/jquery.min.js ...
Read more

Add Item to SharePoint List with attachment using client object model

Add Item to SharePoint List with attachment using client object model If your requirement is to add the item to a SharePoint list with attachment using the client object model, this post will help you in this. You can achieve this by following the below steps.   Step 1 – Define some constant variables those will hold the values of SharePoint web service, List Id, etc. private const string siteUrl = "http://appsuat.portal.intranet.aegonuk.com/sites/genapps/CashFlowsCashPositions" ;         private const string spWebServiceUrl = "http://appsuat.portal.intranet.aegonuk.com/sites/genapps/CashFlowsCashPositions/_vti_bin/Lists.asmx" ;         private const string updateSOAPAction = "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems" ;         private const string addAttachSOAPAction = "http://schemas.microsoft.com/sharepoint/soap/AddAtta...
Read more