sql task variable ssis

• SQL Server training
• Write for us!

Execute SQL Task in SSIS: Output Parameters vs Result Sets

In the previous article, I gave an overview of Execute SQL Task in SSIS and we illustrated some of the differences between writing an expression to evaluate SqlStatementSource property and writing this expression within a variable and change the Execute SQL Task Source Type to variable.

In this article, I will describe the difference between using output parameters and result sets within Execute SQL Task in SSIS.

Note that this article is the fifth article in the SSIS feature face to face series , which aims to remove confusion and to illustrate some of the differences between similar features provided by SQL Server Integration Services.

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.

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

• Input parameters: used to pass a value as a parameter within a SQL command or stored procedure
• Output parameters: used to store a value generated from an SQL command or stored procedure
• Return Value: used to store a value returned by an SQL command or stored procedure

When using Execute SQL Task in SSIS, to map a parameter to a variable, you must go to the Parameter Mapping tab, and define the variable mapping.

As example, in the Execute SQL Task, we defined the following SQL command:

Now, if we click on the Parameter Mapping tab, we should see the following form:

Figure 1 – Parameter Mapping Tab

To add a parameter mapping, we must click on the Add button, since we have on specified one parameter in the SQL command then we should add only one mapping. When we click on the Add button, one line is added to the grid as shown in the image below:

Figure 2 – Adding Parameter Mapping

Within Execute SQL Task in SSIS, you have to configure the following properties of each 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)

Output parameters

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 BusinessEntityID column:

Then we have to configure the parameter mapping as shown in the image below:

Figure 3 – Output Parameter example

If we add a breakpoint on the PostExecute event of the Execute SQL Task, and we check the variable value, it shows the Maximum BusinessEntityID value as shown below:

Figure 4 – Output Parameter Value

In addition, the output parameter can be defined in a stored procedure as the following:

You can refer to the Microsoft official documentation to learn more about Execute SQL Task in SSIS parameters and return values.

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 many 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):

Figure 5 – Result Set type selection

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

Figure 6 – Result Set Tab

In the Result Set tab, you must specify the result name and the variable that you want to map to. If the result set type is Single row , the result name must be the column name or the column position in the column list. If the result set type is Full result set or XML, you must use 0 as the result set name.

When it comes to variable mapping each type of result set must be mapped to specific variable data types:

• Single Row: The variable data type depends on the returned column data type
• Full Result Set: The variable must be a System.Object variable and the Object type is an ADO Recordset mostly or a System.Data.Dataset in case of ADO.NET connection
• XML: The variable must be a System.String variable where the result will be stored as an XML string, or a System.Object variable where the result set is stored as MSXML6.IXMLDOMDocument mostly or System.Xml.XmlDocument when using an ADO.NET connection

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:

System.Data.DataSet:

ADO RecordSet:

System.Xml.XmlDocument:

Note that, an ADO RecordSet variable can be consumed only one time.

For additional information you can refer to the official documentations:

• Result Sets in the Execute SQL Task
• Execute SQL Task Editor (Result Set Page)
• Execute SQL Task in SSIS

Output Parameters Vs Result Sets

Many times, I was asked on the differences between using output parameters and Result Set and which is more recommended when using Execute SQL Task in SSIS. In general, each one of these options has its own use cases even if there are some similarities. In this section, I will try to illustrate the differences and similarities between these two options.

• When the result consists of multiple rows, the output parameter cannot, since they don’t allow to store a table valued results

If we need to store values from different SQL command we cannot use Result Sets, while output parameters can do the trick:

In case that we need to retrieve a value from a query located in the middle of the whole SQL statement and reuse this value within the same SQL statement, we need output parameters:

• Result Set cannot store a Return Value

Result Set:

Parameters:

Both options can store XML results (To use parameters you must use FOR XML clause in the SQL Ssatement)

There are many cases where output parameters and result sets are used in the same Execute SQL Task in SSIS:

• From a performance perspective, there is a difference between using parameters and Result Set, since in general returning scalar values is more efficient then using Result Set since the second carries a lot of helper methods

External Links and References

• Map Query Parameters to Variables in an Execute SQL Task
• Execute SQL Task output parameter vs ResultSet

Table of contents

• Recent Posts

• Different methods for monitoring MongoDB databases - June 14, 2023
• Learn SQL: Insert multiple rows commands - March 6, 2023
• Learn SQL standard deviation function - January 17, 2023

Related posts:

• SSIS XML Source vs. XML task
• An Overview of the XML Task in SSIS Packages
• SSIS Expression Tasks vs Evaluating variables as expressions
• Overview of SSIS Package Logging
• SSIS Foreach Loop vs For Loop Container

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

SSIS - how to set SSIS variable by sql

i need to select some value by sql and then set to SSIS variable, how can i select a row count, some columns from table and then set to SSIS variable?

any sample?

SQL Server Integration Services A Microsoft platform for building enterprise-level data integration and data transformations solutions. 2,323 questions Sign in to follow

Hi @nononame2021 ,

Please also refer to below link for details.

https://www.sqlshack.com/execute-sql-tasks-in-ssis-output-parameters-vs-result-sets/

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

