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
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.
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 SQL 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 SQL Tutorial 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 SQL Statement option, click on the … button
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]
Click OK to close the Execute SQL task editor
Let us run and see whether we successfully truncated the SQL table or not.
Let us open the SQL Server Management Studio, and check whether the table data truncated or not.
