Execute SQL and Bulk Insert Tasks in SSIS

Introduction

SQL Server Integration Services (SSIS) is a Business Intelligence tool which can be used by database developers or administrators to perform Extract, Transform & Load (ETL) operations. In my previous article entitled Using Data Profiler Task and FTP Task in SQL Server 2008 Integration Services I discussed how to use the Data Profiling Task and the FTP Task which are  available in SQL Server 2005 Integration Services and later versions. In this article I will examine the use of the Execute SQL Task and the Bulk Insert Task which are available in SQL Server 2005 and later versions.

Overview of SQL Server Tasks in SSIS 2005 & Later Versions

The SQL Server tasks within SSIS can be used to copy, modify and delete SQL Server objects and data. The different types of SQL Server Tasks which are available in SSIS are Execute SQL Task, Bulk Insert Task, Transfer Database Task, Transfer Error Message Task, Transfer Job Task, Transfer Login Task, Transfer Master Stored Procedure Task and Transfer SQL Server Objects Task. If you are new to SQL Server Integration Services then I would recommend you to start with my previous article entitled SQL Server 2008 Integration Services Tasks. In this article you will see an example for how to configure and use Execute SQL Task and Bulk Insert Task.

Overview of Execute SQL Task

The Execute SQL Task can be used to execute any TSQL code such as truncating a staging table, executing queries, stored procedures etc from a SSIS Package. This task can also be used in combination with For Loop and Foreach Loop containers to run multiple TSQL statements.

Overview of Bulk Insert Task

The Bulk Insert Task can be used to insert data from a text file or a flat file into a SQL Server Table in the same way the  BULK INSERT statement or   BCP.EXE command line tool is used. It is advised to use the Bulk Insert Task in applications which require quick loading of large amounts of data.

Example Using Execute SQL Task and Bulk Insert Task

In this example we will use an Execute SQL Task to create an Employee table within the SSISExample database and then use the Bulk Insert Task to insert the data into an Employee table from a comma separated text file.

1.    Create a new SQL Server Integration Services Project and rename the default package BulkInsertTaskExample.dtsx

2.    Double click the BulkInsertTaskExample.dtsx package to open it up in Design Mode.

3.    Drag and drop “Execute SQL Task” from the toolbox to the Control Flow window and rename it “Create Employee Table”. Then double click the Execute SQL Task to open up Execute SQL Task Editor as shown in the below snippet.

In Execute SQL Task Editor, you will need to configure a connection to a SQL Server, for the SQLSourceType property choose  Direct Input from the drop down list. Next, copy the below TSQL code and paste it into the SQLStatement textbox. Finally click OK to save changes in Execute SQL Task Editor.

Use SSISExample

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))

DROP TABLE [dbo].[Employee]

GO

CREATE TABLE [dbo].[Employee]

(

                [EmpID] [int] NOT NULL,

                [EmployeeName] [nvarchar](100)  NULL,

                [Title] [nvarchar](100)  NULL,

                [Age] [int] NULL,

) ON [PRIMARY]

GO