sql execute task result set

23 October 2012

487,381 views

Printer friendly version

SSIS Basics: Using the Execute SQL Task to Generate Result Sets

The Execute SQL Task of SSIS is extraordinarily useful, but it can cause a lot of difficulty for developers learning SSIS, or only using it occasionally. What it needed, we felt, was a clear step-by-step guide that showed the basics of how to use it effectively. Annette has once again cleared the fog of confusion

The Execute SQL task is one of the handier components in SQL Server Integration Services (SSIS) because it lets you run Transact-SQL statements from within your control flow. The task is especially useful for returning result sets that can then be used by other components in your SSIS package.

When using the Execute SQL task to return a result set, you must also implement the necessary variables and parameters to pass data into and out of the T-SQL statement called by the task. In this article, we look at how to use those variables and parameters in conjunction with the Execute SQL task in order to transfer that data. (In the previous article in this series, “ Introducing Variables ,” I explained how to work with variables, so refer back to that article if you need help.)

This article walks you through two different scenarios for working with variables, parameters, and result sets. In the first scenario, we’ll use two Execute SQL tasks. The first task retrieves a single value from a table in the AdventureWorks2008 database. That value is returned by the task as a single-row result set. The second Execute SQL task will pass that value into a stored procedure that inserts the row into a different table.

The second scenario uses a single Execute SQL task to retrieve a multi-row result set, also known as a full result set. This represents the third Execute SQL task we’ll be adding to our control flow. For now, all we’ll do is use this task to save the result set to variable. In articles to follow, you’ll see how you can use that variable in other SSIS components, such as the Foreach Loop container.

Setting Up Your Environment

Before adding components to your SSIS package, you should first add a table and two stored procedures to the AdventureWorks2008 database. The table will store the value that’s returned by the first Execute SQL task. Listing 1 shows the T-SQL necessary to create the SSISLog table.

TABLE SSISLog ID INT IDENTITY, DATETIME, INT)

Listing 1: Creating the SSISLog table

Next, we will add a stored procedure to insert data into the SSISLog table. Listing 2 provides the T-SQL script necessary to create the UpdateSSISLog stored procedure. Notice that it includes an input parameter. The input will be the data that will be retrieved via the first Execute SQL task.

PROCEDURE  UpdateSSISLog @EmpNum INT INTO SSISLog DateRun, Result) GETDATE(),EmpNum

Listing 2: Creating a stored procedure that inserts data into the SSISLog table

Once you’ve set up the table and stored procedures, you can create your SSIS package, if you haven’t already done so. We’ll perform both exercises in a single package. Our next step, then, is to add a couple variables to our package.

Adding Two Variables to the SSIS Package

The first variable we’ll create is the EmpNum variable. If the Variables window is not open, right-click the Control Flow workspace, and then click V ariables . In the Variables window, add a new variable by clicking on the Add Variable icon.

Name the new variable EmpNum , and ensure that the scope is set at the package level, as indicated by the package name. (In my case, I’ve stuck with the default name, which is Package .) Next, set the data type to Int32 and the value to 0 , as shown in Figure 1. The Execute SQL task will use the variable to store the value it retrieves from the database.

1586-Figure2-ad486543-2346-4d72-9ce7-b68

Figure 1: The new EmpNum variable

Now create a second variable named EmployeeList . This variable should also be at the package scope. However, set the data type to Object . We will be using this variable to store the full result set that we retrieve in our second scenario, and SSIS requires that the variable use the Object type to accommodate the multiple rows and columns.

Adding a Connection Manager to the SSIS Package

The next step is to create a connection manager that points to the AdventureWorks2008 database. Right-click the Connection Manager s window, and then click New OLE DB Connection , as shown in Figure 2.

1586-image2replace.jpg

Figure 2: Creating a new OLE DB connection manager

When the Configur e OLE DB Connection Manager dialog box appears, click the New button to launch the Connection Manager dialog box, shown in Figure 3. From the Server name drop-down list, select the name of your SQL Server instance, and then select an authentication type. From the Select or enter a database name drop-down list, select your database. As you can see in Figure 3, I’m using 192.168.1.19/ Cambridge as my SQL Server instance, SQL Server Authentication as my authentication type, and the AdventureWorks2008 as my database.

1586-Figure4-fbdc3784-dfdf-4fa8-9d29-c34

Figure 3: Configuring an OLE DB connection manager

Be sure to test the connection by clicking the Test Connection button. If the connection is good, click OK to close the Connection Manager dialog box.

When you’re returned to the Configure OLE DB Connection Manager dialog box, you’ll see that your new connection has been added to the Data connections section. Click OK to close the dialog box. Your connection should now be listed in Connection Managers window.

If you want, you can rename your connection manager to something more appropriate. To do so, right-click the connection, click R ename , and type in the new name. I renamed mine to AW2008 , as shown in Figure 4.

1586-Figure5-969088ac-b241-48f9-a6d2-f7e

Figure 4: Renaming a connection manager

Returning a Single-Row Result Set

As mentioned above, our first example uses two instances of the Execute SQL task. The first Execute SQL task will return a single-row result set, which in this case, will contain only one value. Note, however, that this is not a real-world example. I simply want to show you how to get the result set and use it.

In this example, we’ll retrieve the highest BusinessEntityID value from the HumanResources.Employee table and insert it into the SSISLog table, along with the current date and time. We’ll start by using the first Execute SQL task to retrieve the value and pass it to the EmpNum variable.

To get started, drag the Execute SQL task onto the Control Flow design surface. Then double-click the task to open the Execute SQL Task Editor . The editor opens to the General page, as shown in Figure 5.

1586-Figure6-55f66169-8d85-4d55-af47-1d8

Figure 5: The General page of the Execute SQL Task Editor

Notice that the General section contains the Name property and the Description property. The Name property refers to the task name. You should name the task something suitable. On my system, I named the task Get ResultSet . I then added a description to the Description property to explain what the task does.

In the Options section, I stuck with the default property values.

The next section on the General page is Result Set . Notice that this section includes only the ResultSet property. The property lets you select one of the following four options:

  • None : The query returns no result set.
  • Single row : The query returns a single-row result set.
  • Full result set : The query returns a result set that can contain multiple rows.
  • XML : The query returns a result set in an XML format.

The option you select depends on the results of the query you pass into the Execute SQL task. For this exercise, our query will return only a single value. Consequently, we will choose the Single row option.

Next, we need to configure the properties in the SQL Statement section. Table 1 shows the values you should use to configure these properties.

(or whatever you named the connection manager you created earlier)

This means we’ll type the code straight in and not use a stored procedure.

Because we’ve selected the Direct input option, we need to enter a T-SQL statement for this option. I’ve used the following statement, which returns a single value:

    

This option is greyed out because we selected for the property. Had we selected , this property would be available and the property would be greyed out.
The property defaults to . If you change the value to , you can click the button to verify that your T-SQL statement is valid.

Table 1: Configuring the properties in the SQL Statement section

Our next step is to associate our result set value with a variable that will store the value we retrieve from the database. To do this, go to the Result Set page of the Execute SQL Task Editor .

The main grid of the Result Set page contains two columns: Result Name and Variable Name . Click the Add button to add a row to the grid. In the Result Name column, enter the column name returned by your query ( MaxEmpID ). In the Variable Name column, select the User:: EmpNum variable. Your Result Set page should now look similar to the one shown in Figure 6.

1586-image6replace.jpg

Figure 6: Associating your result set value with a variable

If our single-row result set contains multiple columns, we would have had to map a variable to each column. However, because we returned only one value, we needed only one mapping.

Once you’ve associated your result set value with a variable, click OK to close the Execute SQL Task Editor . You task should now be set up to return a single-row result set. Now we need to do something with that result set!

Working with a Single-Row Result Set

Our next step is to drag a new Execute SQL task onto our design surface so we can use the result set returned by the first Execute SQL task. So add the task, and then connect the precedence constraint (the green arrow) from the first task to the new one. Next, right-click the second task and click Edit to open the Execute SQL Task Editor , shown in Figure 7.

1586-Figure9-6f69fff2-e1d8-4bb6-91a2-ffd

Figure 7: Configuring the Execute SQL Task Editor

In the General section, provide a name and description for the task. (I named the task Using Result Set .) For the ResultSet property, stick with the default value, None . In this case, the task won’t be returning a result set. Instead, we’ll be using the results returned by the previous task.

Now let’s look at the SQL Statement section shown in Figure 8. Notice that, for the SQLStatement property, I entered the following T-SQL code:

UpdateSSISLog ?

As you can see, we’re executing the UpdateSSISLog stored procedure. Notice, however, that we follow the name of the stored procedure with a question mark ( ? ). The question mark serves as a placeholder for the parameter value that the stored procedure requires. You cannot name parameters within the actual query, so we have to take another step to provide our value.

Go to the Parameter Mapping page of the Execute SQL Task Editor . On this page, you map the parameters referenced in your queries to variables. You create your mappings in the main grid, which contains the following five columns:

  • Variable Name : The variable that contains the value to be used for the parameter. In this case, we’ll use the User:: EmpNum variable, which contains the result set value returned by the first Execute SQL task.
  • Direction : Determines whether to pass a value into a parameter (input) or return a value through the parameter (output)
  • Data Type : Determines the type of data provided from the variable. This will default to the type used when setting up the variable.
  • Parameter Name : The name of the parameter. The way in which parameters are named depends on your connection type. When running a T-SQL statement against a SQL Server database through an OLE DB connection, as we’re doing here, we use numerical values to represent the statement’s parameters, in the order they appear in the statement, starting with 0 . In this case, because there’s only one parameter, we use 0 .
  • Parameter Size : The size of the parameter if it can be a variable length. The default is -1 , which lets SQL Server determine the correct size.

Once you’ve mapped your variable to your parameter, the Parameter Mapping page should look similar to the one shown in Figure 8.

1586-image8replace.jpg

Figure 8: Mapping a variable to a parameter

When you’re finished configuring the Execute SQL task, click OK .

Your package should now be ready to run. Click the green Execute button. When the package has completed running, query the SSISLog table and verify that a row has been added that contains the expected results.

Returning a Full Result Set

Using the Execute SQL task to return a full result set is similar to returning a single-row result set. The primary differences are that your target variable must be configured with the Object data type, and the task’s ResultSet property must be set to Full result set .

Let’s run through an example to demonstrate how this works. This time, rather than retrieving a single value, we’re going to retrieve a result set that contains multiple rows and columns.

For this exercise, we can use the same SSIS package we used for the previous example, but keep in mind, if you execute the package, all components will run unless you specifically disable those that you don’t want to have run.

Drag an Execute SQ L task to the design surface. Open the task’s editor and configure the properties as necessary. Remember to set the ResultSet property to Full result set . For the SQLStatement property, use the SELECT statement shown in Listing 3. When entering a long SELECT statement into as the property’s value, it’s easier to click the ellipses button to the right of the property to open the Enter SQL Query dialog box and then entering the statement there.

(YEAR, HireDate, GETDATE()) .BusinessEntityID, .FirstName , .LastName , .JobTitle , .Gender HumanResources.Employee E JOIN Person.Person P E.BusinessEntityID = P.BusinessEntityID (YEAR, HireDate, GETDATE()) >= 15

Listing 3: The SELECT statement used to return a full result set

After you enter your SELECT statement, close the Enter SQL Query dialog box. When you’re returned to the Execute SQL Task Editor , the General page should now look similar to the one shown in Figure 9.

1586-Figure10-f2793b11-f06f-4859-9c9a-e3

Figure 9: Configuring the Execute SQL task to return a full result set

Next, go to Result Set page and add a row to the main grid. Because we’re returning a full result set, you should enter 0 in the Result Name column. (The same is true if you’re returning an XML result set). Then, in the Variable Name column, select the User:: E mployeeList variable.

Once this is complete, click OK . Your Execute SQL task will now return a full result set and save it to the E mployeeList variable. (You should execute the task to make sure it runs.) You can then use that variable in other SSIS components. However, to do so is a bit more complicated than what we saw for a single-row result set, so I’ll save that discussion for another time. But feel free to try using the variable if your up for it. You might want to start with a Foreach Loop container.

In this article, I demonstrated how to use an Execute SQL task to return a single-row result set with a single value and save that value to a variable. I then showed you how to use the variable in a second Execute SQL task to insert data into a table.

In the second example, I demonstrated how to use an Execute SQL task to return a full result set and save it to a variable configured with the Object data type. Although I did not show you how to use the result set in other components, you should now have a good sense of the principles behind using the Execute SQL task to retrieve result sets and saving them to variables.

In future articles, I’ll demonstrate how you can use those result sets in other components, such as the Script task and the Foreach Loop container.

Subscribe for more articles

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

sql execute task result set

Annette Allen

Annette is a Microsoft SQL Server MVP, and has been a SQL Developer since 2000, starting work with a London City based Law firm before moving to Cornwall and working as a part Developer part Managerial role. She then worked as Developer for a health care company and in 2015 joined the University of Exeter where she was the SQL Server DBA. Now Annette is working as a remote DBA for WellData, a leading UK provider of database support. Contact Annette: Email: [email protected] Twitter: @Mrs_Fatherjack

Follow Annette Allen via

View all articles by Annette Allen

Load comments

Related articles

sql execute task result set

Introduction to SQL Server Spatial Data

  • T-SQL Programming

sql execute task result set

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

sql execute task result set

Feature Flags in Data Projects

Tim Mitchell, Data Solutions Architect

  • May 3, 2023 | Reflections on My First-Ever Presentation, Fifteen Years Later
  • December 1, 2022 | My Office Setup
  • October 31, 2022 | Returning to PASS Summit
  • March 31, 2022 | Creating a Generic SSRS Report
  • March 20, 2022 | What Stephen King Taught Me About Data Storytelling
  • June 7, 2021 | Let’s Talk About Your Development Environment
  • January 12, 2021 | Moving On From PASS. What’s Next?
  • January 8, 2021 | Connecting to SharePoint Lists with SSIS
  • December 22, 2020 | ETL Antipattern: Ignoring the “Why?”
  • December 21, 2020 | ETL Antipattern: Ignore the Logging

The SSIS Object Variable and Multiple Result Sets

Posted By: Tim Mitchell April 27, 2015

In my most recent post in this series, I talked about how to use the SSIS object variable as an ADO recordset as a source in a data flow. By loading the result set of a query into this variable, the contents of the variable can be read by an SSIS script component and sent out through the SSIS pipeline.

In working up the example in the previous blog post, I wondered to myself: What happens if the source query returns more than one result set?

Multiple Result Sets with the Execute SQL Task

In reality, data retrieval queries are typically configured to return exactly one result set. If two different result sets are needed, two separate queries are built, each with its own source-to-target flow of data. However, I’ve seen more than a few queries (typically in the form of vendor stored procedures, which, of course, cannot be edited) which return more than one set of results in a single execution. Even some of the built-in SQL Server stored procedures do this ( sp_help comes to mind). From wherever they come, any ETL process that consumes data generated by these types of queries or stored procedures must be configured to handle multiple result sets.

Thinking through the previous example, I wanted to experiment with what would happen if the query in an execute SQL task returns more than one result set. To test this, I created a stored procedure that purposefully returns two different result sets, as shown below:

image

Predictably, the stored procedure above returns two sets of results.

image

To continue the test, I’ve added an execute SQL task to the control flow, using the stored procedure above as the query. I set the ResultSet to Full Result Set.

image

Specifying a ResultSet value of Full Result Set requires the use of an object-typed variable to store the results.

image

Next, I’ll use a script component, configured as a source, to programmatically extract the information from the ADO recordset object. In my last post, I used an OleDbDataAdapter to fill a DataTable with the data from the object variable. In this case, I’ve changed that just a bit, instead populating a DataSet object which should allow us to capture more than one result set (since the DataSet can contain one or more DataTables). I’ve also added a second output, setting up the appropriate columns for the added result set. The snippet below shows the example of looping through each result set to build the data for each output. (Teaser: Before you try to execute this code, don’t execute this code. It won’t work. I’ll explain why below.)

When I update the script component source with this code and execute the package, it fails with the following error:

image

This scripting error is a bit cryptic, but it tells me all I need to know: “Cannot find table 1”. The second code block, which attempts to iterate over ds.Tables[1].Rows, fails because there is no table at index 1 (the second table, or what should be the second result set). I can infer from this that the execute SQL task has loaded only the first result set into the SSIS object variable, even though the stored procedure returned two different result sets. If I comment out the section that attempts to process the second table, the package runs fine and loads just the data from the first result set.

So if the execute SQL task does work for this scenario, how exactly does one retrieve data from a stored procedure or query which produces multiple result sets? The easiest method, as it turns out, is to use the script component to connect directly to the database and execute the query.

Handling Multiple Result Sets in the Script Component

All other things being equal, the simplest approach is usually the best. In this case, I can eliminate the middleman entirely (the execute SQL task) and connect directly to the source database from within the script component, retrieving the necessary data from all of the result sets. In testing this solution, I came across an excellent article written some years back by Bob Pearson , in which he describes and demonstrates (in VB.NET) the solution to the multiple result set quandary. With Bob’s permission, I have adapted the design pattern he described to C#.

