Execute SQL Task in SSIS Example

How to truncate the 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 database

Source Table

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 Task 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 Statement. If you already created the Connection, then select from the list or else click <New connection…> to create a New Connection.

New OLE DB Connection 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.

Configure Server Data Connection 5

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

Select the Database 6

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

Execute SQL Task in SSIS Example 7

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

The CODE for Execute SQL Task in SSIS example

TRUNCATE TABLE [Employee Table2]
Execute Truncate Table Query 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 table or not.

Execute SQL Task in SSIS Example 10

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

Destination Table