Execute SQL Task in SSIS

The Execute SQL Task in SSIS runs queries (statements) or stored procedures from the package. You can use this SSIS Execute SQL task to write single or multiple statements that can run sequentially.

We can use this SSIS Execute SQL Task for the following purposes:

Create, Alter, and drop the tables and views.
Before we start inserting the data into the table, we can use this SSIS Execute SQL Task to truncate tables or views.
Recreate (drop and create) the dimension and fact tables before loading the data into them.
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.

Double click on it will open the Editor to configure it.

General Tab

This tab is used to configure the SSIS Execute SQL Task properties.

Name: Please provide the Unique Name
Description: Briefly describe the Task Functionality. It is always a good practice to provide a valid description.

In real-time, please change the SSIS Execute SQL Task name to an appropriate name, and write the description to explain the task functionality. The description helps other developers to understand the SSIS task better.

This page completely focuses on executing a single query. However, combining the Execute SQL Task with the Foreach loop will run multiple queries simultaneously. Please refer to the Execute SQL Task to Run Multiple Transact Query Files.

SSIS Execute SQL Task Options Tab.

TimeOut (in seconds): Please specify the connection timeout in seconds. If the connection takes more than that time, then the connection will fail. By default, it is 0, and it means infinite seconds.
CodePage: Please specify the code page the task wants to use while converting the Unicode values into variables. By default, it will use the local computer code page.
Type Conversion Mode: By default, it is Allowed, and it means the SSIS execute SQL task will attempt to convert the query results and output parameters to data types of the variable

Execute Task Name and Description Properties

Connection Type

Please select the Connection Manager you want to use for the data source. You can see the available connection types provided by the Execute SQL Task in SSIS by clicking the down arrow. And they are OLE DB, EXCEL, ODBC, ADO, ADO.NET, and MOBILE.

Connection

Configure the Connection Manager settings for the Statement. If you already created the Connection, select from the list or click <New connection…> to create a New Connection.

Source Type

Please specify, How you want to provide your source data. This SSIS Execute SQL Task provides three options.

Direct Input: If you want to write the Statement directly, select this option.
File Connection: Please select this option if your Query is in the file system. Refer to the using File connection article for an example.
Variable: If your query is in a variable, please choose this option.

The next property is completely based on the Source Type option. For example, if we select the File system as the source type, the following property will change to File Connection. And we can use this property to locate the select the file.

For example, if we select the Direct input as the SSIS Execute SQL Task source type, the next property will be changed to SQL Statement. And we can use this property to write the statement. To demonstrate, we are writing the following query.

SELECT [Id]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM [Employee Table]
WHERE [Sales]  > ?

IsQueryStoredProcedure

Only this property value is enabled if you select the Connection type as ADO or ADO.NET. Change the property value to True if your query is a stored procedure.

Result Set

This SSIS Execute SQL Task property helps us to configure the output. It has four properties:

None: If you don’t want to return anything, select this option. For example, you use the Execute SQL Task to Truncate the table. Then there is no point in returning any value.
Single Row Result Set: If you want to return a single row result set, then you can use this option — for example, the total number of records in a table, columns, etc.
Full Result Set: If you want to return more than a single row as a result set, you can use this option.
XML: If you want to return the XML file as a result set, you can use this option.

Here is the final look at our configured execute SQL task.

SSIS Execute SQL Task Parameter Mapping Tab

You must use this Execute SQL Task Parameter mapping tab to map the parameters specified in our query. If you don’t have any parameters in your query, quit this section.

If you observe the above statement, we specified the query as Sales > ?. We must pass the value (or variable) to that question mark. So, please click on the Add button to add the result set name.

Once you click on the Add button, it will enable you to map the variable to the parameter.

Variable Name: This property displays the list of the available system variable and user variables (if any); please select the appropriate variable. If you haven’t created any variable before, please click on the <New Variable..> as shown below.

Execute SQL Task Result Set Tab

You have to use this SSIS Execute SQL Task Result Set tab to map the result we get from our query. Remember, if you specified the Result set property as None, this tab is disabled.

Please click on the Add button to add the result set name.

Here,

Result Name: Please specify the valid name you want to assign for the result set. I suggest you use the meaningful name because we will use this name in a subsequent task connected to this.
Variable Name: This property displays the list of available user variables (if any). Please select the appropriate variable. If you haven’t created any variable before, please click on the <New Variable..>.

Execute SQL Task Expressions Tab

It is used to write expressions.