sql task editor parameter mapping ssis

20 August 2008

623699 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 2 – Load Patterns

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

Feature flags in data projects.

Configure the SQL Server Integration Services For Loop Container

By: Dallas Snider   |   Comments   |   Related: More > Integration Services Control Flow Transformations

How can I configure the SQL Server Integration Services For Loop Container? What options are available? How do you establish variables? How can you test the process?

The SQL Server Integration Services (SSIS) For Loop Container will allow us to repeat a task or tasks x number of times as if we had written a "for loop" in most programming languages. This tip was written using SQL Server 2016 Community Technology Preview 2.0's SQL Server Data Tools in Visual Studio Ultimate 2013. The steps shown here will also work in previous versions of SSIS.

To begin this tip, I used the T-SQL below to create a table that will allow us to watch for changes in our control loop variable.

In SSIS, we need to create a variable for our counter. Select Variable from the SSIS menu as shown below.

We will add a variable named Counter with the Int32 data type.

In this tip, we will use the For Loop Container to repeat an Execute SQL Task. Drag an Execute SQL Task onto the Control Flow palette and double-click to display the General page of the Execute SQL Task Editor. Select ADO.NET as the Connection Type and create an ADO.NET connection if one does not already exist.

Then enter the following in the SQL Statement text box:

Click on Parameter Mapping on the left side of the Execute SQL Task Editor to display the Parameter Mapping page of the Execute SQL Task Editor. In the Variable Name column select User::Counter (it should be at or near the bottom of the drop-down list). Select Input as the Direction, Int32 as the Data Type, and enter "Counter" for the Parameter Name. We will leave the Parameter Size at -1. Click on OK when finished.

Before adding the For Loop Container, it is a good programming practice to make sure the code to be repeated in a loop works correctly before invoking the loop.

Running a select query on our test table shows that the INSERT statement worked correctly. Remember that the Counter variable is initialized to zero.

Now we will drag a For Loop Container from the SSIS Toolbox to the Control Flow palette.

Next, we need to drag the Execute SQL Task onto the For Loop Container.

The red circle with white X indicates that we need to configure the For Loop Container. Double-click on the For Loop Container to display the For Loop Editor window. We will set values for the InitExpression, EvalExpression and AssignExpression so the Execute SQL Task executes 10 times.

For the InitExpression, we will initialize the Counter value to zero.

• The EvalExpression needs to evaluate to True or False. For the EvalExpression, we will test to see if the Counter value is less than 10. When the EvalExpression evaluates to False, the for loop will stop.
• The AssignExpression is where we will increment our counter variable. In this tip, we will increment our counter by 1.
• Click on OK to save these configuration changes.

Notice that the red circle with the white X has disappeared. Now it is time to test the For Loop Container. Before testing, I dropped and recreated the test table that was created above.

The package runs successfully.

Querying the table shows that our for loop worked as designed.

The amount to increment and the terminating conditions can be set using variables instead of being hard-coded as we see in this tip.

Check out more tips and tutorials about SSIS on MSSQLTips.com.

• SQL Server Integration Services (SSIS) Tutorial
• Configure the Flat File Source in SQL Server Integration Services 2012 to read CSV files

Case Statement Functionality in SQL Server Integration Services Derived Column Transformation

• Introduction to the Dimension Processing Destination in SSIS 2012

About the author

Comments For This Article

Related Content

Using SFTP with SQL Server Integration Services

How to Retry SQL Server Integration Services Control Flow Tasks

Using Expressions On Precedence Constraints in Integration Services

Transfer Database Task and Transfer SQL Server Objects Task in SSIS

Transfer SQL Server Jobs Task and Transfer SQL Server Logins Task in SSIS

SQL Server Integration Services FTP Task for Data Exchange

Related Categories

Data Transformation Services

Integration Services Analysis Services Tasks

Integration Services Connection Managers

Integration Services Control Flow Transformations

Integration Services Data Flow Transformations

Integration Services Development

Integration Services Error Handling

Integration Services Excel

Integration Services Execute Package Options

Integration Services Oracle

Development

Date Functions

System Functions

JOIN Tables

