Task- Bulk Insertion

Posted on Updated on

Bulk insertion is used to transfer data from files to SQL server tables.

In this article, I provide an overview of bulk insertion task of SSIS.

  1. Create a package named BulkInsert.dtsx
  2. Open SSMS and create Employee table in AdventureWorks2012.

USE [AdventureWorks2012]

GO

CREATE TABLE [dbo].[Employee](

[EmpId] [int] NOT NULL,

[JobTitle] [nvarchar](50) NOT NULL,

[BirthDate] [date] NOT NULL,

[MaritalStatus] [nchar](1) NOT NULL,

[Gender] [nchar](1) NOT NULL,

[HireDate] [date] NOT NULL

) ON [PRIMARY]

GO

  1. Back to your dtsx package and drag -n drop Bulk insert task on control flow design pane. The Task editor has red sign on upper right corner of task indicates it’s not configured yet.
  2. Right click on Task and choose edit option or double click on task.

In General provide name LoadEmployeeDetails AND in description load Employee details from flat file.bulk-insertion-task

1

  1. Click on Connection tab. From the Connection drop down box, select.

This will open the Configure OLE DB Connection Manager dialog. Now, you’re going to create a connection to the AdventureWorksDW12 database

2

  1. Click OK to go back to the previous screen, and click OK again to return to the Bulk Insert

Task Editor.

You’ll now see that the Connection Manager you just created has been transposed into the Connection dropdown box.

  1. To Define Destination Table – Click on destination table and select dbo.Employee
  2. Leave the remaining options set to the defaults. The RowDelimiter property option will be

{CR}{LF} (a carriage return) and the ColumnDelimiter property should be set to Comma {,}.

  1. For the File option, again select to create a new Connection Manager.

This will open the File Connection Manager Editor.

3

  1. For the Usage Type, select Existing File. Then point to employee.csv for the File option. Click

OK to return to the editor. Your final Bulk Insert Task Editor screen should look similar to

If you have column header in flat file then go to Options and change first row to 2.

This would start to import data from second row.

Finally right-click on package and execute it. After execution completed go to SSMS and check the table data.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s