Tutorial Gateway

Execute T-SQL Statement Task in SSIS

The Execute T-SQL Statement Task in SSIS is used to run only the T-SQL statements. In this article, we are going to create the New table and send the following data into that newly created table.

Execute T-SQL Statement Task in SSIS 0

Execute T-SQL Statement Task in SSIS

In this example, we are going to explain the functionality of the Execute T-SQL Statement Task in SSIS. For this demonstration, we will create the New table and send the following data into that newly created table.

First, Drag and drop the Execute T-SQL Statement Task in SSIS toolbox to Control Flow Region

Execute T-SQL Statement Task in SSIS 1

Double click on the task will open the SSIS Execute T-SQL Statement Task editor to configure it

  • Connection: Please specify the Server instance name and credentials to log into the SQL Server Database.
  • Execution Time Out: Here, you can limit the execution time of a T-SQL query. If the query exceeds this time, then the task fails.
  • T-SQL Statement: Write the T-SQL statement you want to execute.
Execute T-SQL Statement Task in SSIS 2

If you have already created the connection, select it. Otherwise, click on the New button to specify the connection properties

Execute T-SQL Statement Task in SSIS 3

The following are the Connection Properties of the Execute T-SQL Statement Task

Connection Name: Please provide a unique name for this connection. In this example, We changed it as Execute T-SQL Statement Task Example

Select or enter Server Name: By clicking the … button you select, or if you know your server name, then type it in the text box.

Enter information to log on to the server: Specify how to connect with the SQL Server

  • Use Windows integrated security: If you select this option, Execute T-SQL Statement Task will use Windows Authentication to connect the Microsoft SQL Server.
  • Use a specific user name and password: If you select this option, the Execute T-SQL Statement Task will use SQL Server Authentication to connect the Microsoft SQL Server. Please provide the Username and Password to connect to a server instance
Execute T-SQL Statement Task in SSIS 4

From the above screenshot, you can observe that we are connecting to our server using windows security.

Click Ok to finish the Connection Properties and write the following T-SQL Statement

SELECT  [GeographyKey]
       ,[City]
       ,[StateProvinceCode]
       ,[StateProvinceName]
       ,[CountryRegionCode]
       ,[EnglishCountryRegionName]
       ,[PostalCode]
INTO [SSIS Tutorials].[dbo].[Execute T-SQL Statement Task Output]
FROM [AdventureWorksDW2014].[dbo].[DimGeography]

The SELECT INTO Statement will create a new table and then insert the rows from the select statement. In this example, we are creating a new table in the [SSIS Tutorials] Database, and the table name is Execute T-SQL Statement Task Output

Execute T-SQL Statement Task in SSIS 5

Click ok to finish configuring the Execute T-SQL Statement Task. Let us run and see whether it is executing the T-SQL statement or not

Execute T-SQL Statement Task in SSIS 6

Let’s open the SQL Server Management Studio and check the result

Execute T-SQL Statement Task in SSIS 7

We successfully inserted the selected rows into a new table using Execute T-SQL Statement Task in SSIS.