SQL Server Management Studio

Database Administration

Performance

Performance Tuning

Locking and Blocking

Data Analytics \ ETL

Microsoft Fabric

Azure Data Factory

Integration Services

Popular Articles

SQL Date Format Options with SQL CONVERT Function

SQL Date Format examples using SQL FORMAT Function

SQL Server CROSS APPLY and OUTER APPLY

DROP TABLE IF EXISTS Examples for SQL Server

SQL Server Cursor Example

SQL CASE Statement in Where Clause to Filter Based on a Condition or Expression

Rolling up multiple rows into a single row and column for SQL Server data

SQL Convert Date to YYYYMMDD

SQL NOT IN Operator

Resolving could not open a connection to SQL Server errors

Format numbers in SQL Server

SQL Server PIVOT and UNPIVOT Examples

Script to retrieve SQL Server database backup history and no backups

How to install SQL Server 2022 step by step

An Introduction to SQL Triggers

Using MERGE in SQL Server to insert, update and delete at the same time

List SQL Server Login and User Permissions with fn_my_permissions

SQL Server Loop through Table Rows without Cursor

How to monitor backup and restore progress in SQL Server

SQL Server Database Stuck in Restoring State

SSIS Execute SQL Task : Mapping Parameters And Result Sets

Arie D. Jones

A very common scenario in an ETL process is one in which you need to call out to some configuration tables to figure out some values that it needs to use somewhere downstream in the process. Quite often you also want to pass in some parameters as well. So in this article I will be addressing both of those scenarios. To start, you will just need to drop an Execute SQL Task item onto the Control Flow of an SSIS package and set up the initial configuration so that it is basically given a connection string

Now we want to enter in our SQL statement that will contain the parameters. In this case we are using a SQL Server connection and I will be calling a stored procedure. So I will provide ?s for each of the parameters involved. So my statement look like this…

exec Staging.usp_GetTicketQueries ?,?,?

I could also be more verbose if I wanted to

exec Staging.usp_GetTicketQueries  @StartDate=?,@EndDate=?,@SomeOtherValue=?

but it isn’t entirely necessary. Now that I have the statement ready I just need to click on the Parameter Mapping menu item on the left hand side and map my parameters.

Notice that there isn’t anything really tricky here. I line out the variables that will be used with the appropriate type and size. The only part that trips a lot of people up is the fourth one which is the Parameter Name. When you initially add an additional parameter to map this column will have a text name in it. Instead what I have done is to change these so that instead it has the 0 based index of the parameters in my statement. Once you have them mapped to the proper index value then everything should be golden.

Now we want to grab that result set that is being sent from the stored proc and map its values to some different variables. So we need to go back to the General settings screen and in the “Result Set” section change the value of the ResultSet property to something other than None in order to be able to make changes to the ResultSet screen later. In this case, I am just returning a single row. So I merely change the value to Single row and I am ready to map the result set.

Now switch to the Result Set screen via the menu on the left. Here what you will want to do is to map the actual column names of the result set to the variables that you want them to be placed into. Pretty simple. In the end, you will have something that looks like this.

Now you can click OK as you are finished with the process. This is actually a fairly handy thing to know in order to make your ETL processes a little more dynamic.

Sign up for more like this.

• SQL Server training
• Write for us!

Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types

In this article, I will give an overview of Execute SQL Task in SSIS and I will try to illustrate some of the differences between writing an expression to evaluate SqlStatementSource property or writing this expression within a variable and change the Execute SQL Task Source Type to a variable.

This article is the fourth 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

From the SSIS toolbox item description, Execute SQL Task in SSIS is used to execute SQL statements or stored procedures in a relational database.

Figure 1 – Execute SQL Task description

The SQL statement can be a:

• Single SQL Command
• Multiple SQL Commands
• Stored procedure

Figure 2 – Execute SQL Task in SSIS

This Task need a connection manager to establish a connection with a data source and it supports several data sources other than SQL Server since it can use the following connection managers:

