Execute SQL Task in SSIS

The Execute SQL Task in SSIS is used to run queries (statements) or stored procedures from the package. You can use this SSIS Execute SQL task to write the single statement 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 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

Execute SQL Task 1

Double click on it will open the 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 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.

Execute Task Editor 2

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
Execute Task Name and Description Properties

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 MOBILE

Execute Task Connection Type 4

Connection

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

Create aNew Connection to Sever

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, then select this option.
  • File Connection: If your Query is in the file system, then please select this option
  • Variable: If your query is in a variable, please choose this option.
Choose the Source Type 6

The next property 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.

File Connection Manager

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 statement. To demonstrate, we are writing the following query.

SELECT [Id]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
FROM [Employee Table]
WHERE [Sales]  > ?
Execute SQL Task in SSIS 8

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.
Execute SQL Task in SSIS 9

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

Execute SQL Task in SSIS 10

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 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.

Execute SQL Task in SSIS 11

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

Execute SQL Task in SSIS 12

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 in SSIS 13

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.

Execute SQL Task in SSIS 14

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.
  • 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 Task Result Set Variables 15

Execute SQL Task Expressions Tab

It is used to write expressions.

Execute Task Expressions 16

Comments are closed.