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
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
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.
If you have already created the connection, select it. Otherwise, click on the New button to specify the connection properties
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
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
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
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.