Home > SSIS > Bulk Insert using SSIS

Bulk Insert using SSIS


Dear SSIS folks,

Bulk insert is the most import tool when you are handling huge amount of data in file system. In a typical IT industry storing data in a flat file is not a surprising thing. Because, in the data processing journey, our IT industry uses flat file with delimiters for storing business data. Perhaps, COBOL application and other system still generating flat file as one of the output of an application.

Of course, we all know this, and then you may ask me what the purpose of this article is. Sorry, I was telling you a story and just moved out of the topic. Ultimate aim of this article is to explain how to use Bulk Insert Task in SSIS.

Well, again a little story about a person A at his work. I name that person called Mr. A and the other character named Boss. Let’s begin.

Scenario:

Boss: Hello Mr. A, I need a solution for a problem

Mr. A: Please tell me.

Boss: Our MIS team is having lot of text files with millions of rows in it. I need a solution to upload those data into a database system faster and without data loss.

Mr. A: Of course, we can do it. It is simple task for them.

Boss: yes, I know that, But, my concern is to save time and increase productivity of the team. I need automation for this solution too for the daily upload process.

Mr. A: okay, I have an idea.

Boss: tell me quickly

Mr. A: we can use SSIS an ETL tool with Microsoft SQL Server for data storage

Boss: Really, then go ahead explain how to complete this task.

Now, Mr. A will explain how to implement text file upload using SSIS

Implementation:

Step 1: create a package and add Bulk Insert Task

Step 2: configure Bulk Insert Task. Right click one the task and select Edit

Step 3: Go to connection tab, provide source text file connection

Step 4: configure destination database connection and select the data load table.

Step 5: specify the flat file delimiter in the format, I have flat file with Tab delimiters

Step 6: finally, click ok and save the package and execute it.

Thank you for reading this article. Your comments are the most valuable motivation words for me to keep up this work.

For more information about Bulk Inset Task read this :http://msdn.microsoft.com/en-us/library/ms141239.aspx

Advertisements
  1. May 19, 2011 at 1:19 am

    >Hiya, it may be worth covering the TSQL command to do the equivalent as well 😉

  2. May 19, 2011 at 2:40 am

    >Oh and there is now an additional tool which can be downloaded from codeplex that allows you to BCP into SQLAzure, if you have a, if you have a need to, called BCP2SlAzure at http://bcp2sqlazure.codeplex.com/

  3. May 19, 2011 at 7:38 am

    >Thank you AAron!

  4. Jess
    September 9, 2015 at 3:28 pm

    Awesome, thanks

  1. No trackbacks yet.

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

%d bloggers like this: