Execute T-SQL Statement Task in SSIS 4

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 the 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 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 Database.
  • Execution Time Out: Here, you can limit the execution time of a the query. If the query exceeds this time, then the task fails.
  • Write the Transact query 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 the 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 the Microsoft SQL Server.
  • Use a specific user name 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 as 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

Output Table

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