In this pattern, the entire source operation – querying the source database and splitting out the result sets into their appropriate outputs – is handled in the script component. I should point out that this example uses the ADO.NET connection manager rather than the OleDB connection manager. Although it is technically possible to access the OleDB connection manager in script, the syntax to do so is a bit quirky, so I’ll keep it simple and use ADO.NET.

After making this change to bypass the SSIS object variable method and simply query the database directly from the script component source, the script component source runs successfully. I can then send the two outputs, each containing a distinct result set from the source stored procedure, to their respective output tables.

image

Tl;dr: In the rare cases when you have to process a query with multiple result sets, skip the SSIS variable and use a direct connection through the script component source.

Thanks again to Bob Pearson for allowing me to adapt his VB.NET code into C# for this example.

Share this:

About the author.

' src=

Tim Mitchell

Related articles.

sql execute task result set

SSIS Layout Formatting Options

Reusing a recordset in an SSIS object variable

Reusing a Recordset in an SSIS Object Variable

SSIS Data Taps

SSIS Data Taps

PASS Summit 2017

PASS Summit 2017 Day 2

12 comments on "the ssis object variable and multiple result sets".

' src=

https://msdn.microsoft.com/en-us/library/cc280492(v=sql.100) .aspx. use ADO.net as connection manager instead of OLEDB. it worked for me in 2008.

' src=

Thanks Tim This helped me a lot

' src=

Great, Sri! Glad it helped.

' src=

This is a great post! I have two outputs in my sp and I set up both in the Inputs and Outputs of the script task as well as in the script itself. I am trying to run it but get an error message:

Error: 0xC0047062 at Data Flow Task Load MedRec sp, Script Component Run SP [134]: System.FormatException: String was not recognized as a valid DateTime.

How do I catch which particular DateTime column gave me an error since the error did not mention it?

Unfortunately, finding out which column(s) caused an error is a bit clunky in SSIS. You could include some try/catch logic in your code to capture the name of the column. It takes some extra code to do this (especially if you have many columns to process) but it can help a lot with troubleshooting.

Just FYI, SSIS in 2016 is supposed to make getting the error column name quite a bit easier. However, I’ve yet to test how this would work in the script component.

' src=

I believe that I encountered this multiple result set error accidentally. I wanted to delete rows in a destination table where their datekeys were within the date range from my sort table: DECLARE @MinDateKey,@MaxDateKey INT

— query to set variables, but which inadvertently adds an resut set. SELECT @MinDateKey = MIN(DateKey),@MaxDateKey = MAX(DateKey) FROM StagingTable

— The stored procedure then runs another query that ssis parses and logs which results in a error message that is misleading at best. SELECT VariousReturnValuesThatAreAttemptedToBeParsedBySSIS

Fortunately I found this article and changed the above select to a pair of SETs to resolve the problem.

Thanks Tim!

Hi Thomas, thanks for the feedback. I’m glad this post helped!

' src=

Where does SalesOutputBuffer come from? How is it defined and where?

' src=

It took me a second to figure this out. I believe it is defined in the Inputs and Outputs of the Script Component. I believe you’d also want to add the columns in here that it is pulling in.

' src=

Does the connection manager for the source data have to have AllowMultipleResultsets=True set? Are we making that assumption in this article?

Steven, this design does not depend on that setting since we are working with a copy of the source data in SSIS memory.

' src=

Hi Tim. Very grateful to you for this excellent solution to the multiple result sets query. It helped me great deal in my work.

1 Trackbacks & Pingbacks

  • Dew Drop – April 25, 2015 (#2001) | Morning Dew

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed .

The Data Queen

Explorations in Business Intelligence with Microsoft

How To Set and Use Variables in SSIS Execute SQL Task

I couldn’t find any step-by-step resources on this so I thought I’d blog it.  This is done in SQL Server 2008 R2. In this scenario I will use a SQL Statement to populate an SSIS variable, and then use that variable in another SQL Statement.

Create your variables

If the variables window isn’t visible already, from the menu select View –> Other Windows –> Variables.

SNAGHTML1fb1c4da

This will bring up your variables window on the left hand side of your screen.  You should be able to tab between it and the Toolbox.  You may have to pin it to your screen first.

image

I have already created some variables in the screenshot above.  To create a variable, first decide on the scope of the variable, whether it is the entire package, a container or a specific task.  I normally scope all of my variables to the package, since you can’t see the variables in the window unless you have selected the scoped object.  I find it easier to manage variables having them all in one place.  I have not had any compelling reason not to do it this way, although I’m sure there are cases where you may want them scoped differently. 

Before creating the variable you must select the item for it’s scope. In this case select the canvas of the Control Flow which will permit you to scope the variable to the entire package.  Then create the variable by clicking on the Add Variable button at the top of the Variables window.

image

Give your variable a Name, a Data Type and a Value.  In this case we are working with EndDate, and although you would want to set it as Date, there are some compatibility issues when using the Date data type for a variable and then pulling into a SQL parameter.  I have found it is best to set the date as a string and then manipulate it from there.  Set your EndDate attributes as shown, with the Scope being the name of your package.

image

Populate an SSIS variable using a SQL statement

From the Toolbox, drag an Execute SQL Task onto your Control Flow.  Double click on it and set the following properties:

On the General tab:

  • Set the ResultSet to Single row
  • Select your Connection to the database where you will run your SQL Statement
  • Enter your SQL Statement

image

On the Result Set tab:

  • In the Result Name key in the name of the field being return in your SQL Statement.  In this case I named the field EndDate
  • In the Variable Name select the user variable you created.

image

Use the Variable in a SQL Statement

From the Toolbox, drag an Execute SQL Task onto your Control Flow. Double click on it and set the following properties:

  • Enter your SQL Statement, placing a ? where each variable is to be used.

image

In this case I need to add two Parameters to the Parameter Mapping tab, since I have used two question marks in my SQL Statement.  Even if you are using the same Variable multiple times, you still need to enter them separately on the Parameter Mapping tab, once for each time they appear in the SQL Statement.  This is the only way to identify which parameter is being used for which question mark.  Parameters are made much easier in 2012.

Here’s the tricky bit which isn’t at all self explanatory.

On the Parameter Mapping tab: 

  • Select the user variable in the Variable Name drop down which corresponded to the first variable (?) being used in your SQL Statement.
  • Set the Data Type to NVARCHAR, since we are using a string variable. (This is the spot where the data type compatibility didn’t mesh, which is why we opted for string).  
  • Set the ParameterName to 0.  This indicates the order in which the parameter is appearing in the SQL statement.  You can enter them in the pane in any order you want, but the ParameterName must contain numbers starting from 0 to however many parameters you have minus one.  So if you have two parameters you must have one ParameterName = 0 and one ParameterName = 1.  You can reverse the order, but they must both exist or the task will fail.

image

47 thoughts on “How To Set and Use Variables in SSIS Execute SQL Task”

Thanks, great stuff

Thanks, your help!

Hi, Thanks for the information given above.Its useful. Can you please let me know when to use single row set and full row set in Execute SQL task ?

Hi Karan, Single row set is when you only ever expect one row returned. Full row is when you expect multiple rows. Cheers.

Good stuff. Thanks.

Nice write up.It was exactly what I was looking for. Thank you, Martina!

Thanks a lot for this blog. It is really helpful.

Thanks Data Queen! This told me what I needed to know.

Is there a similar approach that would create a variable that holds a list of values? I need to hit one database to get a list of product codes (0-10 returned), and use that list as a filter in another query on another database (via a different connection manager), the resulting query uses an IN operator as the filter – eg. i want my code to show … prdct_cd in (@User::prod_ids) … and at runtime be able to generate … prdct_cd in (‘ABC123′,’ABH334′,’NBN667’).

Any suggestions?

You could try something like reading the list into an SSIS object variable, and then looping through that object to append each value to a string which you could use in your IN operator in the SQL statement. Cheers, Martina

Hi Matrina,

I am looking for similar kind.. Could you please provide me sample on how to create a variable that holds a list of values.

Thanks, Vamsi

Instead of a string variable you need to create a variable of type Object to hold the multiple values. Then you need to set the [Execute SQL Task] to Result Set to “Full result set” instead of “Single Row”. On the Result Set tab, be sure to set your Result Name = 0.

Cheers, Martina

Have you actually tried this? I’ve done what you said and used this query in the sql task but it is still not working.

SELECT ‘0’,’1′

Your query should read: SELECT ?,?

I am trying to set a variable value in Execute Sql Task but it is giving error like this

[Execute SQL Task] Error: An error occurred while assigning a value to variable “RowCount”: “Exception from HRESULT: 0xC0015005”.

I have Defined One Variable named as @RowCount as Int32

In General Tab I have done following settings Result set : Single Row Connection Type : OLEDB SourceType : Direct Input

SQL Statement

UPDATE [dbo].[ONE] SET [COLUMN 2] = ‘aaa’; Go select @@ROWCOUNT as rtn Go

and in Result set Tab Result Name rtn and Variable name User::RowCount

But Still Getting an Error Please Help

Thanks in Advance!!!

I would guess (I don’t know for sure) that the Execute SQL Task is unable to manage two statements to set the variable. It may be looking for the variable in the first statement (the update statement). Try just using a single select statement rather than including the update statement. You could run one Execute SQL statement to populate the variable “SELECT count([COLUMN 2]) as rtn FROM [ONE]”, and then run a second Execute SQL Statement to update the table.

Thanks. Really helped me get this task done quickly

Thank you, good example

Is it possible to pass the variable from your SQL Task to a File System Task?

How do you verify what is in your variable after the execution of the SQL Task? In other words how do you display what is in User::EndDate?

I am trying to pass a variable (User::EndDate) to a File System Task which renames a file (example – C:Folder + “Filename” + @[User::EndDate].csv.

Is this possible?

Thanks, Sqlraider

Hi Sqlraider,

You can definitely pass a variables to a File System Task. See this post http://www.bidn.com/blogs/KeithHyer/bidn-blog/2465/copy-and-rename-a-file-in-ssis-using-the-file-system-task . If I wanted to test what is in a variable I would either add a Script Task into the package which displayed a message box, or simpler would be to write it to a temporary table using an Execute SQL Task.

Thanks Martina… Your post and respose to others were both helpfull. keep posting more.

How can I use the variable multiple times. I have set a variable managerID = 10 and used in the following statement with both manager and training_officer = managerID

insert into employee (Name, Manager, training_officer) values (‘Peter’, ?, ?)

Even if you are using the same Variable multiple times, you still need to enter them separately on the Parameter Mapping tab, once for each time they appear in the SQL Statement. This is the only way to identify which parameter is being used for which question mark.

This is really helpful except I do not see Variables or Log Events in the Other Windows submenu. I am running 2008. Is there an option that would turn that on?

Hi Dennis, When you select the View menu, have you first selected the Control Flow or created a package to which you would like to add the variables?

Many thanks Dear Martina for that useful and helpful document. I appriciate. best regards.

Very concise and to-the-point. Was exactly what I needed with no extra frills. Thanks.

How to create variable with numeric datatype in excute sql task ?

Use a numeric datatype such as Int32

Simple but helpful, thanks.

Thanks a lot this article is really helpful 🙂

Brilliant stuff, thank you

Thank you so much for providing this information clearly and thoroughly. It’s still relevant and useful after the original post 2 years ago. Have a good one!

guys…..could u please out and post how to call the stored procedures parameters in execute sql task send me the query…….for that

Hi Dinesh, It works in exactly the same was as other sql statements. Instead of the parameter for your stored procedure, use the question mark. An example of executing a stored procedure with one parameter, the SQL statement would look like this: EXEC spTest ?

Great explanation for what should be a simple task but is not

I am trying to insert variables into my table. It seems like I did exactly what you recommended but it gives me error message. Could you take a look?

I have 4 variables plus getdate(). all the variables are int 32. I filled the parameters as you recommended and fill the result set as you recommended, filled the general session as recommended.

here are my SQL statement:

insert into LOC_Summary_Table (Report_dt,LOC_A,LOC_B,LOC_C,ASSESS)

select getdate(),?,?,?,?

The error message is compile error.

could you help?

Many many thanks

Are the variables scoped to the package? What datatype did you use on the Parameter Mapping tab? The SQL statement looks fine.

Thanks, Martina, for an excellent explanation. I am using SQL 2012. In what way is the process easier? Could you please give me the steps to 1) create a variable (Tables) and populate it with a list of table names, 2) create a second variable (CurrentTable) that takes a value from the Tables variable and 3) use the value in CurrentTable to name an output (text) file? I will use a Foreach Loop container to query each of the tables in Tables.

