Tutorial Gateway

Execute T-SQL Statement Task in SSIS

by

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 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 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 then select it otherwise, Click on the New button to specify the connection properties

Execute T-SQL Statement Task in SSIS 3

Following are the Connection Properties of the Execute T-SQL Statement Task

Connection Name: Please provide 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 then, 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 then, 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 own 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]

SELECT INTO Statement will create new table and then insert the rows from the select statement. In this example, we are creating 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

Lets 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 new table using Execute T-SQL Statement Task in SSIS.

Thank You for Visiting Our Blog