Execute SQL and Bulk Insert Tasks in SSIS
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
Overview of SQL Server Tasks
in SSIS 2005 & Later Versions
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.
a new SQL Server Integration Services Project and rename the default package BulkInsertTaskExample.dtsx
click the BulkInsertTaskExample.dtsx package to open it up in Design Mode.
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.
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.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[Employee]‘) AND type in (N’U’))
DROP TABLE [dbo].[Employee]
[EmpID] [int] NOT
[EmployeeName] [nvarchar](100) NULL,
[Title] [nvarchar](100) NULL,
[Age] [int] NULL,
) ON [PRIMARY]