Execute T-SQL Statement Task in SSIS

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.

Drag and drop the Execute T-SQL Statement Task in SSIS toolbox to Control Flow Region

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.

If you have already created the connection then select it otherwise, Click on the New button to specify the connection properties

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

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

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

Lets open the SQL Server Management Studio and check the result

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

Thank You for Visiting Our Blog