Regards, Irene

1) Create an Object variable to hold the list of table names. Use an Execute SQL task to populate the variable: on the General tab set the Result Set to “Full Result Set”. On the Result tab map the Result Name “0” to the variable you created above. 2) Create a string variable Add a Foreach Loop. On the Collection tab, set the Enumberator to “Foreach ADO Enumerator”. Set the ADO object source variable to your Object variable created in step 1. On the Variable Mappings tab, map the string variable to Index 0. 3) In the properties of the Connection Manager for your output (text) file, select Expressions and set the Property “Name” to the string variable you created in step 2.

Hope that helps give you some direction. Martina

Hi Martina,

My case is similar but I had two execute sql tasks. I had a variable “output”(data type object) which gets populated by EST-1. The data is a table. I need to pass this table as a input parameter to a Stored procedure , which is called in the EST-2.

I am confused in setting the data type of variable in the parameter mapping tab of EST-2. Can you tell me what data type should we use for variable in parameter mapping with datatype Object.

Thanks – the MS Doc is very unclear on this.

Wow this is exactly what I was looking for. Thank you so much.

Fantastic tutorial! I am a first time developer. Explained exceptionally well.

My search found this article when I was searching for “initial”, but there really isn’t any content about initialization.

For example, if one leaves “Value” field un-populated, does that mean NULL, or empty string / zero, depending on type?

If I want to set the initial value to NULL, do I have to create an expression and use one of the NULL() expressions? Or can I put something into the “value” field to set NULL? I’m guessing that entering the letters n-u-l-l for a string would set the string “null”, not the indicator NULL.

Of course one can do experiments, but I think this is something a reference article should at least mention (don’t know if this one is ever updated).

Comments are closed.

web analytics

This browser is no longer supported.

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

Execute T-SQL Statement Task

  • 7 contributors

The Execute T-SQL Statement task runs Transact-SQL statements. For more information, see Transact-SQL Reference (Database Engine) and Integration Services (SSIS) Queries .

This task is similar to the Execute SQL task. However, the Execute T-SQL Statement task supports only the Transact-SQL version of the SQL language and you cannot use this task to run statements on servers that use other dialects of the SQL language. If you need to run parameterized queries, save the query results to variables, or use property expressions, you should use the Execute SQL task instead of the Execute T-SQL Statement task. For more information, see Execute SQL Task .

Configuration of the Execute T-SQL Task

You can set properties through SSIS Designer. This task is in the Maintenance Plan Tasks section of the Toolbox in SSIS Designer.

For more information about the properties that you can set in SSIS Designer, click the following topic:

  • Execute T-SQL Statement Task (Maintenance Plan)

For more information about how to set these properties in SSIS Designer, click the following topic:

  • Set the Properties of a Task or Container

Integration Services Tasks Control Flow MERGE in Integration Services Packages

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

Any way to see or view the results of an execute sql task ?

SSCertifiable

Points: 5025

More actions

October 24, 2013 at 2:39 pm

I want to see the results of my execute SQL query. Is there any simple way to do that ? I am looking for some kind of viewer or even file where the results can be written to.

Koen Verbeeck

Points: 259085

October 24, 2013 at 3:24 pm

You cannot see this in SSIS itself.

You'll need to go to the database and check if the query was executed.

You could also use SQL Server Profiler to verify if the query has been sent to the database correctly.

Or you could implement some auditing in the query itself.

The Execute SQL Task will only tell you if the query has been successfully executed or not and how long it took.

Need an answer? No, you need a question My blog at https://sqlkover.com. MCSE Business Intelligence - Microsoft Data Platform MVP

SSCommitted

Points: 1828

October 28, 2013 at 4:34 am

Write the results of your SQL query out to a variable, try single row first , if you have a full result set then it gets a little more tricky.

1 . Create a string variable eg: @resultsfromquery.

2. In you SQL Task container, choose ResultSet Single row

3. Choose Result Set from the menu on the side GENERAL, ParameterMapping, Result Set, Expressions.

4. Set the Result Name to 0 and the VariableName to your variable eg: User::resultsfromquery

5. Add a breakpoint to your SQLTask, you can use Break when the container receives the OnPostExecute

6. Start Debug (F5)

7. When debug starts you will have the extra option of adding a WATCH window.

8. When the watch window appears drag your variable onto the watch window.

When your package hits the breakpoint, you will then see your results appear in your variable.

If you are doing a full result set you will need to set up a for each loop container 🙂

Hope this helps

October 28, 2013 at 7:00 am

