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

In SSIS there are two tasks than can be used to execute SQL statements: Execute T-SQL Statement and Execute SQL.  What is the difference between the two?

The Execute T-SQL Statement task tasks less memory, parse time, and CPU time than the Execute SQL task, but is not as flexible.  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.  Also, 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.  In addition, the Execute SQL task supports many connection types but the Execute T-SQL Statement task supports only ADO.NET.  So in the end, if you want a bit more speed and don’t need the additional flexibility, use the Execute T-SQL Statement task over the Execute SQL task.

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

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQLServerPedia Syndication, SSIS. Bookmark the permalink.

7 Responses to SSIS: Execute SQL task vs Execute T-SQL Statement task

  1. mahesh says:

    I love your blog. I am trying Execute ssis package(32 bit) using t-sql or sql job on 64 bit.i am getting error on that.could you help me out in this…

  2. Koen Verbeeck says:

    You forgot to mention that the Execute TSQL Task has a horrible user interface :)

  3. Chintak Chhapia says:

    Yesterday, I had started with SSIS 2012, was thinking what’s the difference between these two tasks.. Thanks for bringing this post.

  4. mahesh says:

    The error is
    ” Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 8:43:57 AM Error: 2012-11-09 08:43:57.89 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B “Key not valid for use in specified state.”. You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2012-11-09 08:43:57.90 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B “Key not valid for use in specified state.”. You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2012-11-09 08:44:00.01 Code: 0xC0202009 Source: iNovahTestQARefresh Connection manager “SourceConnectionOLEDB” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0″ Hresult: 0x80040E4D Description: “Login failed for user ‘saqa’.”. End Error Error: 2012-11-09 08:44:00.02 Code: 0xC020801C Source: Data Flow Task 1 Source – Account [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “SourceConnectionOLEDB” failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2012-11-09 08:44:00.02 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: component “Source – Account” (1) failed validation and returned error code 0xC020801C. End Error Error: 2012-11-09 08:44:00.02 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2012-11-09 08:44:00.03 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:43:57 AM Finished: 8:44:00 AM Elapsed: 2.687 seconds. The package execution failed. The step failed.

    I manually i change the user name and pwd in sql server job pro…

  5. Sergei says:

    Just ran into this drop-down problem and appreciate your mentioning the “SQL” qualifier solution. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>