• Excel: An Excel connection manager enables a package to connect to a Microsoft Excel workbook file
• OLE DB: An OLE DB connection manager enables a package to connect to a data source by using an OLE DB provider
• ODBC: An ODBC connection manager enables a package to connect to a variety of database management systems using the Open Database Connectivity specification (ODBC)
• ADO: An ADO connection manager enables a package to connect to ActiveX Data Objects (ADO) objects, such as a recordset
• ADO.NET: An ADO.NET connection manager enables a package to access data sources by using a .NET provider
• SQL Compact (SQLMobile): A SQL Server Compact connection manager enables a package to connect to a SQL Server Compact database

Note that the SQL Statements syntax differs based on the data source provider, as example you have to write T-SQL when data source is SQL Server. But when it is Excel or Access, SQL commands have to be supported by Microsoft.Jet.OLEDB and Microsoft.ACE.OLEDB providers.

Is Excel a relational database?

As mentioned above, Execute SQL Task in SSIS is used to execute SQL statements on a relational database, while Microsoft Excel is not a database engine. Because Excel is widely used to store data, and many users want to retrieve data using SQL Statements, Microsoft has given the ability to query tabular data stored in Excel and text file using Jet and ACE OLE DB providers, which pretend that Excel is a relational database and tries to force some data integrity rules while reading, i.e. forcing a single data type for each column by only reading a dominant data type and converting all other values to NULL.

Execute SQL Task configuration

As shown in the image below, the Execute SQL Task in SSIS has many options that can be configured. This article will only focus on the SQL Statement configuration. If you need more details on other options, you can refer to the official documentation .

Figure 3 – Execute SQL Task editor

There are different methods to define the SQL Statement that we need to execute. You can select the method in the SQLSourceType property:

• Direct Input: You can write the SQL Statement manually in SQLStatement property; it can be a simple query or you can just enter a stored procedure name and change the IsQueryStoredProcedure property to True
• File Connection: You can select a text file or .sql file that contains an SQL Statement using a File Connection manager
• Variable: You can select a variable that contains an SQL Statement; when changing the SQLSourceType property to Variable, a new property appears in the editor which is SourceVariable

Another method to set the SQL Statement is by using expressions, you can go to the Expression Tab, and select the SQLStatementSource property to write an expression. To learn more about writing expressions in SSIS, you can refer to the official documentation: Integration Services (SSIS) Expressions .

SqlStatementSource expression vs Variable Source Type

Many times I was asked on the difference between writing the SQL Statement within a variable and use it as a Source or using expressions to define the SQL Statement. And if one approach is more recommended.

In this section, I will first mention each method description from the official documentation of Execute SQL Task in SSIS, then I will try to clarify more based on my experience.

Figure 4 – SQLStatementSource expression

Official Documentation

Referring to the official documentation of Execute SQL Task in SSIS :

• Variable Source: Set the source to a variable that defines the Transact-SQL statement. Selecting this value displays the dynamic option, SourceVariable
• SqlStatementSource expression: When you use an OLE DB connection manager, you cannot use parameterized subqueries because the Execute SQL Task in SSIS cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task

After mentioning each approach description, and based on my experience, I can say that there is no better approach, each one has its own use case.

As mentioned above using expression is needed if the SQL Command is built based on other variables, such as passing the table name dynamically, or passing parameter because  Execute SQL Task in SSIS doesn’t support dynamic queries such as :

The first approach can be used if the developer decided to store the whole SQL Command inside a variable even if the variable is evaluated as an expression.

In case that variable is evaluated as an expression, there is no difference between both approaches from the SQL command perspective, but it may differ in case that the statement is used multiple times within the package. As an example of the query mentioned above, if the table name is variable and this query needs to be executed at different steps within the package, evaluating the variable as an expression is more efficient than writing the expression multiple times. Also, using a variable may facilitate the debugging process since it can be easily monitored.

In addition, as I remember, in the old SSIS versions, the variable source option was not available and it was added later (I think in 2012) version to facilitate the developer work in case the whole SQL command is stored inside a variable rather than adding one variable expression in SQLStatementSource, as an example if the query is built within a Script Task and stored in a variable.

In the end, it is up to you to decide which approach you feel more comfortable with based on what we have mentioned.

