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
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.
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.
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.
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.
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.
Here, we are selecting the SQL Source Type as Direct Input. Next, Within the Statement option, click on the … button
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]
Click OK to close the Execute SQL task editor
Let us run and see whether we successfully truncated the table or not.
Let us open the Management Studio, and check whether the table data truncated or not.
