SQL Server Integration Services (SSIS) - Create/Delete a table in SQL using SSIS

Introduction

In this article we are going to see how to run a query from SSIS. Mainly create or delete a table from a SSIS package. We have a task called Execute SQL Task in SSIS that helps us to do this task. Let's see how to use this task and create a table using SSIS. This task will be mainly used in a scenario such as if we ever need to check if a table exists or not and to create a table using this task.

Steps

Follow steps 1 to 3 in my first article to open the BIDS project and select the integration services project to work on. Once the project is created, we will see how to use Execute SQL task and create a table using the same.

I just dragged and dropped the execute SQL task as shown in the above image. Double-click it; it will open the configurations tab as shown below.

Here we need to configure the connection and the SQLStatement. Here the statement we are going to execute is a create table statement as shown in the code below.

CREATE TABLE [dbo].[EmployeeTable]([EmpID] [int] NOT NULL, [EmployeeName] [nvarchar](100) NULL, [EmpDesignation] [nvarchar](100) NULL, [EmpAge] [int] NULL,) ON 
[PRIMARY] GO

Now we are confirmed that the configuration is done and we are ready to run the package.

Press F5 to build and execute the package as shown in the screen below.

The output of the package is the creation of table Employeetable in the Northwind Database. Just go to the SSMS and query the table using: "Select * from Employeetable". We will see the table with no recrods as shown below.

In a similar manner we can create an Execute SQL task in order to delete the table with the same structure.

Conclusion

So in this article we have seen how to use the Execute Sql task container to Create or Delete a table from SSIS packaging.