Executing T-SQL commands: Execute SQL Task

One of the most useful tasks in SSIS is the Execute SQL Task. This task can be used for executing every T-SQL command on an underlying database. Underlying databases can vary because there is a wide variety of connection managers that have SQL Task support. In this recipe, we will see some of the most useful scenarios such as the execution of T-SQL commands and fetching results, or passing input and output parameters.

How to do it...

1. Open SSTD and create a new Integration Services Project type at the following location: C:\SSIS\Ch02_Control Flow Tasks\ and name the project R01_Execute SQL Task.

2. Rename the package as P01_SingleRowResultSet.dtsx.

3. From the SSIS menu select Variables; the Variables pane will appear. Create a new variable with the Name RowCount and Data type Int32 at the package Scope.

   

4. In the empty package's Control Flow tab, drag-and-drop a Execute SQL Task from the toolbox under Favorites into the Control Flow.

5. Double-click on Execute SQL...