pnr8uk (10/28/2013) Write the results of your SQL query out to a variable, try single row first , if you have a full result set then it gets a little more tricky. 1 . Create a string variable eg: @resultsfromquery. 2. In you SQL Task container, choose ResultSet Single row 3. Choose Result Set from the menu on the side GENERAL, ParameterMapping, Result Set, Expressions. 4. Set the Result Name to 0 and the VariableName to your variable eg: User::resultsfromquery 5. Add a breakpoint to your SQLTask, you can use Break when the container receives the OnPostExecute 6. Start Debug (F5) 7. When debug starts you will have the extra option of adding a WATCH window. 8. When the watch window appears drag your variable onto the watch window. When your package hits the breakpoint, you will then see your results appear in your variable. If you are doing a full result set you will need to set up a for each loop container 🙂 Hope this helps Paul

That only works with SELECT statements of course, which are kind of pointless in an Execute SQL Task unless you assign the results to a variable 🙂

October 28, 2013 at 8:31 am

Well yes 🙂 but the poster seemed to want that? :-S

October 28, 2013 at 2:07 pm

pnr8uk (10/28/2013) Well yes 🙂 but the poster seemed to want that? :-S

Not sure, he doesn't mention the type of query 🙂

If it's an update statement, you see nothing in SSIS.

October 28, 2013 at 2:34 pm

Dude... what are you trying to do here? I'm just trying to help the guy out, there's no mention of an update in his post or I wouldn't have put down my solution. I'm not trying to say you were wrong or anything. Aren't these forums about trying to provide a solution, not undermine any poster or helper?

I want to see the results of my execute SQL query . Is there any simple way to do that ? I am looking for some kind of viewer or even file where the results can be written to.

October 29, 2013 at 1:47 am

Dude, relax. I was just pointing out that there's not enough information in the original post to know what kind of query it is.

If it is an update/delete, the variable is useless, if it's a select, it's not.

Points: 11520

October 30, 2013 at 8:55 am

This is one of two reasons I don't use the execute SQL task. If I get called at 1 AM because the SQL failed I want to know where it failed and what error I got. I don't want to have to rerun the SQL in SSMS to find the error. And I may have to determine if I can rerun the earlier steps in the SQL, may need to restore or recreate certain files. Not things you want to have to think about at 1 AM.

The second reason is I have to update the SSIS package anytime I make a SQL change. I know this can be a good thing if you keep versions of each update. But luckily we haven't ran into any issues, yet.

We use an 'Execute process task' to call a 'bat' file that executes the SQL. I can set this to put the log from the SQL out to a file that I can then review.

So until someone can tell me how I can see this same type of 'log' information for an execution of a SQL job, I will not be using the execute SQL task.

------------------------------------------------------------- we travel not to escape life but for life not to escape us Don't fear failure, fear regret.

Luis Cazares

Points: 183698

October 30, 2013 at 9:30 am

below86 (10/30/2013) So until someone can tell me how I can see this same type of 'log' information for an execution of a SQL job, I will not be using the execute SQL task.

If you just want to know where and why the task failed, you could use SQL Server error handling to get an even better error log. To avoid changes on the SSIS package, you could simply use Stored Procedures instead of the full query.

I'm not sure that your way is the way to go, but if it works for you, then I can't suggest you to stop using it, just wanted to give some ideas.;-)

Luis C. General Disclaimer: Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

October 30, 2013 at 9:47 am

Luis Cazares (10/30/2013) below86 (10/30/2013) So until someone can tell me how I can see this same type of 'log' information for an execution of a SQL job, I will not be using the execute SQL task.

I know how to tell where my SSIS package failed it's just finding out where in the SQL it failed and what error it gave. And from what I've done, granted not a lot, I don't see that I can get that detail from the execute SQL task. If you have a 1,000 line SQL file you are executing with say 20 individual SQL 'steps' within it. I need to know that SQL 'step' 10 failed for 'duplicate key' or whatever the error is. My log file will give me that and I can also see how many rows were processed by each step. I haven't done much with stored procedure so I am not aware of any 'log' that is created when it is executed. Where/and how would I find that information? Does the stored procedure need to be set up in any special way for this to happen? Or is is a setting on the database or server?

October 30, 2013 at 9:57 am

I wasn't saying that stored procedures save a log automatically, but are a simple way to manage your T-SQL code without touching the SSIS packages.

For the error logging, take a look at TRY...CATCH documentation . It might seem to be more work to do, but it's more flexible than normal logging.

October 30, 2013 at 10:18 am

Luis Cazares (10/30/2013) I wasn't saying that stored procedures save a log automatically, but are a simple way to manage your T-SQL code without touching the SSIS packages. For the error logging, take a look at TRY...CATCH documentation . It might seem to be more work to do, but it's more flexible than normal logging.

Sorry I miss understood your prior post. I'll keep the TRY..Catch in mind, but with thousands of individual SQL jobs out there it would be a long time to migrate to that. I was just hoping there was something out there that I wasn't aware of. Thanks.

October 30, 2013 at 10:42 am

I am doing only select statements.

Response "to below86" - regarding execute SQL indirectly by executing a bat file which executes SQL - I am trying something similar now. Need your help to figure it out.

I made posts on it here and haven't got much help so far -

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dbcdd59d-469d-49db-a33e-f554d5f915c3/need-help-to-make-sqlcmd-run-as-per-requirements?forum=sqltools#cbff0250-8809-4708-9eb4-f0622360f887

http://stackoverflow.com/questions/19674377/need-to-understand-output-of-sqlcmd-exe

October 30, 2013 at 10:51 am

Hi mate, if you are only doing select statments then my solution would work...

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

mava.jpg

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

sql execute task result set

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

sql execute task result set

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

sql execute task result set

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

sql execute task result set

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)

sql execute task result set

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.

sql execute task result set

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

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

sql execute task result set

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

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

sql execute task result set

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.

sql execute task result set

Step 5: Run the package

sql execute task result set

Execute SQL Task, Result set: Full result set

I want to place this result set into a variable.

sql execute task result set

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.

sql execute task result set

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

sql execute task result set

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

sql execute task result set

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

sql execute task result set

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

sql execute task result set

Step 6: Run the package

sql execute task result set

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.

sql execute task result set

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

sql execute task result set

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

sql execute task result set

Check Execute SQL Task for the SQLStatement

sql execute task result set

Recent Posts

SSIS Append data

Data Warehouse Dimension Loading Using Lookup and Conditional Split Transformations.

SSIS Expressions

Start with the FREE community plan and get your lifetime access to 20+ courses. Get Instant Access Now!

Private Training

Customized training to master new skills and grow your business.

On-Demand Learning

Beginner to advanced classes taught by Microsoft MVPs and Authors.

In-depth boot camps take you from a novice to mastery in less than a week.

Season Learning Pass

Get access to our very best training offerings for successful up-skilling.

Stream Pro Plus

Combine On-Demand Learning platform with face-to-face Virtual Mentoring.

Certification Training

Prepare and ace your next certification with CertXP.

  • Virtual Mentor
  • Private Classes
  • Admin in a Day

All Courses

View all of the courses available on our on-demand learning platform.

By Learning Path

Explore our courses based on role-specific learning path.

Find all of the courses for the product you want to master.

Instructors

  • Brian Knight
  • Devin Knight
  • Matt Peterson
  • Jonathon Silva
  • Austin Libal
  • Allison Gonzalez
  • Angelica Choo Quan
  • View all instructors

In-depth bootcamps take you from a novice to mastery in less than a week.

Learn With The Nerds

A free virtual series covering an array of data analytics and cloud topics.

In A Day Workshops

Pragmatic Works is proud to manage and update content for these Microsoft workshops.

How to Set Up Microsoft Bookings Quickly

Cheat Sheets

Quick references for when you need a little guidance.

Nerd Guides

Summaries developed in conjunction with our Learn with the Nerds sessions.

Digital goodies - code samples, student files, and other must have files.

Stay up-to-date on all things Power BI, Power Apps, Microsoft 365 and Azure.

Community Discord Server

Start here for technology questions to get answers from the community.

Affiliate Program

Earn money by driving sales through the Pragmatic Works' Training Affiliate Program.

Reseller Partner

It's time to address your client's training needs.

Learn how to get into IT with free training and mentorship.

Management Team

Discover the faces behind our success: Meet our dedicated team

How can we help? Connect with Our Team Today!

Find all the information you’re looking for. We’re happy to help.

Looping Through a Result Set with the ForEach Loop

Shawn Harrison

In today's blog post, I am going to show you another use for the ForEach Loop container in SSIS. Most of the time, this container is used for looping through a group of files, however, there are many different uses for it.

Let's say you have a need to take some rows from a source and aggregate some values. Normally, something like that is done in the data flow, but it can also be done in the control flow.  First, create 5 variables. Pay close attention to the data types.

In the script editor, use the following script.

 

