Execute T-SQL Statement Task in SSIS

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

Source Table

Execute T-SQL Statement Task in SSIS

In this example, we are going to explain the functionality of the SSIS Execute T-SQL Statement Task. 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 the toolbox to Control Flow Region.

Drag Data Flow Task 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 Database.
  • Execution Time Out: Here, you can limit the execution time of a query. If the query exceeds this time, then the task fails.
  • Write the Transact query statement you want to execute.
Click the New Connection button 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 SSIS Execute T-SQL Statement Task

Connection Name: Please provide a unique name for this connection. In this example, We changed it as an 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, it will use Windows Authentication to connect to the Microsoft SQL Server.
  • Use a specific username and password: If you select this option, it will use SQL Server Authentication to connect. 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 Statement.

SELECT  [GeographyKey]
       ,[City]
       ,[StateProvinceCode]
       ,[StateProvinceName]
       ,[CountryRegionCode]
       ,[EnglishCountryRegionName]
       ,[PostalCode]
INTO [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 Database, and the table name is the Output.

Select Statement 5

Click ok to finish configuring the Task. Let us run and see whether it is executing it or not.

Execute Execute T-SQL Statement Task in SSIS 6

Let’s open the SQL Server Management Studio and check the result. We successfully inserted the selected rows into a new table using Execute T-SQL Statement Task in SSIS.

Output Table