• Marek Vavrovic

Execute SQL Task in SSIS

Updated: Aug 19

Example 1: Execute SQL Task, Result set: Sigle row, using dynamic query

Example 2: Execute SQL Task, Result set: Full result set

Example 3: Execute SQL Task, SQLStatement as a Expression

Parameters

Execute SQL Task in SSIS allows user to execute parameterized SQL statement and create mapping between these parameters and the SSIS variables. To add a parameter into a SQL statement you must use a parameter marker which differs based on the connection type.


Connection Type Marker Example

ADO,ODBC,OLEDB ? Select * from table where ID > ?

ADO.NET,SQLMOBILE @<parameter name> Select * from table where ID > @ID

EXCEL ? Select * from table where ID > ?


There are three types of parameters that can be used within an Execute SQL Task in SSIS:

  1. Input parameters: to pass a value as a parameter within a SQL command or stored procedure

  2. Output parameters: to store a value generated from an SQL command or stored procedure

  3. Return Value: to store a value returned by an SQL command or stored procedure

As a example the following SQL command:


select * from dbo.Products where CategoryID = ?


When using SQL query with a parameter you must go to the Parameter Mapping tab and define the variable mapping.

  1. create the variable

  2. map the variable comming from SQL Server


Properties of parameter

  • Variable Name: Select the variable name that you want to map to a parameter

  • Direction: Specify if the type of the parameter (input, output, return value)

  • Data Type: Specify the data type of the parameter (It must be compatible with the data type of the variable)

  • Parameter Name: The name of the parameter, the naming convention depends on the connection type

  • Parameter Size: Specify the length of the parameter when using string data types otherwise it must be -1 (default value)

Parameter Name

Connection type Parameter name

ADO Param1, Param2, …

ADO.NET and SQLMOBILE @<parameter name>

ODBC 1, 2, 3, …

EXCEL and OLE DB 0, 1, 2, 3, …


Output parameter

When it comes to output parameters, they are used to store values that can be set at any part of the SQL command, they have the same concept of the stored procedure output parameters. As example, if we can use a similar SQL command to store the Maximum value of CategoryID column:


Select ? = Max(CategoryID) from dbo.Products



Result Sets

When using an Execute SQL Task in SSIS, result sets can be generated from an SQL command mostly a SELECT query. There are 4 types of result sets:

  • None: No result set is generated

  • Single Row: When the result set is a single row, such as SELECT TOP 1 or a SELECT MAX() commands

  • Full Result set: When the SQL statement generates multiple rows such as a SELECT * command

  • XML: This option is used to store the result within an XML value

You can select the result set type from the Execute SQL Task editor (General Tab):


To store the result set into a variable, we must configure the variable mapping within the Result Set tab.


Variable data type


When using Single Row result set or XML string, values are stored within variable and can be consumed directly within Expressions, Tasks, Scripts or Transformations. But, when the result set is stored within a variable of type System.Object, the variable can be consumed using:

  1. ADO enumerator within a Foreach Loop container: This option is used to loop over the rows of the result set and consume them row by row by mapping each row columns with SSIS variables

  2. Using a .Net Script (Task / Component): The code differs based on the Object type

Example 1:

Execute SQL Task, Result set: Sigle row

SQLSourceType: variable (dynamic query)


Step 1: Define variables


vValueReturnedBack: Execute SQL Task will return UnitPrice value from SQL server - Result set: Single Row. I will map this variable to Result set variable in Execute SQL Task.

vSQLCommand: this variable is for building the dynamic SQL code.

vWhereCondition: contains value[6] in the where clase

vTable: holding SQL table name [Products]

vColumnName: column I want to retrieve from table products.

Step 2: use Expression Builder to build the dynamic SQL statement.


@[User::vSQLCommand]="Select " + @[User::vColumnName]+" From " + @[User::vTable] +" Where ProductID = " + @[User::vWhereCondition]


step 3: Add Execute SQL Task and set it up.

Result Set: Single row; SQLSourceType: Variable; SourceVariable: vSQLCommand ...


Step 4 :Because the result set is NOT none but Single row, I have to set up the variable for Result set. I do not work with parameters from SQL [?] so there is no parameter mapping needed.

From SQL server, I will get back UnitPrice: 25 and vValueReturnedBack will hold this value.

Step 5: Run the package



Example 2:

Execute SQL Task, Result set: Full result set


I want to place this result set into a variable.

Step 1 : Prepare the variables which will represent the columns from SQL table and variable to hold the returned table (data type Object) from sql server.


Step 2: set up Execute SQL Task

Result set: Full result set

SQLSourceTyp: Direct query

SQLStatement:

Select TOP(10) [CategoryID], [CategoryName], [ProductName], [ProductSales] from [Sales by Category]

order by ProductSales desc


Step 3: Go to Result Set, SQL statement returns Full result set which must be mapped to the variable type Object.


Step 4: I am going to add Foreach loop Container just to watch the variables.

I need Foreach ADO Enum and an Object variable @vtblProductSales


Step 5: Variable Mapping. Must be in the same order as the SQL statement has been written.


Step 6: Run the package


Example 3:

Using dynamic query


I have set up a Data Flow which is loading data from a flat file into SQL server table. Using Row Count transformation which will return a number of rows loaded and require a variable for that @vCount.

Step 1:

On the Control Flow tab I have an Execute SQL task which will insert data into a SQL table. I am going to use an expression to build a dynamic query for this.

a/ mark Execute SQL task, press F4

b/ go to Expressions, click on the dots [....]

c/use SqlStatementSource in the Property window

d/build an expression


Step3:

Build the expression

"insert into tblLogs

select '"+ @[User::vFilePath] +"', "+(DT_WSTR,12) @[User::vCount] +", getdate()

"

Evaluated value:

insert into tblLogs

select 'C:\Files\TestData_1.CSV', 0, getdate()


Step 4

Check Execute SQL Task for the SQLStatement




14 views0 comments