Running Parameterized SQL Commands Using the Execute SQL Task – I
Posted by decipherinfosys on March 26, 2008
In one of our previous blog post, we saw the configuration of one of the many control flow tasks which the Integration Services offers us: the Execute SQL Task. We also saw how to configure this task to use various kind of sources for the sql statements like the Direct Input, Input from the variable and also the Input from a sql file. In this article we will go over using the parameterized queries in the Execute SQL task and returning result sets using OLE DB. In OLTP systems, we require running a particular sql statement many times with a change in the parameter value supplied. The idea is to parse and compile once and execute many times. As such such queries make use of bind variables (Oracle Lingo) or parameters and the queries using them are called as parameterized queries. You can search for parameterized query or bind variables on our site to get more information on those. In this post, we are going to concentrate on using the Execute Sql Task to run the parameterized queries. A lot of very good information on the Execute SQL task can be obtained at this URL at MSDN:
Defining the parameters in the Execute Sql Task:
SQL statements and stored procedures frequently use input parameters, output parameters, and return codes. The Execute SQL task supports the Input, Output, and ReturnValue parameter types. The image below shows the parameter page of the Execute Sql task. For demonstration purposes few variables have been added and defined as parameters. We are going to see how to do this with an example later in the post.
We use the Input type for input parameters, Output for output parameters, and ReturnValue for return codes from the stored procedure. Return codes from the stored procedures indicate if the stored procedure was executed successfully or not. Parameters in SQL commands, including queries and stored procedures, are mapped to user-defined variables that are created within the scope of the Execute SQL task, a parent container or within the scope of the package. The values of variables can be set at design time or populated dynamically at run time. You can also map parameters to system variables.
Defining the Result Set in the Execute Sql Task:
As we had mentioned in the previous post, we can also return the result set from the sql statement we execute from the Execute Sql Task against the source database. Depending upon the type of SQL command, a result set may or may not be returned to the Execute SQL task. Example: A SELECT statement typically returns a result set, but an INSERT or an UPDATE statement does not return a result set.
Writing Parameterized Queries in the Execute Sql Task Editor:
Depending on the connection type that the Execute SQL task uses, the syntax of the SQL command uses different parameter markers. Parameter markers are the place holders for the parameters. The following table (refer the MSDN post above – we are using that as an example for building up our blog post sample) summarizes the different syntax that is used for different connection types for specifying the parameters.
|Conne Connection Type||Parameter Marker||Parameter Name||Example|
|OLED OLEDB & EXCEL||?||0,1,2,3||select * from HumanResources.Employee where ManagerId =?|
||?||1,2,3,4||select * from HumanResources.Employee where ManagerId =?|
|ADO ADO||?||par1, par2, par3||select * from HumanResources.Employee where ManagerId =?|
||@<parametername>||@<parametername>||select * from HumanResources.Employee where ManagerId = @ManagerId|
An explanation of these is given in the link above from MSDN. Using that as a reference and applying it to our example: In OLE DB, Excel, ODBC and ADO connections, the parameters are represented by a “?” in the query while in ADO.NET, it is represented by @parameter. The names that you can use as parameter names in the mappings between variables and parameters also vary by connection manager type. In the Parameter page of the Execute Sql task, for OLDDB & EXCEL connection types, the parameter name is specified by the ordinal number of the parameter in the query. That means the parameter name of 0 represents the first “?” in the query, parameter name 1 represents the second “?” in the query and so on. The ODBC connection manager type requires that you use the numeric value of a 1-based ordinal as the parameter name i.e. the first “?” in the query is named as 1 in the parameter name property of the parameter page, the second “?” is named and 2 and so on. The ADO.NET connection type, uses a user-defined name with a @ prefix. The user defined parameter name in the query and the user defined parameter name in the parameter page should match. Finally the ADO connection type uses the user defined parameter name in the parameter page of the execute sql task. Of all the connection types, OLE DB and the ADO.NET are most widely used.
Executing parameterized query using Execute SQL Task returning single result set:
The configuration of the Execute SQL Task was explained in detail in the earlier post. There is a minor change in the configuration here. In the earlier post, since it did not return any result set, we set the Result Set Property in the general page to be None. Since we want to return a single value in this case, let us set the Result Set Property to single row. The image below, shows the configuration of the Result Set Property in the General Page of the editor.
Let us consider that we are running the following parameterized query in the Execute Sql Task. We configured the SQLSourceType property to direct input. Copy the following statement and paste it in the SqlStatement editor.
select Title from HumanResources.Employee where ManagerId =? And EmployeeId = ?
Add two variables names ‘Manager’ and ‘Employee’ of type integer to the SSIS variables collection to pass the ManagerId and the EmployeeId as the input parameters. Add another variable named ‘Title’ to hold the value returned by the sql statement. In the parameter page of the task, we should define these two parameters as Input parameters. The parameters will be named as 0,1 respectively, The name 0 maps to the ManagerId we are passing in and the name 1 maps to the EmployeeId we are passing in as the parameters. The image shown below shows the parameter page for the above query.
The next step is to define the variable to hold the Result Set. The image shown below shows the Result Set page of the Execute Sql Task.
Select the drop down for the Variable Name column and select the variable named ‘Title’ which we defined earlier as a part of the variable collection. Since we are returning only one column, the Result Name is left at 0. If we are returning more than one column, then the corresponding variables will have incrementing integers starting from 0. This completes the configuration of the Execute SQL task to execute the parameterized query. For our demo purposes, we have to 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 Title. Script Task in also one of the most important tasks in SSIS control flow tasks. The image below shows the configuration of the script task.
In the script page of the script task, enter the variable name we wish to display which is the ‘Title’. Click on the Design Script button on this page and paste the following code in it:
MsgBox(“The Retrieved Value is: “& _ Dts.Variables(“User::Title”).Value.ToString)
After finishing coding the script task, click on ok and we will return to the Control Flow design pane.Click on Execute Sql Task. 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 parameterized query. 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. We can go back to query the database to verify if the value returned was correct or not.
Configuring the Execute Sql Task for executing the parameterized query returning a Full Result Set is very similar to the above example that we had just described with a few differences.
The first difference is that since we are returning a full result set, set the Result Set property in the General Page to Full result set rather than Single Row. The image shown below shows the Execute Sql Task configured to return the full result set.
The next difference is in defining the Result Set. Since we are returning a full result set from the sql statement we require a variable of Object Type to hold the full result set. So for our example let us define a variable ResultSet of type Object for holding the full result set returned from the sql statement. Paste the sql statement below into the Sql Statement Editor of the Execute Sql Task.
select EmployeeId,Title,BirthDate,Gender,VacationHours from HumanResources.Employee where ManagerId = ?
Let us define one more variable called manager to pass the ManagerId into the sql statement. Since we are returning 5 columns, for our display purposes, let us define 5 more variables named EmployeeId, Title, BirthDate, Gender, VacationHours with the respective types. The image below shows the variables editor with the variables we described.
In the Result Set page of the Execute Sql Task, we set the Variable which holds the result set returned by the sql statement. As described earlier this variable is of type Object. In our example, the variable name is ResultSet which holds the result set. The following screen shot shows the configuration of the Result Set Page of the Execute Sql Task.
For our display purpose, we need to have some mechanism which loops through each record in the result set and displays the record. Here comes the need of the For Each Loop Container. This article gives a brief introduction of For Each Loop container. This will be dealt in detail in upcoming posts.
For Each Loop Container is used in the situations where we need to loop through a particular directory until we process all the files in the director or when we need to loop through each record in the record set for processing. The following screen shot shows the For Each Loop Container Collection Page. The General Page of this task just asks us to enter a meaningful name for this task.
The most important configuration property in this is the Enumerator Property. The Enumerator specifies the For each Loop Container what kind of looping it performs. In our case, since we have to loop through a record set, we should select the Foreach ADO Enumerator. Foreach ADO Enumerator loop through each record in the record set. We should map each column in the record set to a variable to display/process them. This assignment is done in Variable Mappings Page. Earlier we defined the variables to hold the columns returned by the sql statement. The image below shows the variable mapping to the columns returned from the SQL statement.
The Index column represents the ordinal position of the column in the sql statement. I am mapping the first column returned to a variable called EmployeeId, the second column to the variable Title and so on. The Index value starts from 0 and increments by 1 as the number of variables increases. This finishes the configuration of the For Each Loop Container. Inside the For Each Loop Container, drag and drop the script task. Configure this task as we did in the earlier example. The image below shows the script page of the script task.
Enter all the variables EmployeeId, Title, BirthDate, Gender, VacationHours in the ReadOnly variables Property of the script task. Click on the Design Script button on the Script page of the task and paste the following code in the Script Task Editor:
MsgBox(“The EmployeeId is: ” & _
MsgBox(“The Title is: ” & _
MsgBox(“The BirthDate is: ” & _
MsgBox(“The Gender is: ” & _
MsgBox(“The VacationHours is: ” & _
The image below shows the scripting editor of the script task:
This finishes the development of the SSIS Package which displays the result set returned by a parameterized query. We can change the parameters passed into the query by giving different values to the variables Manager. To execute the package press the F5 key. The following screen shot shows the partially successful execution of the package. We can go back to query the database to verify if the value returned was correct or not.
In this post, we discussed the use of parameterized queries and how to execute the parameterized queries using the Execute SQL Task. We also discussed the various kinds of result sets returned by the Execute Sql Task and how to configure the task to return the single result set and the Full Result Set. We also went a little into the configuration of “For Each Loop” Container. Experimenting with the XML result set is left as an exercise to the reader. This is similar to that of returning the single row except that we specify explicitly that the statement is returning the XML data.
One Response to “Running Parameterized SQL Commands Using the Execute SQL Task – I”
Sorry, the comment form is closed at this time.