SSIS Execute SQL Task

The Execute SQL task is one of the most useful task in SSIS packages and it is used to run a SQL statement, multiple SQL statements or stored procedures on a database connection. The connection type can be set as one of following managers.

  • EXCEL: Excel Connection Manager
  • OLE DB: OLE DB Connection Manager
  • ODBC: ODBC Connection Manager
  • ADO: ADO Connection Manager
  • ADO.NET: ADO.NET Connection Manager
  • SQLMOBILE: SQL Server Compact Edition Connection Manager

The SQL Source Type can be set as one of the following.

  • Direct input: Input the SQL statements or stored procedures in the SQLStatement field directly.
  • File connection: Use a FileConnection to connect to a file which contains the SQL statements to run.
  • Variable: Use a variable to hold all the SQL statements and stored procedures.

After the task runs we'll get the result set and the result set can be set as Single row, Full result set, XML or None in the task, binding to one or several user-defined variables. The following table summarizes the result sets and their binding variables.

Result SetData type of VariableType of objectResult Name
Single rowCompatible type with result setN/AThe same as output field name
Full result setObjectADO Recordset if the connection is ADO, OLE DB, Excel or ODBC.
System.Data.DataSet if the connection is ADO.NET
0
XMLStringString0
XMLObjectMSXML6.IXMLDOMDocument if the connection is ADO, OLE DB, Excel or ODBC.
System.Xml.XmlDocument if the connection is ADO.NET
0

We'll create a sample package to demonstrate all the 4 different result set types in 4 sequence containers. Let's get started.

In this package, we need OLE DB Connection to connection a SQL Server database.

  1. Create an empty package ExecuteSQLTask.dtsx in the LearnSSIS1 project and open it in design mode.

  2. Right click Connection Managers section and choose "New OLE DB Connection..." to open Configure OLE DB Connection Manager.
  3. Configure OLE DB Connection Manager


  4. Click "New..." button to open Connecton manager window and make the settings as below.
  5. SSIS Connection Manager

    In this example, the SQL server is my local machine so localhost is chosen and chose "Use Windows Authentication" to connect to the database. Of course you can set the server in another machine and use SQL Server Authentication. The database Test was created on the server and was chosen here. After the settings, you can click "Test Connection" button to verify if the connection can be connected successfully.


  6. Click "OK" twice to finish the settings and you will see the OLE DB connection manager "localhost.Test" is displayed in the connection managers section.
  7. OLE DB Connection localhost.Test


  8. Drag & drop the sequence containers, Execute SQL Tasks and other tasks to finish the layout as following.
  9. Execute SQL Task Test Package Layout

    The sequence containers and Execute SQL Tasks were renamed.

We'll enable only one sequence container and disable the others to test the result one by one in the next sub sections.