Systems Engineering and RDBMS

Archive for April 14th, 2008

Execute SQL Tasks: Using Stored Procedures

Posted by decipherinfosys on April 14, 2008

This post is a continuation of the series of posts that we have done in the past on SSIS. You can access those here:

DTS vs SSIS, Execute SQL Task Introduction, Running parameterized queries using Execute SQL Task – I and Running parameterized queries using Execute SQL Task – II.

In this post, we are going to explore the execution of the Stored Procedures using the Execute SQL Task. As described in the earlier posts, SQL Server Integration Services provides us with a wide variety of connections to communicate with the database. The OLE DB connection and the ADO.NET connection are the most widely used connections to communicate with the database. In this post, we are going to learn executing the stored procedures using both the connections.

Defining the parameters in the Execute Sql Task:

This topic has been dealt in detail in one of the previous posts: Running Parameterized SQL Commands using the Execute SQL Task – I

Executing the Stored Procedures in the Execute Sql Task Editor:

One of the earlier post, “Running Parameterized SQL Commands using the Execute SQL Task – I” discusses the configuration of the Execute Sql Task to use different kinds of connections to talk to the database. We recommend reading through that post before reading this one since many items are common and we have not reproduced them in this post again.

Executing Stored Procedures using Execute SQL Task using OLEDB Connection:

The configuration of the Execute SQL Task to use OLEDB connection was explained in detail in the earlier post. Configure the connection manager to point to the source database. The following screenshot shows the general page of the Execute SQL Task:

Let us consider that we are running the following stored procedure in the Execute Sql Task. We configured the SQLSourceType property to direct input. Copy the following statement and paste it in the SqlStatement editor. As described earlier, while using the OLDEB connection, the parameters are represented by the ?.

exec ?=spSplitNames ?,? out,? out,? Out

This stored procedure accepts the FullName as the input parameter and splits into the ‘FirstName’, ‘MiddleName’, and ‘LastName’. The business rule coded states that the middle name can only be one character. It may or may not be followed by the ‘.’. The first parameter represented by a ? represents the return value while the next ? represents the input parameter. The output parameters are represented by out keyword.

Add four variables named ‘FullName’, ‘FirstName’, ‘MiddleName’, ‘LastName’ of type string and ‘RetValue’ of type long to the SSIS variables collection to pass the FullName as the input parameter and the ‘FirstName’, ‘MiddleName’, ‘LastName’ as the output parameters. In the parameter page of the task, we should define one input parameter, three output parameters and one return value. The parameters will be named as 0,1,2,3,4,5 respectively, The name 0 maps to the Return Value and the name 1 maps to the FullName we are passing in as the parameters.2,3,4 maps to FirstName, MiddleName and LastName respectively. The following screen shot shows the parameter page for the above query.

For our demo purposes, let’s display the value returned by the Execute Sql Task. To do that, drag and drop Script Task from the control flow tasks and rename it as Display the Result. Script Task in also one of the most important tasks in SSIS control flow tasks. The following screen shot shows the configuration of the script task.

In the script page of the script task, enter the variable name we wish to display which are the FirstName, MiddleName, LastName, and RetValue. Click on the Design Script button on this page and paste the following code in it.

MsgBox(Dts.Variables(“User::FirstName”).Value.ToString, , “The First Name is :”)

MsgBox(Dts.Variables(“User::MiddleName”).Value.ToString, , “The Middle Name is :”)

MsgBox(Dts.Variables(“User::LastName”).Value.ToString, , “The Last Name is :”)

MsgBox(Dts.Variables(“User::RetValue”).Value.ToString, , “The Returned Value is :”)

After you are done coding the script task, click on ok and we will return to the Control Flow design pane. Click on Execute Sql Task next. This will show a green arrow which is the precedence constraint. Drag the green arrow and drop it on the Script Task. This is basically telling the SSIS engine that the Script Task should executed upon the successful execution of the prevoius step which is the execute sql task.

This finishes the development of the SSIS Package which displays the value returned by a stored procedure and also the output parameters. We can change the parameters passed into the query by giving different values to the variables Manager and Employee.

To execute the package press the F5 key. The following screen shot shows the successful execution of the package:

You can download the code for this post from here.

In this post, we saw how one can use stored procedures in the Execute SQL Task. In one of the future posts, we will start looking into the logging and the debugging features of SSIS and how they help in building a strong ETL process.

Posted in SQL Server | 1 Comment »