Running Parameterized SQL Commands using the Execute SQL Task – II
Posted by decipherinfosys on March 29, 2008
This post is a continuation of the SSIS series of posts that we had done before: DTS vs SSIS, Execute SQL Task, and running parameterized SQL commands using that task. In today’s post, we will continue the previous post and will cover parameterized queries in the Execute SQL task and returning the result set using the ADO.Net connection. The last part is what makes this post different than the previous one. So, we would advise reading up that post before reading this one since many items are common and we have not reproduce them in here again. We are only going to be highlighting what is different when using the ADO.Net connection.
Defining the parameters and the Result Set in the Execute Sql Task:
This remains the same as the previous post.
Executing parameterized query using Execute SQL Task returning single result set using ADO.NET Connection:
The configuration of the Execute SQL Task to use OLEDB connection was explained in detail in the earlier article. There is a minor change in the configuration here to use the ADO.NET connection. In the earlier post, since we 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 shown 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. As described earlier, while using the ADO.NET connection, the parameters are represented by the @parametername.
Select Title from HumanResources.Employee where ManagerId =@MgrId And EmployeeId = @EmpId
Add two variables named ‘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 @MgrId, @EmpId respectively. The name @MgrId maps to the ManagerId we are passing in and the name @EmpId maps to the EmployeeId we are passing in as the parameters. The parameter names in the query and the parameter place holders in the Parameter Mapping Page should match while using the ADO.NET connection. The image below shows the parameter page for the above query.
In the previous post we discussed the design of the SSIS package for executing the parameterized query using the OLEDB connection. Follow the instructions discussed there to configure the Execute SQL Task to return the Single Result Set and display the Result Set using the Script Task. 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 image shown below the successful execution of the package. We can go back to query the database to verify if the value returned was correct or not.
Executing parameterized query using Execute SQL Task returning Full Result Set using ADO.NET:
Configuring the Execute Sql Task for executing the parameterized query returning a Full Result Set is very similar to the above example 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 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 = @MgrId
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 shown below shows the variables editor with the variables we described.
In the previous post, we discussed the design of the SSIS package for executing the parameterized query using the OLEDB connection. Follow the instructions discussed there to configure the Execute SQL Task to return the Full Result Set and display the Result Set using 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 with ADO.NET connection. 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. This is similar to that of returning the single row except that we specify explicitly that the statement is returning the XML data.
Sorry, the comment form is closed at this time.