Table of contents

• Recent Posts

• An overview of SQL Server monitoring tools - December 12, 2023
• Different methods for monitoring MongoDB databases - June 14, 2023
• Learn SQL: Insert multiple rows commands - March 6, 2023

Related posts:

• SSIS Connection Managers: OLE DB vs ODBC vs ADO.NET
• Import data from multiple Excel files into SQL Server tables using an SSIS package
• SSIS Expression Tasks vs Evaluating variables as expressions
• SSIS OLE DB Source: SQL Command vs Table or View
• SSIS: Execute T-SQL Statement Task vs Execute SQL Task

How to Execute Stored Procedure in SSIS Execute SQL Task in SSIS

• SSIS SQL Server Editions
• SSIS Data Flow Destinations
• SSIS Interview Questions
• SSIS Tutorial
• 64-Bit Issues In SSIS
• Guide for Accessing a Heterogeneous Data In SSIS
• Administering SSIS Using The SSIS Catalog
• Advanced Data Cleansing in SSIS
• Fuzzy Lookup and Fuzzy Grouping in SSIS
• SSIS – Analysis Services Processing Task, Execute DDL Task
• BreakPoints In SSIS
• Building the User Interface - SSIS
• Bulk Insert Task in SSIS Package - SSIS
• Learn Cache Connection Manager and Cache Transform in SSIS
• Clustering SSIS
• Command-Line Utilities In SSIS
• Creating the Basic Package in SSIS
• Data Extraction In SSIS
• Data Flow Task in SSIS
• Data Loading In SSIS
• Data Preparation Tasks in SSIS
• Data Profiling Task in SSIS with Example
• Introduction to Data Quality Services (DQS) Cleansing Transformation
• Deployment Models In SSIS
• Developing a Custom SSIS Source Component
• Developing a Custom Transformation Component - SSIS
• Dimension Table Loading In SSIS
• Error Outputs in SSIS
• Error Rows in SSIS
• Essential Coding, Debugging, and Troubleshooting Techniques - SSIS
• Event Handling - SSIS
• Excel and Access In SSIS
• SSIS Architecture
• External Management of SSIS
• Fact Table Loading
• Flat Files In SSIS
• Create SSIS Package in SQL Server
• How to get Solution Explorer in Visual Studio 2013 - SSIS
• How to Use Derived Column Transformation in SSIS with Example - SSIS
• Importing From Oracle In SSIS
• How to do Incremental Load Data using Control Table Pattern in SSIS
• Software Development Life Cycle in SSIS
• Introduction to SSIS
• Literal SQL - SSIS
• Logging in SSIS
• Lookup Transformation in SSIS
• Overview of Master Data Services SQL Server in SSIS
• Using the Merge Join Transformation in SSIS
• Monitoring Package Executions - SSIS
• Import and Export Wizard in SSIS with SQL Server Data
• Null in SQL Server - SSIS
• What is Open Database Connectivity (ODBC) in SSIS
• Package Configuration Part II - SSIS
• Package Configurations Part I - SSIS
• Package Log Providers - SSIS
• Package Transactions - SSIS
• Performance Counters In SSIS
• Pipeline Performance Monitoring In SSIS
• Restarting Packages In SSIS
• Scaling Out in SSIS
• Scheduling Packages in SSIS
• SSIS Script Task Assign Value to Variable
• Scripting in SSIS
• Security Of SSIS Packages
• SQL Server Boolean Expression In SSIS
• SQL Server Concatenate In SSIS
• SQL Server Data Tools for Visual Studio 2013
• SQL Server Date Time - SSIS
• SQL Server Management Objects Administration Tasks In SSIS
• SQL Server The Data Flow Sources in SSIS 2014
• SQL string functions
• Conditional Expression In SSIS
• SSIS Container
• SSIS Data Flow Design and Tuning
• SSIS Data Flow Examples in SSIS
• SSIS Expressions
• SSIS Script Task
• SSIS Software Development Life Cycle
• SSIS Pipeline Component Methods
• The SSIS Engine
• Typical Mainframe ETL With Data Scrubbing In SSIS
• Understanding Data Types Using Variables, Parameters and Expressions - SSIS
• Understanding The DATA FLOW in SSIS
• SSIS Master ETL Package Overview
• Using Precedence Constraints In SSIS
• Using the Script Component in SSIS
• Using T-SQL With SSIS
• Using XML and Web Services In SSIS
• Various Types of Transformations In SSIS - 2014
• Versioning and Source Code Control - SSIS
• Windows Management Instrumentation Tasks In SSIS
• SSIS Workflow Tasks – Integration Services
• Working with SQL Server 2014 Change Data Capture In SSIS
• SSIS Projects and Use Cases
• Explore real-time issues getting addressed by experts
• Test and Explore your knowledge

