Execute SQL Task in SSIS Example

How to truncate the SQL tables using the Execute SQL Task in SSIS with an example. It also demonstrates the Result Set option NONE. For this, we are going to use the Employee table present in the SQL Tutorial database

Execute SQL Task in SSIS Example 1

Execute SQL Task in SSIS Example

Drag the Execute SQL Task from SSIS Toolbar and drop it into the Control Flow region. Here, we changed the task name as Execute SQL Task in SSIS example.

Execute SQL Task in SSIS Example 2

Double click on it will open the Execute SQL Task Editor to configure it. As you can see from the below screenshot, we changed the description to demonstrate the property.

Execute SQL Task in SSIS Example 3

In this example, we are using the OLE DB Connection Manager to connect with the source data. Next, configure the Connection Manager settings for the SQL Statement. If you already created the Connection, then select from the list or else click <New connection…> to create a New Connection.

Execute SQL Task in SSIS Example 4

Once you click on the <New connection…>, a new window called Configure OLE DB Connection Manager will open. If there are any data connections, select the same. Otherwise, click on the New button to create a new connection.

Execute SQL Task in SSIS Example 5

Here, we are selecting the SQL Tutorial database using the localhost server name. I suggest you to refer the OLE DB Connection Manager to understand the connection properties.

Execute SQL Task in SSIS Example 6

Here, we are selecting the SQL Source Type as Direct Input. Next, Within the SQL Statement option, click on the … button

Execute SQL Task in SSIS Example 7

Once you click on the … button, a new window called Enter SQL Query opened. Here we are writing the TRUNCATE Table Statement to truncate table

SQL CODE for Execute SQL Task in SSIS example

TRUNCATE TABLE [Employee Table2]
Execute SQL Task in SSIS Example 8

Click OK to close the Execute SQL task editor

Execute SQL Task in SSIS Example 9

Let us run and see whether we successfully truncated the SQL table or not.

Execute SQL Task in SSIS Example 10

Let us open the SQL Server Management Studio, and check whether the table data truncated or not.

Execute SQL Task in SSIS Example 11