In this article, we will show you, How to truncate the SQL tables using the Execute SQL Task in SSIS with an example. This will also demonstrate 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 be opened. If there are any data connections then 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 will be opened as shown below. Here we are writing the TRUNCATE Table Statement to truncate table
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 is truncated or not.
Thank You for Visiting Our Blog.