Execute SQL Task

The Execute SQL Task is one of the most widely used tasks in SSIS for interacting with an RDBMS Data Source. The Execute SQL Task is used for all sorts of things, including truncating a staging data table prior to importing, retrieving row counts to determine the next step in a workflow, or calling stored procedures to perform business logic against sets of staged data. This task is also used to retrieve information from a database repository. The Execute SQL Task is also found in the legacy DTS product, but the SSIS version provides a better configuration editor and methods to map stored procedure parameters to read back the result and output values. This section introduces you to all the possible ways to configure this task by working through the different ways you can use it. You’ll work through how to execute parameterized SQL statements or execute batches of SQL statements, how to capture single-row and multiple-row results, and how to execute stored procedures.

Executing a Parameterized SQL Statement

The task can execute a SQL command in two basic ways: by executing inline SQL statements or by executing stored procedures. The resulting action can also result in the need to perform one of two options: accepting return values in parameters or a result set. You can get an idea of how the task can be configured to do these combinations in the General tab of the Execute SQL Task Editor, shown in Figure 3-21. Here, the Execute SQL Task is set to perform an Update operation on the DimProduct table using an inline SQL statement with a variable-based parameter. This is the easiest use of the Execute SQL Task because you don’t need to configure the Result Set tab properties.

Notice in Figure 3-21 that the General tab contains the core properties of the task. Here the task is configured to point to an OLE DB connection. The other options for the ConnectionType include ODBC, ADO, ADO.NET, SQLMOBILE, and even EXCEL connections. The catch to all this connection flexibility is that the Execute SQL Task behaves differently depending upon the underlying data provider. For example, the SQLStatement property in Figure 3-21 shows a directly inputted T-SQL statement with a question mark in the statement. The full statement is here:

This ?, which indicates that a parameter is required, is classic ODBC parameter marking and is used in most of the other providers — with the exception of the ADO.NET provider, which uses named parameters. This matters, because in the task, you need to configure the parameters to the SQL statement in the Parameter Mapping tab, as shown in Figure 3-22.

Here the parameter mapping collection maps the first parameter [ordinal position of zero (0)] to a user variable. When mapping parameters to connections and underlying providers, use the following table to set up this tab in the Task Editor:

Because we are using an OLE DB provider here, the parameter marker is ?, and the parameter is using the zero-based ordinal position. The other mapping you would have needed to do here is for the data type of the parameter. These data types also vary according to your underlying provider. SSIS is very specific about how you map data types, so you may need to experiment or check tutorials Online for the mapping equivalents for your parameters and provider. We’ll cover many of the common issues in this regard throughout this section, but for this initial example, we mapped the System::ContainerStartTime to the OLE DB data type of DATE. At this point, the Execute SQL Task with this simple update statement could be executed, and the ModifyDate would be updated in the database with a current DateTime value.

A variation of this example would be a case in which the statement can be dynamically generated at runtime and simply fired into the Connection Manager. The SQLSourceType property on the General tab allows for three different types of SQL statement resolution: either directly input (as we did), via a variable, or from a file connection. Another way to build the SQL statement is to use the Build Query action button. This brings up a Query-By- Example (QBE) tool that helps you build a query by clicking the tables and establishing the relationships. The variable-based option is also straightforward. Typically, you define a variable that is resolved from an expression. Setting the SQLSourceType property in the Execute SQL Task to Variable enables you to select the variable that will resolve to the SQL statement that you want the task to execute.