And there you have it. Again, this is something that is normally done in the data flow, but in case you ever encounter a need to do it at the control flow level, you know that it can be done.

Sign-up now and get instant access

Shawn Harrison

ABOUT THE AUTHOR

Shawn Harrison

Business Intelligence speaker and developer.

Leave a comment

On-demand learning, most recent.

  • How to Set Up Microsoft Bookings Quickly
  • Dynamic Subscriptions in Power BI
  • Enhancing PowerPoint Presentations with Power BI
  • Automating Notifications with Power Automate: A Pragmatic Guide

private training

Hackathons, enterprise training, virtual monitoring

Related articles.

Transitioning from SSIS to Azure Data Factory

Transitioning from SSIS to Azure Data Factory

SSIS in the Cloud Overview

SSIS in the Cloud Overview

We're Excited to Announce Our New Course - SSIS in the Cloud

We're Excited to Announce Our New Course - SSIS in the Cloud

Join our blog

Join other Azure, Power Platform and SQL Server pros by subscribing to our blog.

c7cd1fb9-0efa-499e-8225-f2f05232b5c6-removebg 1

Execute SQL Task in SSIS Single Rowset

All about SQLServer

writings on my experiences and learnings in SQL Server...

SSIS - Execute SQL Task - Result Set and Parameter Mapping

sql execute task result set

Post a Comment

  • SQL Server training
  • Write for us!

Hadi Fadlallah

SSIS: Execute T-SQL Statement Task vs Execute SQL Task

T-SQL (Transact-SQL) is a set of SQL language programming extensions developed by Sybase and Microsoft. These extensions are adopted in Microsoft SQL Server and it provides a powerful set of functions to execute analytics and administrative commands; also it is very helpful during data wrangling.

When it comes to SSIS, executing commands over SQL Server is mainly done using “Execute SQL Task” which is one of the most popular SSIS task, it is also used to run SQL commands over different engines such as SQLite, MySQL, Microsoft Access and Excel and any source that has a supported connection manager. We have previously published two articles in this series where we illustrated how to use and configure this task:

  • Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types
  • Execute SQL Tasks in SSIS: Output Parameters vs Result Sets

While looking in the SSIS toolbox, you will see that there is another similar task called “Execute T-SQL Statement Task”. In this article, we will give an overview of this Task, and we will make a small comparison with the Execute SQL Task which is more popular.

Execute T-SQL Statement Task

Referring to the SSIS toolbox, this task is only used to “run Transact-SQL statements”.

Execute T-SQL Statement task description from SSIS toolbox

After adding this task to your control flow and opening the task editor, you will see that it is very simple. It contains the following components:

Execute T-SQL statement task editor form

This task uses only ADO.NET Connections, which requires that .NET Framework is installed, you can simply select an existing connection manager or you can add a new one by clicking on the “New…” button.

When attempting to add a new connection, this task opens a simplified window that only contains the main connection information:

Add new connection dialog

  • Connection name : which is the connection manager name
  • Server name : where you should enter the SQL Server instance that you need to connect with
  • Authentication configuration : where you should select whether you need to use Windows or SQL authentication (user and password should be provided)

From the new connection form, you can see that it only accepts SQL Server connections (not all ADO.NET connections) since it is mentioned that the user should “specify the following to connect to SQL Server data”.

After creating a new connection, you will see that a new connection manager is created. If you open this connection manager, you will see that it is of type ADO.NET and it uses “.Net Providers/SqlClient Data Provider”. Now, you can check other connection settings when you click on the “All” tab.

Advanced connection properties in the connection manager

Execution time out

The execution time out refers to the time (in seconds) to wait for statement completion before timing out (terminating task). If the value is set to zero (0), then it will be infinite. Note that this property differs from the connection time out property that can be configured from the connection manager since the second refers to the time needed to establish the connection.

T-SQL statement

In this field, you should write the SQL statement you need to execute on the connected server. You can check the command that will be sent to the SQL Server instance by clicking on the “View T-SQL” button.

Showing how "View T-SQL" button acts.

Many times I was asked about the need for this button since it shows the same command written in the main form. The answer is that this button is found within all the tasks that are supported in the SQL Server maintenance plans such as “Back up database task”, “History cleanup task” and others where it will show the statement generated based on the configuration you have made within the task.

Expressions and hidden properties

If you close the editor and click on the task within the control flow then press F4 to show the properties tab, you will see that there are many properties that are not shown within the task editor such as CodePage, ResultSetType, SqlStatementSource, SqlStatmentSourceType.

You may note that many of these properties are related to the Execute SQL Task, which means that both tasks are built using the same template. But these properties may be invalid, such as ResultSetType, since you will not be able to select the variable where the result set is stored.

Task's hidden properties

On the other hand, you can use SqlStatmentSourceType and SqlStatementSource properties to force the task to read the SQL statement from a variable or a file connection. Note that you should write the variable name or file connection name in the statement field (or SqlStatementSource property).

Also, it is worth mentioning that you can use Expressions similar to other tasks in order to evaluate these task properties using SSIS expressions. But in the official documentation, they recommended using the Execute SQL Task when you need to use expressions.

Comparison with Execute SQL Task

In general, if we need to compare the Execute T-SQL Statement Task with Execute SQL Task, we can say that the latter is more generic since it gives the user the ability to execute statement over SQL Server and other supported sources while the former is only dedicated to SQL Server (ADO.net) connections. In addition, there are other points worth to mention:

SQL agent maintenance plans

If you are building SSIS packages to be used as SQL Agent maintenance plans, you cannot use the Execute SQL Task since it is not supported.

Result Sets

If you need to generate result sets from your statement and to store them within SSIS variables, you should use Execute SQL Task since this feature is not supported within the other task.

Parameterized queries

If you need to use parameterized queries, then you should use Execute SQL Task.

.NET framework installation

The Execute T-SQL Statement task uses ADO.NET connections only, which means that it requires that the .NET framework should be installed on the machine.

Online resources

You may note that Execute SQL Task is more popular and it is widely used by developers; you will find hundreds of related topics, tutorials online. While you may not find ten articles related to the Execute T-SQL Statement.

Statement Parse Time

Since the Execute SQL Task supports multiple SQL connection types and languages, it will require additional time to parse and validate the statement and other parameters. But the amount of time is mostly negligible.

Project connection issue

In his personal blog , James Serra has mentioned the following issue related to project connection manager:

“One problem I found with the Execute T-SQL Statement task: When you create an ADO.NET project connection in the Connection Manager, it will automatically create a package connection that is linked to the project connection and will have a “(project)” prefix. In the Qualifier property for the package connection, you see “System.Data.SqlClient.SqlConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”. However, when using an Execute T-SQL Statement task you won’t see that package connection listed in the “Connection” drop-down on the task’s properties. To see it, you must change the Qualifier property in that package connection to “SQL”. You do not need to do this when using the Execute SQL task.”

After showing the main difference between these two tasks, we can see that it is more preferable to use Execute SQL Task since it guarantees flexibility and based on my personal experience, the only place to use the Execute T-SQL Statement task is within SQL agent maintenance plans.

Table of contents

SSIS: Execute T-SQL Statement Task vs Execute SQL Task
  • Recent Posts

Hadi Fadlallah

  • 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
  • Execute SQL Task in SSIS: Output Parameters vs Result Sets
  • SSIS XML Source vs. XML task
  • SSIS Expression Tasks vs Evaluating variables as expressions

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

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.

SQL Statement executing

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.

SQL statement in the Parameter

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:

mapping collection

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.

MindMajix YouTube Channel

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

Result Name property

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.

Multi-Row Results

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. 

Foreach Loop Container

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:

Executing a Stored Procedure

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.

parameter listing

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.

Parameter Mapping tab

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:

SSIS variables

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.

parameter setup

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:

   
   
   
   
   
NameDates
SSIS TrainingJul 13 to Jul 28
SSIS TrainingJul 16 to Jul 31
SSIS TrainingJul 20 to Aug 04
SSIS TrainingJul 23 to Aug 07

Remy Sharp

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.

Sisense Training || "Reco slider img"

  • 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
  • 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

Democrats are talking about replacing Joe Biden. That wouldn't be so easy.

President Joe Biden's performance in the first debate Thursday has sparked a new round of criticism from Democrats , as well as public and private musing about whether he should remain at the top of the ticket.

In the modern era, a national party has never tried to adversarially replace its nominee, in part, because knows it would most likely fail. The issue came before both parties in 2016, but neither took action.

Party rules make it almost impossible to replace nominees without their consent, let alone smoothly replace them with someone else. And doing so would amount to party insiders’ overturning the results of primaries when Democratic voters overwhelmingly to nominate Biden. He won almost 99% of all delegates.

And at the moment, there’s no known, serious effort to push him off the top of the ticket.

