Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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.

Comments

Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...