The other option, using a file connection, warrants a little more discussion.

Executing a Batch of SQL Statements

If you use the File Connection option of the Execute SQL Task’s SQLSourceType property, typically you are doing so to execute a batch of SQL statements. All you need to do is have the file that contains the batch of SQL statements available to the SSIS package during runtime. Set up a File Connection to point to the batch file you need to run. Make sure that your SQL batch follows a few rules. Some of these rules are typical SQL rules, like using a GO command between statements, but others are specific to the SSIS Execute SQL Task. Use these rules as a guide for executing a batch of SQL statements:

• Use GO statements between each distinct command. Note that some providers allow you to use the semicolon (;) as a command delimiter.
• If there are multiple parameterized statements in the batch, all parameters must match in type and order.
• Only one statement can return a result, and it must be the first statement.
• If the batch returns a result, then the columns must match the same number and properly named result columns for the Execute SQL Task. If the two don’t match and you have subsequent UPDATE or DELETE statements in the batch, these will execute even though the results don’t bind, and an error results. The batch is sent to SQL Server to execute and behaves the same way.

Returning results is something that we haven’t explored in the Execute SQL Task, so let’s look at some examples that do this in SSIS.

Capturing Singleton Results

On the General tab of the Execute SQL Task, you can set up the task to capture the type of result that you expect to have returned by configuring the ResultSet property. This property can be set to return nothing, or None, a singleton result set, a multi-line result, or an XML-formatted string. Any setting other than None requires configuration of the ResultSet tab on the editor. In the Result Set tab, you are defining the binding of returned values into a finite set of SSIS variables. For most data type bindings, this is not an issue. You select the SSIS variable data type that most closely matches that of your provider. The issues that arise from this activity are caused by invalid casting that occurs as data in the Tabular Data Stream (TDS) from the underlying provider collides with the variable data types to which they are being assigned. This casting happens internally within the Execute SQL Task, and you don’t have control over it as you would in a Script Task. Before you assume that it is just a simple data type–assignment issue, you need to understand that SSIS is the lowest common denominator when it comes to being able to bind to data types from all the possible data providers. For example, SSIS doesn’t have a currency or decimal data type. The only thing close is the double data type, which is the type that must be used for real, numeric, current, decimal, float, and other similar data types.

Check Out SSIS Tutorials

The next example sets up a simple inline SQL statement that returns a single row (or singleton result) to show both the normal cases and the exceptional cases for configuring the Execute SQL Task and handling these binding issues. First, we’ll use a simple T-SQL statement against the AdventureWorks database that looks like this (code file Ch03SQL.txt):

SELECT TOP 1 CarrierTrackingNumber, LineTotal, OrderQty, UnitPrice From Sales.SalesOrderDetail

We’ve chosen this odd result set because of the multiple data types in the SalesOrderDetail table. These data types provide an opportunity to highlight some of the solutions to difficulties with mapping these data types in the Execute SQL Task that we’ve been helping folks with since the first release of SSIS.

To capture these columns from this table, you need to create some variables in the package. Then these variables will be mapped one-for-one to the result columns. Some of the mappings are simple. The CarrierTrackingNumber can be easily mapped to a string variable data type with either varchar or varchar data types in the Execute SQL Task. The OrderQty field, which is using the smallint SQL Server data type, needs to be mapped to an int16 SSIS data type. Failure to map the data type correctly will result in an error like this:

[Execute SQL Task] Error: An error occurred while assigning a value to a variable “OrderQty”: “The type of the value being assigned to a variable “User::OrderQty” differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.”

The other two values, for the SQL Server UnitPrice (money) and LineTotal (numeric) columns, are more difficult. The closest equivalent variable data type in SSIS is a double data type. Now the parameters can simply be mapped in the Execute SQL Task Result Set tab, as shown in Figure 3-23. The Result Name property maps to the column name in your SQL statement or its ordinal position (starting at 0). 

Just use the Add and Remove buttons to put the result elements in the order that they should be returned, name them according to the provider requirements, and get the right data types, and you’ll be fine. If these are in the incorrect order, or if the data types can’t be cast by the Execute SQL Task from the TDS into the corresponding variable data type, you will get a binding error. This should give you a general guide to using the Execute SQL Task for capturing singleton results.

