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

2
23405
single row

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

2 COMMENTS

  1. All of the comp-earning apps are tied to some sort of casino-friendly games. Some are providing slot games that can be found at actual casinos; others have their own games that are either completely new creations, or have original games that look and play a lot like casino games that they’re inspired by. Some offer other games, like Blackjack or Video Poker, aside from slots. Ignition: Our top pick, Ignition Casino has done an amazing job with its poker app to ensure the most thrilling mobile experience for all of its poker fans. If you join the casino, you can take part in regular poker tournaments but before doing so, consider benefiting from its lucrative offer for newcomers that grants up to $3,000 in bonus funds to play slots and poker.
    http://ohanataxi.com/us/bbs/board.php?bo_table=free&wr_id=6086
    Most online slot machines are optimized for mobile phones and other personal portable devices so that punters can squeeze in a little bit of gambling into their busy and moving lives. Most established software providers build their games with the HTML5 technology to equip free slots no download for Android and iOS sets. Over a stable internet connection, punters can play free slots online no download without any restrictions. Some casino sites do have an application rendition to download on mobile phones, while the rest can be accessed directly from any Flash-enabled browser. Free slots without downloading, as well as real money slots come in different types: video slots, classic slots, fruit machines, 3-reel slots, 3D slots, mobile slots, and offline slots. OnlineSlots isn’t an online casino, we’re an independent online slots review site that rates and reviews online casinos and slot games. That means you can play free slots on our website with no registration or downloads required.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

13 + = 19