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.
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
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