Execute SQL Task in SSIS

The Execute SQL Task in SSIS is used to run SQL queries (statements) or stored procedures from the SSIS package. You can use this SSIS Execute SQL task to write the single SQL statement or multiple SQL 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 task to truncate tables or views
Recreating (drop and create) the dimension and fact tables before loading the data into them.
Run stored procedures.
Save the result set in a variable so that we can use that data in other tasks.

Execute SQL Task in SSIS Properties

In this article, we will show you the steps involved in configuring the Execute SQL Task in SSIS with an example. First, Drag the Execute SQL Task in SSIS Toolbar and drop it into the Control Flow region

Double click on it will open the SSIS Execute SQL Task Editor to configure it.

SSIS Execute SQL Task General Tab

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

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

In real-time, please change the 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.

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 infinity seconds.
CodePage: Please specify the code page that the task wants to use while converting the Unicode values in variables. By default, it will use the local computer code page.
Type Conversion Mode: By default, it is Allowed, and it means the execute SQL task will attempt to convert the query results and output parameters to data types of the variable

Connection Type

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

Connection

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

SQL 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 an SQL Statement directly, then select this option.
File Connection: If your SQL Query is in the file system, then please select this option
Variable: If your query is in a variable, please choose this option.

The next property completely based on the SQL 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 source type, the next property will be changed to SQL Statement. And we can use this property to write the SQL statement. To demonstrate, we are writing the following query.

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

IsQueryStoredProcedure

If you selected the Connection type as ADO or ADO.NET, then only the property value is enabled. If your query is a stored procedure, change the property value to True.

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 are using the Execute SQL Task to truncate the table. Then there is no point in returning any value.
Single Row: If you want to return a single row as the result set, then you can use this option — for example, the total number of records in a table, or columns, etc.
Full Result Set: If you want to return more than a single row as the result set, then you can use this option.
XML: If you want to return the XML file as the result set, you can use this option.

Here is the final look of our configured SSIS execute SQL task

SSIS Execute SQL Task Parameter Mapping Tab

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

If you observe the above SQL statement, we specified the query as Sales > ?. It means we have to 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 that we are getting from our query. Remember, if you specified the Result set property as None, then this tab is disabled.

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

Here,

Result Name: Please specify the valid name that 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 that is a connection to this execute SQL task.
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.