Multi-Row Results

Typically, you capture multi-row results from a database as a recordset or an XML file (particularly between SQL Server Data Sources) to use in another Script Task for analysis or decision-making purposes, to provide an enumerator in a Foreach or Looping Task, or to feed into a Data Flow Task for processing. Set up the SQLSourceType and SQLStatement properties to call either an inline SQL statement or a stored procedure. In either case, you would set the ResultSet property in the General tab to Full ResultSet, and the ResultSet tab is set up to capture the results. The only difference from capturing a singleton result is that you need to capture the entire result into a variable, rather than map each column. The data type you should use to capture the results varies according to what you are capturing. The XML file can be captured in either a string or an object data type. The recordset can only be captured in a variable with the object data type. An example of the Execute SQL Task configured to create an object data type to store the results of a selection of rows from the Sales. SalesOrderDetail table is shown in Figure 3-24. Note that the ResultSet tab shows the capturing of these rows with the required zero-ordinal position.

Once the recordset is stored as a variable, you can do things like “shred” the recordset. The term shredding means iterating through the recordset one row at a time in a Foreach Loop operation. For each iteration, you can capture the variables from, and perform an operation on, each row. Figure 3-25 shows how the Foreach Loop Container would look using the variable-based recordset. 

Another way to use the variable-based recordset is to use it to feed a data transform. To do this, just create a Source Script Transform in a Data Flow and add to it the columns that you want to realize from the stored recordset and pass in the recordset variable. Then add code (code file Ch03SQL.txt) similar to the following to turn the column data from the recordset into the output stream (to save time and space, only two columns are being realized in the recordset):

The XML version of capturing the result in a string is even easier. You don’t need to use the Script Component to turn the XML string back into a source of data. Instead, use the out-of-the-box component called the XML Source in the Data Flow. It can accept a variable as the source of the data. (Review the example demonstrating how to do this in the “Web Service Task” section of this chapter.) You can see that the Execute SQL Task is really quite useful at executing inline SQL statements and retrieving results, so now take a look at how you can use stored procedures as well in this task.

Executing a Stored Procedure

Another way to interact with an RDBMS is to execute stored procedures that can perform operations on a data source to return values, output parameters, or results. Set up the SSIS Execute SQL Task to execute stored procedures by providing the call to the proc name in the General tab’s SQLStatement property. The catch is the same as before. Because the Execute SQL Task sits on top of several different data providers, you need to pay attention to the way each provider handles the stored procedure call. The following table provides a reference to how you should code the SQLStatement property in the Execute SQL Task:

Returning to the earlier example in which you used an inline SQL statement to update the modified date in the sales order detail, create a T-SQL stored procedure that does the same thing (code file Ch03SQL.txt): In the online downloads for this chapter, we’ve created a package that demonstrates how to call this procedure using both the OLE DB and the ADO.NET Connection Managers. In the General tab (shown in Figure 3-26), the SQLStatement property is set up as prescribed earlier in the guide, with the ? parameter markers for the one input parameter. Note also that the IsQueryStoredProcedure property is not enabled. You can’t set this property for the OLE DB provider. However, this property would be enabled in the ADO.NET version of the Execute SQL Task to execute this same procedure. If you set the IsQueryStoredProcedure for the ADO.NET version to true, the SQLStatement property would also need to change. Remove the execute the command and the parameter markers to look like this:

Frequently Asked SSIS Interview Questions & Answers

Usp_UpdatePersonAddressModifyDate. In this mode, the Execute SQL Task will actually build the complete execution statement using the parameter listing that you’d provide in the Parameter Mapping tab of the Task Editor.

The Parameter Mapping tab of the Task Editor varies according to the underlying provider set on the Execute SQL Task, as shown in Figure 3-27.

For brevity, this figure just shows an OLE DB connection with parameters. With ADO.NET connections though, the parameter names follow the same rules you used when applying parameters to inline SQL statements earlier in this chapter by changing the Parameter Name option to @MODIFIED_DATE, for example.

