Create Table from text file in SSIS 2008

Well, I have already written few example of how to create flat/text file from SQL Server via SSIS. You can refer those old articles at below links:

Dynamic name of text file generated by Script Task in SSIS 2008

Create text file from Stored Procedure output in SSIS with Flat File Destination – SQL Server 2008

This is the time now to go reverse, above examples shows you how to generate text file from SQL Server table but now I am going to show you how to generate SQL Server table from text file.

First generate one text file in your D drive with following data.  Following data would be there in one text file, may be named as “Emp.TXT”. Data is pipe separated; you can have any other separator too like comma.

ritesh|shah|MIS

rajan|shah|account

Alka|Shah|management

Once you have your text file ready, open BIDS (Business Intelligence Development Studio), create new project for SSIS and drag “Data Flow” task from tool bar and drop it to your control area. Once you have dataflow task, double click on it to configure.

Now, important part of this article is coming, before we jump into it, let me tell you, you will need two things in order to copy your text file data to SQL Server.

1.)    As a first thing, you will need source (your text file)

2.)    You will need destination (your SQL Server)

This is just a basic understanding and minimum these much steps needed for copy from any other source to destination.

So, as a first step, we are going to have, “Flat File Source” task in our “Data Flow” tab, double click on it to configure.

In “Flat File Source Editor”, click on “New” to create new source of your text file and name it to “EmpTextFile”.

Browse the text file which we have generated in D drive with name “EMP.TXT”.

We don’t have header information in our text file so table will be created with “Column0”, “Column1” etc., if you want to give meaningful name to your columns,  click on “Advanced” tab in “Flat file connection manager editor” and give meaningful name.  In our case, we will use following name for our columns in “Column” property.

1.)    FirstName

2.)    LastName

3.)    Department

For more details, let us look at below given screen capture:

Click on OK in both the dialog box. And now take “Ado.Net Destination” task from tool box to your control area. Connect green arrow from “Flat File Source” to “Ado.Net destination” task.

Double click on “Ado.Net Destination” task to configure it. To setup database and Server, click on “New” button in “Connection Manager” area.

If you already have a table in your database, select it from drop down list of “use a table or view” , if you don’t already have table in your database, you can create new table by clicking on “New” button. For more details, have a look at screen capture given below.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com