how can I output multiple variable by preparing stored procedure, finally, map to multiple variables in ssis?

1 additional answer

That's more then simple, with a SQL Task and mapping the the result to a SSIS variable, see https://learn.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task?view=sql-server-ver16

if i have multiple variable , how to map to SSIS variable? any screen capture how to setup in SSIS?

if i have multiple variable

The count don't matter, map one variable by the other.

Insight Extractor – Blog

Paras doshi's blog on analytics, data science & business intelligence., ssis – how to use execute sql task to assign value to a variable.

How to use Execute SQL Task in SSIS to assign value to a variable?

This is a beginner level post so I’ll show you how you can use Execute SQL Task to assign a value to a variable. Note that variables can also be given full result set. With that said, here are the steps:

1. Create the query against the source system

Example: ((Note the column name, this will be handy later!)

2. Open SSIS Project > Create the variable

Conclusion:

In this post, you saw how to use Execute SQL Task in SQL server integration services to assign a value to a variable.

Share this:

• Click to share on LinkedIn (Opens in new window)
• Click to share on Reddit (Opens in new window)
• Click to share on Twitter (Opens in new window)
• Click to share on Facebook (Opens in new window)
• Click to email a link to a friend (Opens in new window)
• Click to share on WhatsApp (Opens in new window)
• Click to share on Pinterest (Opens in new window)
• Click to share on Pocket (Opens in new window)
• Click to share on Telegram (Opens in new window)
• Click to share on Tumblr (Opens in new window)
• Click to print (Opens in new window)

2 thoughts on “ SSIS – How to use Execute SQL Task to assign value to a variable? ”

• Pingback: Design pattern for making staging table loads incremental in SSIS: | Paras Doshi - Blog

ModifiedDate is String or Date ? You created the varible data type String.

• February 15, 2021 at 8:27 am

What do you think? Leave a comment below. Cancel reply

Marek Vavrovic's Blog

07479 744442

• Marek Vavrovic
• Aug 15, 2021

Execute SQL Task in SSIS

Updated: Aug 19, 2021

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

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

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

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

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.

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

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()

Check Execute SQL Task for the SQLStatement

Recent Posts

SSIS Append data

Data Warehouse Dimension Loading Using Lookup and Conditional Split Transformations.

SSIS Expressions

Welcome To TechBrothersIT

TechBrothersIT is the blog spot and a video (Youtube) Channel to learn and share Information, scenarios, real time examples about SQL Server, Transact-SQL (TSQL), SQL Server Database Administration (SQL DBA), Business Intelligence (BI), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Data Warehouse (DWH) Concepts, Microsoft Dynamics AX, Microsoft Dynamics Lifecycle Services and all other different Microsoft Technologies.

• Azure Data Factory Interview Question & Answers
• Azure Data Factory Tutorial Step by Step
• DWH INTERVIEW QUESTIONS
• Google Cloud SQL Tutorial
• Kusto Query Language (KQL) Tutorial
• MS Dynamics AX 2012 R2 Video Tutorial
• MariaDB Admin & Dev Tutorial
• MySQL / MariaDB Developer Tutorial Beginner to Advance
• MySQL DBA Tutorial Beginner to Advance
• SQL SERVER DBA INTERVIEW QUESTIONS
• SQL SERVER DBA Video Tutorial
• SQL Server / TSQL Tutorial
• SQL Server 2016
• SQL Server High Availability on Azure Tutorial
• SQL Server Scripts
• SQL Server on Linux Tutorial
• SSIS INTERVIEW QUESTIONS
• SSIS Video Tutorial
• SSRS INTERVIEW QUESTIONS
• SSRS Video Tutorial
• TSQL INTERVIEW QUESTIONS
• Team Foundation Server 2013 Video Tutorial
• Team Foundation Server 2015 Video Tutorial
• Windows Server 2012 R2 Installation Videos

SSIS - Use Variable in Execute SQL Task ( Update Statement )

Scenario : , solution : .

Aivivu - đại lý chuyên vé máy bay trong nước và quốc tế vé máy bay đi Mỹ lịch bay từ mỹ về việt nam các đường bay từ canada về việt nam giá vé máy bay từ nhật về việt nam vé máy bay từ hàn quốc sang việt nam Vé máy bay từ Đài Loan về VN các khách sạn cách ly ở quảng ninh

20 August 2008

622010 views

Passing Variables to and from an SSIS task

In which Patrick Index casts a jaundiced eye on SSIS, and decides that, for all its faults, it has a number of uses as an ETL tool. In the first of a series of articles 'from the trenches', Patrick describes how to pass variables to, and from, an SSIS task.

Like it? SSISht!

Like it or loathe it, SSIS is here to stay.  I suppose it’s nice and graphical, and it is also aesthetically  pleasing when you finally get a screen full of green tasks – I tend to leave my screen displaying for a while when this happens, so that everyone can see it whilst I go and make a coffee. SSIS is much richer than DTS .  Additionally you quite often see jobs for SSIS specialists; it would seem that companies are using it as their de-facto ETL tool, standing apart from SQL Server.

SSIS is, by its very nature, frustrating to work with because it is a mish-mash of dissimilar development environments, and I don’t find that the syntax is always intuitive.

There doesn’t seem to be a great deal of material on the web and it can be hard to find good examples to use as guidelines.  So, in the spirit of building up a knowledge base, and hopefully persuading Tony to set up a dedicated section on Simple-Talk for SSIS, I have constructed an example to demonstrate passing variables into and out of an ‘ Execute SQL Task and Script Task’ .

Passing Variables to and from an ‘Execute SQL Task and Script Task’.

The two tasks do fundamentally the same thing, which is to try and date-stamp a file.  The final variable value “ FullPath ” could then be easily used by a File System Task to copy/move or delete a file perhaps.

I suppose most SQL Server developers would be more comfortable knocking up this fairly trivial code in SQL, but the difficulty is in passing and catching the input variables in the task.   This example demonstrates the problem.

I have set up a package with three String variables called

• FileName, which has a data type of String and an initial value of “Import.txt”
• FolderName, which has a data type of String and an initial value of “c:\”
• FullPath, which has a data type of String and no initial value

… and an ‘ Execute SQL Task and a Script’   Task.

The package is called, rather imaginatively, “Package3”. The scope of the variables is at the package level.  One thing to note when you set up variables (choose SSIS-VARIABLES from the top menu) is to make sure you have clicked on the package and not a task when you create or add a variable.  If you create a variable while being clicked on a task (therefore with task scope) then the variable will disappear from the list when you click up to the package.  Sorry to be an old dog but I initially found this a bit confusing.

The simplest way to inspect your variables is to set a break-point on the task (right click on the task and choose EDIT BREAKPOINTS) for the OnPostExecute event of the task.  This will then allow you to  inspect the value of the variable after the task has completed its execution.  The red dots on the tasks indicate that there are already breakpoints set up on the task.

Doing it the ‘Execute SQL Task’ way

In the ‘Execute SQL Task Editor’ in the ‘Parameter Mapping’ section, (double-click on the task and choose Parameter mapping), I have set it up so that the two variables i.e. User::FolderName and User::FileName are added to the dialogue box.  Each has a Direction of “Input” which seems logical and a data type of VARCHAR.  The parameter names that I have used are just the ordinal positions of 0 and 1, which is what the context help suggests.  In other words, the value of User::FolderName is the first input parameter and User::FileName is the second input parameter.  The parameter lengths are 50. In other words, we are dealing with a varchar(50) parameter.  The initial default values for these, when you set them up, are -1 which tells me nothing I am afraid.

For the Result Set options on the Execute SQL Task, I have put in the aliased name of the output column from my query, which, giving my poetic instincts full rein, I have called FullPathFromQuery , and I want to pass this to my variable User::FullPath .

‘So what about the query?’ you may ask.  Well, if we go back to the General option on the Execute SQL Task Editor, you can see the code and I will list it again here

For such trivial code you would not want to set up a stored procedure I suspect, so the option of passing parameters to a stored procedure is not really there.

The only way to pick up these input variable values is to use question marks “?” in the order that they are passed.  This query as it stands will clearly not parse in query analyser window so you can only really test your code by running it in the task: This is not ideal.

You must also set the ResultSet option to be “Single row”.

If you run this to the break point one can see that the variable User::FullPath has been assigned the value  C:\\Import_200870805.txt ( I ran this on 6th Aug  2008) so the code must be working.

Using a Script Task instead

Perhaps a simpler way to do this is just to use the Script Task.  The trivial code again demonstrates how to pick up and assign values to and from variables in the vb.net code.  You need to tell the task which variables it is going to use by adding them to the ReadOnlyVariables  and ReadWriteVariables options in the Script Task editor window and also be aware that the variables names are case-sensitive.

The code which shows the passing and assignment of the variables in the script is shown below.

If you put a breakpoint on the task the value of the variable can be inspected to give tomorrows date, and it should look like this…

So which approach is best? 

People tell me that using question marks was how it was in DTS, but I have to say I didn’t find setting up the Execute SQL Task particularly intuitive.  The script task for me seems like the simplest solution of the two, surprisingly.

So the example is not going to win a Nobel Peace Prize for complexity but it does demonstrate the awkwardness of SSIS in the real world.  Hopefully the article will encourage readers to publish their experiences using the various tasks and we can build up a knowledge base of real world examples for everyone to reference on the simple-talk site.  I have recently been wrestling with the XML task and data source which will be the subject of my next article.

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

Rate this article

Patrick Index

I have been contracting for 14 years and before that I worked for a software house for 5 years in the late 80's. I started out as a Lotus 1-2-3 developer and then did database development with Paradox. That soon became Access development which progressed to Access with SQL Server in a client server scenario and now I suppose I would describe myself as a SQL Server developer.

Follow Patrick Index via

View all articles by Patrick Index

Load comments

Related articles

Warehouse Load Patterns – Part 1 – Requirements and General Patterns

What’s new in T-SQL in SQL Server 2022

Power bi: etl or not etl, that’s the question.