Home > Articles > SQL Server Articles > Executing stored procedures using Execute SQL Task component.

Executing stored procedures using Execute SQL Task component.

by Rahul Pyarelal   on Feb 08, 2017   Category: SQL Server  | Level: Beginner  |  Views: 711    |  Points: 100   
Like this article? Bookmark and Share:
In this article we will show practical demo of executing stored procedures using “Execute SQL Task” component.

This article will show practical demo of executing stored procedures using “Execute SQL Task” component. Here we will create new database and also create a new table in the SQL Server. That is the same input column name.Thereafter we will also create a stored procedure in the “SSISTarget”as shown in the following screen short…


Execute SQL Task on SSIS

First create the two “Connection Manager”, one for the source database other for the Destination database.

Drag and drop “Execute SQL Task”on the Control Flow. Then open “Execute SQL Task” Editor and configure that as see the below screen short…


When you drag the “Execute SQL Task” in the Control Flow andthen configure the Task. In order to that, right click on the “Execute SQL Task” and say Edit. First go to the “IsQueryStoredProcedure” and set to “True” and then change “SQLStatement” to Stored Procedure name and say ok. No change in the “Parameter Mapping” than say “OK” as see the following screen short…


Component executing successfully and output on the SQL Server is…


How to fire select query using Execute SQL Task

First create the two connection manager one for the source database and other for the destination database.Right click on the connection manager say “New ADO.Net Connection”. It open the “Configure ADO.NET Connection manager” say “NEW” and create connection as see the following screen short…


Click the New and configure the connection manager. Then put the “Server name” and select the “Database Name” under the “Connection to a Database” say “OK”. After that select the “Data Connection” say “OK”. This connection is created for the “Source DataBase”. It’s see the following screen short…


Then create the connection manager for the “Destination Database”. It will also follow same process for the Destination database but only changeis the database name which hasto be created for the “Destination Database”rest all are same.


First go to the SSIS Toolbox and drag the “Execute SQL Task” in the Control Flow as see the below screen short…


Before, go to your application folder and create a “Text” file as see the following screen short…


Now double click on the “Execute SQL Task” and configure. In order to do the go to the “ConnectionType” and select the “ADO.NET”. Then go to the “Connection” and select the Connection type which is created for the “Source Database”. Now come to the “SQLSourceType” and select the “FileConnection”. Then click on the “IsQueryStoredProcedure” and select the “New Connection” as see the following screen short…


When you click on the “<New Connection>, then new window will open. Thereafter that configure it, first go the “Browse” and select the Text files which is createin your application as see the following screen short…


Now go to the Result Set and select “Full Result Set” for the “SELECT QUERY”.

Please Note:When query returns only one row then select “Single row” and when query returns more than one row then select “Full Result Set”.


Now go to the Result set. Say “Add” result set and creates a new variable. We need variable which is going to full recode set which will return because of the select query.

Than configure the “AddVariable”. Put the “variable name” and “variable type” than say “ok” as see the following screen short…


Complete the all configuring then click OK. After the package is executed you will see red cross mark on the component to see failure details go to Progress tab.


Here under “Progress tab”, error says that Result Set name must be “Zero”.

Solution

When we set “Result Set” to Full Result set or XML in that case “Result Set Name” is zero. You not set anything and it cannot have more than one row. We can have only one record here that is zero. It sees the following screen short…


When you set the “Result Name” to zero then package will run as see the following screen short…


Below is the video on SSIS to start with a project. Get the essence of practical MSBI with it: -



Like this article? Bookmark and Share:

Most viewed Articles

User Comments


No response found, be the first to review this article.

Submit feedback about this article

Please sign in to post feedback

Latest Posts