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.

Execute T-SQL Statement Task in SSIS

In this example, we are going to explain the functionality of the 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.

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.

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

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