Tuesday, August 18, 2015

Bulk Insert Task

Bulk Insert Task

This is being used to insert the records from some text file or csv file into a table.

Following properties should be set.
column delimiter can be comma, semicolon, tab, pipeline, etc.

If 1st row is header, then FirstRow should be set to 2.




Bulk Insert Task

Introduction

As the name suggests the BULK Insert task is used to insert bulk data from source to destination. But the transformation of data is not possible over here. It straight forwards insert bulk data from source to destination. The configuration of bulk insert task is quite simple and there is no need of  data flow components over here. Just one control flow tasks is sufficient to insert data from source to destination.
In this article I am trying to illustrate the feature and configuration of Bulk Insert task of control flow.

Type of Source and Destination
The source and destination can be any of the data sources as we have such as OLEDB, excel, etc.

Configuring Bulk Insert Task

Step-1 [ The flat file source and Destination Table ]

The flat file name is "BulkInsertSource.txt"



Destination Table objects name is "tbl_BulinsertDestination"

IF OBJECT_ID(N'tbl_BulinsertDestination', N'U') IS NOT NULL
   BEGIN
     DROP TABLE tbl_BulinsertDestination;
   END
GO
CREATE TABLE tbl_BulinsertDestination
      (UNIT             VARCHAR(50)       NOT NULL,
       [YEAR]           VARCHAR(4)        NOT NULL,
       [PERCENT]        DECIMAL(20,2)     NOT NULL);
GO      

Step-2  [ Control Flow tasks ]



Step-3 [ Bulk Insert Task Editor ]



Step-4 [ Result Set ]

SELECT * FROM tbl_BulinsertDestination;

UNIT      YEAR     PERCENT
East         2010       50.00
East         2011       55.00
East         2012       60.00
East         2013       45.00
West       2010       0.00
West       2011       40.00
West       2012       56.00
West       2013       72.00
North      2010       5.00
North      2011       12.00
North      2012       16.00
North      2013       45.00                      


Hope you like it.