Thursday, 30 May 2013

Bulk Insert Task


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
     DROP TABLE tbl_BulinsertDestination;
CREATE TABLE tbl_BulinsertDestination
      (UNIT             VARCHAR(50)       NOT NULL,
       [YEAR]           VARCHAR(4)        NOT NULL,
       [PERCENT]        DECIMAL(20,2)     NOT NULL);

Step-2  [ Control Flow tasks ]

Step-3 [ Bulk Insert Task Editor ]

Step-4 [ Result Set ]

SELECT * FROM tbl_BulinsertDestination;

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.

Posted by: MR. JOYDEEP DAS


  1. Very nice. Thanks for sharing.

  2. HI,

    There is one question regarding this post

    Is it possible to use Excel as a source???
    As of my knowledge: NO

    Please correct me if am wrong.