Still, the Democratic National Committee's charter does make some provisions in case the party’s nominee is incapacitated or opts to step aside, and an anti-Biden coup at the convention is theoretically possible, if highly unlikely. So how would it work?

What happens if Biden drops out before the convention?

The only plausible scenario for Democrats to get a new nominee would be for Biden to decide to withdraw, which he has sworn off repeatedly during other bumpy stretches of his campaign.  

He could do so while serving out the rest of his term in the White House, as Lyndon Johnson did in 1968. 

If Biden were to drop out before he is scheduled to be formally nominated in August, it would create a free-for-all among Democrats, because there’s no mechanism for him or anyone else to anoint a chosen successor.

It takes a majority of the roughly 4,000 pledged delegates to win the party’s nomination. Biden’s won 3,900 of them. Under recent reforms, the party’s more than 700 superdelegates — Democratic lawmakers and dignitaries — are allowed to vote only if no one wins a majority of pledged delegates on the first ballot, so their votes could be crucial in a contested convention. 

Because Biden's opponents all won effectively no delegates throughout the Democratic nominating process, there'd be a virtual clean slate heading into the convention, and the decision would most likely come down to the convention delegates who were initially pledged to Biden.

Biden would have some influence over his pledged delegates, but ultimately, they can vote as they please, so candidates would most likely campaign aggressively to win over each individual delegate.

However, there's a potentially important wrinkle: Democrats plan to formally nominate Biden virtually ahead of the late-August convention to sidestep any potential concerns about ballo t access in Ohio, where a technical quirk has complicated things

Democrats decided to plan a virtual nomination for Biden after Ohio Republicans balked at passing pro forma legislation that would allow Biden to be on the ballot, even though the convention falls after a state deadline. But while Republicans passed a law to shift the deadline, Democrats decided to move forward with a virtual nomination nonetheless.

Could Democrats replace Biden against his will?

There’s no evidence the party would entertain a change without Biden’s consent. But even if it did, there’s no mechanism for it to replace a candidate before the convention, and certainly no way for it to anoint a chosen successor.

If large swaths of the Democratic Party lost faith in Biden, delegates to the national convention could theoretically defect en masse. Of course, they were chosen to be delegates because of their loyalty to Biden and have pledged to support him at the convention.

But, unlike many Republican delegates, Democratic delegates aren’t technically bound to their candidate. DNC rules allow delegates to “in all good conscience reflect the sentiments of those who elected them,” providing some wiggle room.

The party’s charter does include provisions to replace the nominee in the event of a vacancy. The measure is intended to be used in case of death, resignation or incapacitation, not to replace someone who has no desire to step down.

That was the measure that Donna Brazile, then the interim DNC chair, considered invoking after Hillary Clinton collapsed two months before the 2016 election, she wrote in her memoir .

In her memoir, released a year later, Brazile wrote that she was worried “not just about Hillary’s health but about her anemic campaign ... so lacking in the spirit of fight.” 

“Perhaps changing the candidate was a chance to win this thing, to change the playing field in a way that would send Donald Trump scrambling and unable to catch up,” she wrote, adding that aides to other would-be candidates contacted her, including then-Vice President Biden’s chief of staff.

But after less than 24 hours of consideration, Brazile realized the idea was untenable without Clinton’s cooperation and likely to only divide her party further. “I could not make good on my threat to replace her," she wrote.

Current DNC Chair Jaime Harrison is a longtime Biden ally who serves, essentially, at the pleasure of the president. And the national party has certainly given no indication it’s anything but fully behind his re-election.  

What happens if Biden withdraws after the convention?

To fill a vacancy on the national ticket, the chair can call a “special meeting” of the full DNC, which includes about 500 members. On paper, at least, all it takes is a majority vote of those present to pick new presidential and vice presidential nominees. But that process would most likely be anything but smooth and be filled with behind-the-scenes jockeying and public pressure campaigns. 

If a vacancy were to occur close to the November election, however, it could raise constitutional, legal and practical concerns. Among other issues, ballots have to be printed well in advance of the election, and it might not be possible to change them in time.

Would Kamala Harris replace Biden?

If Biden were to relinquish the presidency, Vice President Kamala Harris would automatically become president — but not the Democratic Party’s nominee. Nor would she necessarily be the nominee if Biden withdrew from his re-election bid while he remained in the White House.

She might be politically favored, but party rules give the vice president no major mechanical benefit over other candidates. 

Biden’s delegates wouldn’t automatically transfer to Harris, and the convention holds separate votes on nominees for president and vice president. So she would still need to win a majority of delegates at the convention. 

If the top of the ticket were vacated after the convention, she would still need to win a majority of votes at the special meeting of the DNC.

That is all, at least, under current party rules. But a vacancy at the top of the ticket is the kind of dramatic moment that might lead party leaders to revisit them in the name of easing the transition. Harris has some close allies in key places at the DNC, including a co-chair of the party’s Rules and Bylaws Committee. But nothing would be likely to happen without a fight.

sql execute task result set

Ben Kamisar is a national political reporter for NBC News.

sql execute task result set

Alex Seitz-Wald is a senior politics reporter for NBC News.

  • Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
  • Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
  • OverflowAI GenAI features for Teams
  • OverflowAPI Train & fine-tune LLMs
  • Labs The future of collective knowledge sharing
  • About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

How to assign an exec result to a sql variable?

How do you assign the result of an exec call to a variable in SQL? I have a stored proc called up_GetBusinessDay , which returns a single date.

Can you do something like this:

  • stored-procedures

Code Maverick's user avatar

7 Answers 7

I always use the return value to pass back error status. If you need to pass back one value I'd use an output parameter.

Here's a sample stored procedure, with an OUTPUT parameter:

Here's an example of calling the stored procedure, with an OUTPUT parameter:

CarenRose's user avatar

  • 12 By using an OUTPUT parameter, you can return back any data type, the RETURN value from a stored procedure can only be an integer. –  KM. Commented Feb 11, 2010 at 18:14
  • 2 Just a side note, OUTPUT parameters that are declared with a value don't need to be passed in. This means that if you are altering an existing SP you can do it safely without risking breaking anything. eg ,@Param3 datetime = '1900-01-01' OUTPUT. –  Morvael Commented Oct 25, 2016 at 10:07

This will work if you wish to simply return an integer:

Matthew Lock's user avatar

  • 16 -1 This will only return an integer. The OP wants to return a date. The accepted answer by @KM. is the correct answer, as it uses OUTPUT instead of RETURN. –  Code Maverick Commented May 7, 2014 at 0:41
  • 6 Actually this works. The example as to how to get an integer that is returned, you can do the same for all other kinds (didn't check if table possible, but I think yes.) I just tried it for nvarchar(50). –  Lzh Commented May 18, 2014 at 4:39
  • 3 @Mzn "you can do the same for all other kinds" , certainly doesn't work with UNIQUEIDENTIFIER . –  James Commented Jun 9, 2016 at 9:20
  • 3 @James Why? What's different about the uniqueidentifier datatype? Stored procedures cannot return uniqueidentifiers? –  Lzh Commented Jun 9, 2016 at 9:50
  • 4 @Mzn UNIQUEIDENTIFIER was just an example, RETURN is designed to only work with integer values, see the docs . The recommended way of getting other data from an SP is to either return a result set or use OUTPUT –  James Commented Jun 9, 2016 at 12:21

AZ Chad's user avatar

  • 7 actually the only working way decribed here apart from changing the stored proc's signature –  Michael Sander Commented Aug 31, 2016 at 7:43
  • 3 Used this as well for a date, when the underlying Sproc doesn't have an output parameter either. (Underlying sproc had another Exec inside it from dynamic SQL) –  Jeff Beagley Commented Sep 27, 2016 at 22:16
  • 4 @MichaelSander Totally right, all other solutions are not correctly answering the OPs question. The only way is a temporary table which holds the results. –  SQL Police Commented Oct 5, 2016 at 8:17
  • 5 Only way that works here without requiring edits to the proc, which I can't do in my case. +1 –  DLeh Commented Feb 16, 2017 at 21:33
  • 1 You can also use a table variable in place of a temp table. –  error Commented Mar 14, 2019 at 14:58

From the documentation (assuming that you use SQL-Server):

So yes, it should work that way.

Peter Lang's user avatar

  • 9 in the OP's example they want to return a date, Stored procedures can only RETURN an integer value to a calling procedure or an application. –  KM. Commented Feb 11, 2010 at 18:13
  • This is incorrect. That documentation is talking about a scalar function. The OP is asking about a stored procedure with its resultset. @KM. note even the source documentation is assigning the returned value to nvarchar(15), not int. –  youcantryreachingme Commented Sep 15, 2020 at 1:00

I had the same question. While there are good answers here I decided to create a table-valued function. With a table (or scalar) valued function you don't have to change your stored proc. I simply did a select from the table-valued function. Note that the parameter (MyParameter is optional).

To assign to your variable you simply can do something like:

You can also use a scalar valued function:

Then you can simply do

CodeCaptain's user avatar

Here is solution for dynamic queries.

For example if you have more tables with different suffix:

marc_s's user avatar

  • This worked perfectly for my situation - thanks –  AVH Commented Apr 21, 2021 at 21:20

You can use a Table Variable for that

https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-variables/

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged sql sql-server t-sql stored-procedures or ask your own question .

  • Featured on Meta
  • We spent a sprint addressing your requests — here’s how it went
  • Upcoming initiatives on Stack Overflow and across the Stack Exchange network...
  • What makes a homepage useful for logged-in users

Hot Network Questions

  • Is taping behind tile necessary when drywall seams are not easily accessible?
  • Submitting 2 manuscripts explaining the same scientific fact but by two different methods
  • What is the probability that a penalty shoot-out in soccer will end with a difference of 1, 2 or 3 goals?
  • If someone clearly believes that he has witnessed something extraordinary very clearly, why is it more reasonable to believe that they hallucinated?
  • Why bother with planetary battlefields?
  • Is it an option for the ls utility specified in POSIX.1-2017?
  • Are you radical enough to solve this SURDOKU?
  • 80's/90's anime about a mecha made out of bones
  • Using grout that had hardened in the bag
  • Did any other European leader praise China for its peace initiatives since the outbreak of the Ukraine war?
  • Coincidence between coefficients of tanh(tan(x/2)) and Chow ring computations?
  • What scientifically plausible apocalypse scenario, if any, meets my criteria?
  • The rear wheel from my new Road Bike vibrates strongly
  • Are the North Star and the moon ever visible in the night sky at the same time?
  • Why are metal ores dredged from coastal lagoons rather than being extracted directly from the mother lode?
  • How can I search File Explorer for files only (i.e. exclude folders) in Windows 10?
  • Meaning of 春天一到,花園裡綠意盎然
  • Can trills have 3 notes instead of two?
  • How are GameManagers created in Unity?
  • What enforcement exists for medical informed consent?
  • What does masu + そな mean?
  • How should I deal with curves in new deck boards during installation?
  • Identify this bouncing ball screensaver in DOS
  • When, if ever, is bribery legal?

sql execute task result set

IMAGES

  1. SSIS Basics: Using the Execute SQL Task to Generate Result Sets

    sql execute task result set

  2. Execute SQL Task in SSIS: Output Parameters vs Result Sets

    sql execute task result set

  3. 117 Execute SQL Task in SSIS result set

    sql execute task result set

  4. SQL Server EXECUTE Statement with RESULT SET

    sql execute task result set

  5. SSIS Basics: Using the Execute SQL Task to Generate Result Sets

    sql execute task result set

  6. SSIS "Execute SQL Task"

    sql execute task result set

VIDEO

  1. AND Operator

  2. Data-Engineering/ETL/Database Views

  3. Execute SQL Task with Parameters

  4. SSIS||How to Update table with a Variable using Execute SQL Task?

  5. Step 3

  6. 49 SSIS Controlling Package Flow Using Execute SQL Task

COMMENTS

  1. Execute SQL Task in SSIS: Output Parameters vs Result Sets

    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 ...

  2. SSIS Basics: Using the Execute SQL Task to Generate Result Sets

    Then, in the Variable Name column, select the User:: E mployeeList variable. Once this is complete, click OK. Your Execute SQL task will now return a full result set and save it to the E mployeeList variable. (You should execute the task to make sure it runs.) You can then use that variable in other SSIS components.

  3. Execute SQL Task

    TypeConversionMode When you set this property to Allowed, the Execute SQL Task will attempt to convert output parameter and query results to the data type of the variable the results are assigned to.This applies to the Single row result set type.. ResultSet Specify the result type expected by the SQL statement being run. Choose among Single row, Full result set, XML, or None.

  4. sql server

    Return single row single value result set of Execute SQL task as an input to another execute SQL task in SSIS. 2. Store result in Execute Sql task. 1. SSIS execute sql task returncode from SP. Hot Network Questions When do you know things are actually going poorly in graduate school?

  5. Execute SQL Task in SSIS

    Run stored procedures. Save the result set in a variable to use that data in other tasks. Execute SQL Task in SSIS Properties. This article will show you the steps in configuring the Execute SQL Task in SSIS with an example. First, Drag the Execute SQL Task in the Toolbar and drop it into the Control Flow region.

  6. SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers

    To configure the variable, select the Result Set page in the Execute SQL Task and click the Add button. Replace the text "NewResultName" in the Result Name column with the ordinal 0. In the ...

  7. The SSIS Object Variable and Multiple Result Sets

    To continue the test, I've added an execute SQL task to the control flow, using the stored procedure above as the query. I set the ResultSet to Full Result Set. Specifying a ResultSet value of Full Result Set requires the use of an object-typed variable to store the results.

  8. How To Set and Use Variables in SSIS Execute SQL Task

    Use an Execute SQL task to populate the variable: on the General tab set the Result Set to "Full Result Set". On the Result tab map the Result Name "0" to the variable you created above. 2) Create a string variable Add a Foreach Loop. On the Collection tab, set the Enumberator to "Foreach ADO Enumerator".

  9. Execute T-SQL Statement Task

    If you need to run parameterized queries, save the query results to variables, or use property expressions, you should use the Execute SQL task instead of the Execute T-SQL Statement task. For more information, see Execute SQL Task. Configuration of the Execute T-SQL Task. You can set properties through SSIS Designer. This task is in the ...

  10. Any way to see or view the results of an execute sql task

    In you SQL Task container, choose ResultSet Single row. 3. Choose Result Set from the menu on the side GENERAL, ParameterMapping, Result Set, Expressions. 4. Set the Result Name to 0 and the ...

  11. Execute SQL Task in SSIS

    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.

  12. Looping Through a Result Set with the ForEach Loop

    Click the 'Result Set' option to the left. Set the Result Name to 0 and for the Variable Name, select objProductList. This variable will hold the results returned by the query. Click OK to close the editor. Next, add a ForEach Loop container and connect the Execute SQL task to it. Open the editor and click Collection.

  13. Execute SQL Task in SSIS Full Row Set

    Next, go to the Result Set tab and click the Add button to assign the variables for the return set. As we said before, the select statement will return a complete table. So, we are assigning object variable (ResultSet variable) as the result variable. Until now, we successfully configure the execute SQL task. But, we intend to save the result ...

  14. Execute SQL Task in SSIS Single Rowset

    Next, go to the Result Set tab in Execute SQL Task Editor and click the Add button to assign the variables for the return set. As you can see from the below screenshot, we are assigning the previously created variables to all the columns that are returned by the statement. We completed configuring SSIS Execute SQL Task Single Rowset.

  15. SSIS

    In this post,let us see an example for capturing query result (single valued) into SSIS variable and insert the captured data in the variable into another table. First create a variable in the scope of package with Data Type as INT32. Drag & drop two Execute SQL Task on the Control Flow tab. Follow as shown in below screen shots : Posted by ...

  16. SSIS: Execute T-SQL Statement Task vs Execute SQL Task

    SSIS: Execute T-SQL Statement Task vs Execute SQL Task. March 4, 2020 by Hadi Fadlallah. T-SQL (Transact-SQL) is a set of SQL language programming extensions developed by Sybase and Microsoft. These extensions are adopted in Microsoft SQL Server and it provides a powerful set of functions to execute analytics and administrative commands; also ...

  17. How To Execute Stored Procedure In SSIS & Execute SQL Task

    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. ... Now the parameters can simply be mapped in the Execute SQL Task Result ...

  18. sql server

    Below are the settings of Execute SQL Task. In General tab, Resultset project is set to single row; In Result Set tab, Result Name is set to 0 (I also tried by setting it to csvids which is the alias column name in the select list) and Variable Name is set to User::sCSVCompanyIds; I have no clue why its not working.

  19. Democrats are talking about replacing Joe Biden. That wouldn't be so easy

    President Joe Biden's performance in the first debate Thursday has sparked a new round of criticism from Democrats, as well as public and private musing about whether he should remain at the top ...

  20. How to assign an exec result to a sql variable?

    How do you assign the result of an exec call to a variable in SQL? I have a stored proc called up_GetBusinessDay, which returns a single date. Can you do something like this: exec @ ... How to set a SQL variable with either EXEC or sp_executesql. 2. T-SQL Stored procedure, declaring variables within the exec ...