Retrieving Output Parameters from a Stored Procedure

Mapping input parameters for SQL statements is one thing, but there are some issues to consider when handling output parameters from stored procedures. The main thing to remember is that all retrieved output or return parameters have to be pushed into variables to have any downstream use. The variable types are defined within SSIS, and you have the same issues that we covered in the section “Capturing Singleton Results” for this task. In short, you have to be able to choose the correct variables when you bind the resulting provider output parameters to the SSIS variables so that you can get a successful type conversion.

As an example, we’ll duplicate the same type of SQL query we used earlier with the inline SQL statement to capture a singleton result, but here you’ll use a stored procedure object instead. Put the following stored procedure in the AdventureWorks database (code file Ch03SQL.txt): In this contrived example, the stored procedure will provide four different output parameters that you can use to learn how to set up the output parameter bindings. (Integer values are consistent and easy to map across almost all providers, so there is no need to demonstrate that in this example.) One difference between returning singleton output parameters and a singleton row is that in the General tab of the Execute SQL Task, the ResultSet property is set to None, as no row should be returned to capture. Instead, the Parameters in the Parameter Mapping tab will be set to the Direction of Output and the Data Types mapped based on the provider.

To get the defined SQL Server data type parameters to match the SSIS variables, you need to set up the parameters with these mappings:

You might assume that you would still have an issue with this binding, because, if you recall, you attempted to return a single-rowset from an inline SQL statement with these same data types and ended up with all types of binding and casting errors. You had to change your inline statement to cast these values to get them to bind. You don’t have to do this when binding to parameters, because this casting occurs outside of the Tabular Data Stream. When binding parameters (as opposed to columns in a data stream), the numeric data type will bind directly to the double, so you won’t get the error that you would get if the same data were being bound from a rowset. We’re not quite sure why this is the case, but fortunately stored procedures don’t have to be altered in order to use them in SSIS because of output parameter binding issues. The remaining task to complete the parameter setup is to provide the correct placeholder for the parameter. Figure 3-28 is an example of the completed parameter setup for the procedure in OLE DB.

At this point, you have looked at every scenario concerning binding to parameters and result sets. Stored procedures can also return multi-row results, but there is really no difference in how you handle these rows from a stored procedure and an inline SQL statement. We covered multi-row scenarios earlier in this section on the Execute SQL Task. Now we will move away from tasks in the RDBMS world and into tasks that involve other controlling external processes such as other packages or applications in the operating system.

List of Related Microsoft Certification Courses:

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Vaishnavi Putcha was born and brought up in Hyderabad. She works for Mindmajix e-learning website and is passionate about writing blogs and articles on new technologies such as Artificial intelligence, cryptography, Data science, and innovations in software and, so, took up a profession as a Content contributor at Mindmajix. She holds a Master's degree in Computer Science from VITS. Follow her on LinkedIn.

Copyright © 2013 - 2024 MindMajix Technologies

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

This browser is no longer supported.

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

SSIS Execute SQL Task Editor and Parameter Mapping Order

So I have an Execute SQL Task Editor Task that executes a SQL Server Stored Procedure with 26 Parameters and forgot one...and it's about half-way down the list of 26.

Is there any way that I can add a Parameter into a specific position within the Execute SQL Task Editor Parameter Mapping? Or do I have to Remove all the subsequent Parameters and Re-Add in at the spot in which the Parameter is missing?

Thanks for your review and am hopeful for a reply.

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

Hi @Bobby P ,

It depends on what connection type the SSIS Execute SQL Task is using.

Check it out here: SSIS "Execute SQL Task" - stored procedure with input parameters

As you can see, the ADO.NET is using named parameters instead of numbers. It means that their sequential position is irrelevant, they are not position based.

Check execute-sql-task about the parameter names and markers in Execute SQL Task.

Also you may refer below about calling Stored Procedure in execute sql task in SSIS.

https://bageshkumarbagi-msbi.blogspot.com/2019/11/call-stored-procedure-in-execute-sql.html

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.

Do you have further question on this , could we offer more support? If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)