Working with BULK INSERT Task in SQL Server

Using BULK INSERT Task we can import data from text file to SQL Server .
First I have created a table emp in destination SQL server database.

CREATE TABLE [dbo].[emp](

       [empid] [int] IDENTITY(1,1) NOT NULL,

       [name] [varchar](100) NULL,

       [salary] [int] NULL,

PRIMARY KEY CLUSTERED

(

       [empid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Input data of text file 

7,sami,20000

8,rao,30000

9,rema,24000

BULK INSERT Task is similar to BULK INSERT Command in T-SQL. 

The Following is the Example of BULK INSERT command

BULK INSERT dbo.emp

   FROM 'd:empdetails.txt'

   WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n'

      );

When loading bulk insert command ensure that number of columns is source should macth with destination table . other wise you may this type of error.

 Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (empid).

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (empid).

Msg 4832, Level 16, State 1, Line 2

Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 2

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

  

So always source and destination columns should match when using BULK INSERT Task.

Drag and drop the bulk insert task from control flow

Double click on the bulk insert task and GOTO connection tab

We can specify the format or can use the format file to import the data. Once we have used the specify in the format options then we can have to specify the row delimiter and column delimiter for the specified input file as shown in the picture. then GOTO options tab.

KEEPIDENTITY is used for keeping the existing identity values in the input file. If we check the check box then it will use the existing values. 

The T-SQL code is similar to checking the keepidentity 

BULK INSERT dbo.emp

   FROM 'd:empdetails.txt'

   WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n',

         KEEPIDENTITY

      );

If we unchecked the check box then it will use the destination table identity seeding values. click ok and save the Task.