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.
Open SSTD and create a new Integration Services Project type at the following location:
C:\SSIS\Ch02_Control Flow Tasks\and name the projectR01_Execute SQL Task.Rename the package as
P01_SingleRowResultSet.dtsx.From the SSIS menu select Variables; the Variables pane will appear. Create a new variable with the Name
RowCountand Data type Int32 at the package Scope.
In the empty package's Control Flow tab, drag-and-drop a Execute SQL Task from the toolbox under Favorites into the Control Flow.
Double-click on Execute SQL...