Learn to Code SSIS: The Execute SQL Task

Note: This article was first published at SQL Authority.

With “SQL Server” included in the name of SQL Server Integration Services (SSIS), it is easy for people to assume SSIS is simply a database tool or accessory. Let there be no mistake, SSIS is a software development platform. To give you a better understanding of how to use SSIS as a development platform, I’ll write a series of blogs that step through how to use Control Flow tasks. This first article will cover the Execute SQL Task.

When developing solutions with SSIS, I use a handful of Control Flow tasks:

Execute SQL Task
Data Flow Task
Script Task
Execute Package Task
File System Task
Execute Process Task

This is a list of the which tasks I use most – from most-used to least-used. In this article I provide a basic example of configuring the SSIS Execute SQL Task:

Three things are required to configure an Execute SQL Task:

Connection Type
Connection Manager
SQL Statement

Connection Type

The default Connection Type is OLE DB:

I configure Execute SQL Tasks to execute SQL statements like:

truncate a table
update or insert a single row of data
call a stored procedure

I typically use OLE DB or ADO.NET connection types; but I occasionally use ODBC connection types. When executing parameterized statements I find ADO.NET offers a cleaner interface.

After selecting a connection type, selecting the Connection Manager is next. The Execute SQL Task Editor filters the list of available connection managers, displaying only connection managers of the (connection) type configured in the previous step:

The last required step to configuring an SSIS Execute SQL Task is to supply an SQL Statement. There are two properties for this: SQLSourceType and SQLStatement. The SQL Source Type property specifies the source of the SQL Statement as one of the following:

Direct Input
File Connection
Variable

The SQL Statement can be entered manually (Direct Input). It can be stored in a file (File Connection) or the SQL Statement can be stored in an SSIS variable (Variable). In most cases you will manually enter the query: