Bulk insert task in SSIS.

In this article we will discuss about bulk insert task.

Bulk insert task in SSIS allows us to load data from text file or flat file into SQL server database table using BULK insert command or BCP.EXE (Bulk Copy program) command line utility tool. This functionality gives fastest way of loading data.

BULK insert is a SQL statement which is used import data from flat file but BCP is an utility used in command prompt to load data from flat file into Database table

Read online articles for more details to learn more about Bulk insert command and BCP.exe utility

Drag Bulk insert task from tool box onto control flow as shown in figure 1.

Figure 1

When you double click Bulk insert task it will open task editor to configure as shown in figure 2. If task is not configured properly one red symbol shown as shown in figure 1

Figure 2

In General tab, Suitable name and description for the task should be provided.

Next tab is Connection where source file connection and destination table should be provided as shown in Figure 3

Figure3

Select destination from drop-down list if there are any created. Next, Select destination table from next Destination Table drop down box.

Next, specify the file using source connection by File drop down box. Both source and Destination should be created in connection managers tab. if not create new connections by selecting <New connection….> tab in respective connection drop-down box.

After specifying source and destination connections, you need to specify the format which is having two options. Options 1 specify allows Row delimiter and Column delimiter which are having same set delimiters as shown in figure 4

Figure 4

{CR}{LF} = New Line

{CR} = Carriage return

{LF} = Line feed

{;},{,}, Tab, Vertical Bar {|}

When option 2 Use File selected, you need to specify the format (.fmt) file which is created using BCP utility as showing figure 5

Figure 5

In the Options tab, you need to specify options for inserting data from file into table.

Figure 6

In Advanced Options, You can specify code page of source and you can rarely change Codepage type from RAW which is default. The DataFileType option can specify what file type is. Options here are char, native, wide char, wide native. Generally files will be loaded using char type but some times if file is being loaded using format file (.fmt) which is created native option you then need to specify native in DataFileType.

Other Advanced options, Batch size specifies how many rows should be executed as single batch. By default batch size is 0 which means all rows will be executed as single batch and you can specify first row and last row to copy using FirstRow and LastRow options

The options drop down box contains five options as shown in figure 7.

Figure 7

Check constraints: This options checks constraints on columns before committing the record. This option is turned on by default.

Keep nulls: this option keep nulls if any column is left as blank.

Enable Identity insert: This option should be enabled if destination table has identity column. If not this task will be failed

Table lock: This option creates SQL lock on the destination table from insert and update options from other processes.

Fire Triggers: Enable if this option if insert triggers need to be fired while inserting data on the table. By default this option is turned off.

Other options are SortedData which specifies what column you wish to sort by while inserting data. By default sortedData is false. Type the column name in the SortedData field if you need to specify. The MaxErrors option specifies how many error rows can be ignored while inserting data before the task is stopped with an error. By default MaxErrors is 0, if single row has a problem, entire task fails.

NOTE: Please leave comment if you like the post

Technorati Tags: SSIS,MSBI,Bulk insert task,task,bulk insert