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:
Input parameters: to pass a value as a parameter within a SQL command or stored procedure
Output parameters: to store a value generated from an SQL command or stored procedure
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.
create the variable
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:
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
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
Comments