Returning Single Row using Execute SQL Task in SSIS 2008 R2 with Example

Friends,

The Execute SQL task is one of the handier components in SQL Server Integration Services (SSIS) because it lets you run Transact-SQL statements from within your control flow. The task is especially useful for returning result sets that can then be used by other components in SSIS package.

The Execute SQL task runs SQL statements or stored procedures from a package. The task can contain either a single SQL statement or multiple SQL statements that run sequentially. We can use the Execute SQL task for the following purposes:

• Truncate a table or view in preparation for inserting data.
• Create, alter, and drop database objects such as tables and views.
• Re-create fact and dimension tables before loading data into them.
• Run stored procedures. If the SQL statement invokes a stored procedure that returns results from a temporary table, use the WITH RESULT SETS option to define metadata for the result set.
• Save the rowset returned from a query into a variable.

The Execute SQL task can be used in combination with the Foreach Loop and For Loop container to run multiple SQL statements. These containers implement repeating control flows in a package and they can run the Excute SQL task repeatedly. For example, using the Foreach Loop container a package can enumerate files in a folder and run an Execute SQL task repeatedly to execute the SQL statement stored in each file.

When using the Execute SQL task to return a result set, we must also implement the necessary variables and parameters to pass data into and out of the T-SQL statement called by the task.

In this scenario, I’ll use  Execute SQL task. The  task retrieves a single value from a table in theAdventureWorks2008 database. That value is returned by the task as a single-row result set.

Adding  Variable to the SSIS Package

The first variable I’ll create is the EmpNum variable. If the Variables window is not open, right-click the Control Flow workspace, and then click Variables. In the Variables window, add a new variable by clicking on the Add Variable icon.

Name the new variable EmpNum, and ensure that the scope is set at the package level, as indicated by the package name. (Here my package name is Execute sql task with single row). Next, set the data type to Int32 and the value to 0. The Execute SQL task will use the variable to store the value it retrieves from the database.

Now drag and drop Execute Sql Task to Control Flow pane.

Adding a Connection Manager to the SSIS Package

The next step is to create a connection manager that points to the AdventureWorks2008 database. Right-click theConnection Manager s window, and then click New OLE DB Connection

When the Configure OLE DB Connection Manager dialog box appears, click the New button to launch theConnection Manager dialog box.From the Server name drop-down list, select the name of your SQL Server instance, and then select an authentication type. From the Select or enter a database namedrop-down list, select  database.  you can see, I selected SQL Server instance, Windows Authentication as my authentication type, and the AdventureWorks2008 as my database.

Be sure to test the connection by clicking the Test Connection button. If the connection is good, click OK to close the Connection Manager dialog box.

When you’re returned to the Configure OLE DB Connection Manager dialog box, you’ll see that your new connection has been added to the Data connections section. Click OK to close the dialog box. Your connection should now be listed in Connection Managers window.

If you want, you can rename your connection manager to something more appropriate. To do so, right-click the connection, click Rename, and type in the new name.

Returning a Single-Row Result Set

Execute SQL task will return a single-row result set, which in this case, will contain only one value.

In this example, I’ll retrieve the highest BusinessEntityID value from the HumanResources.Employee table. I’ll start by using the first Execute SQLtask to retrieve the value and pass it to the EmpNum variable.

To get started, drag the Execute SQL task onto the Control Flow design surface. Then double-click the task to open the Execute SQL Task Editor. The editor opens to the General page

.

Notice that the General section contains the Name property and the Description property. The Name property refers to the task name. You should name the task something suitable.

The next section on the General page is Result Set. Notice that this section includes only the ResultSetproperty.

In this exercise, our query will return only a single value. Consequently, we will choose the Single row option.

Our next step is to associate our result set value with a variable that will store the value we retrieve from the database. To do this, go to the Result Set page of the Execute SQL Task Editor.

The main grid of the Result Set page contains two columns: Result Name and Variable Name. Click the Addbutton to add a row to the grid. In the Result Name column, enter the column name returned by your query (MaxEmpID). In the Variable Name column, select the User:: EmpNum variable.

If our single-row result set contains multiple columns, we would have had to map a variable to each column. However, because we returned only one value, we needed only one mapping.

Once you’ve associated your result set value with a variable, click OK to close the Execute SQL Task Editor. The task should now be set up to return a single-row result set.

Thanks a lot Archana for the document and it is well designed.

Regards,
Roopesh Babu V