In the previous post we looked in How to Execute SQL Query or Store Procedure from SSIS and successfully created a package which execute SQL Query or Procedure on targeted SQL Server with the help of this. In this post we look in How to Load Data from text Files to SQL Server, in daily life several time we need to load data in SQL Server tables, we can achieve this with the help of SSIS. We look into the detailed process of loading data from text files to SQL Server tables.

Load Data From Text File to SQL Server Tables

We can load data from text files in to SQL Server tables with the help of Data Flow Task Control. We will see how to use Data Flow Task in detail. Before that you need to create a project or a new package in existing project, you can create a new project or add a new package to project as shown in the previous post.

So you can add a new package in your SSIS project as shown in previous post, now you have a new package in your project name it as loaddata.

Adding Data Flow Task Control to Package

Now you have a package so this is time to add Data Flow Task to your package, to add Data Flow Task control to the package, double click on the item or just drag and drop item from from the toolbox to your package window.

Now you have Data Flow Task control on your package window the item will show up on the package like this (see the below image)

Configuring Data Flow Task

Now we have Data Flow Task added in your package, the next step is to configure Data Flow Task. To configure Data Flow Task right click on the control and choose edit option to configure the control.

You will see a new window opened at your screen and you are navigated to Data Flow tab within your current package (as shown in below image).

Now we have a blank Data Flow Task and we have to configure it to load data from a text/csv file and insert it into an SQL Server table. For this we have to configure Data Flow Task, and we need to setup a source connection for Flat File and a destination connection for OLE DB.

Setting up an Flat File Source

To configure a Flat File Source, go to the toolbox and under the Data Flow Sources list you can find Flat File Source. To add the Flat file Source to the package, double click on the control or simply drag and drop it to the package.

Now you have to configure Flat File Source, for configuring Flat File Source right click on Flat File Source control and choose edit and you will get a window name Flat File Source Editor( image as shown below).

Now you have to create a new flat file connection, for this click on the New button on the Flat File Source Editor (as shown in the image above) and you will see a new window named Flat File Connection Manager Editor (as shown in image below).

You have to create a new connection with the help of this Flat File Connection Manager Editor you have to fill following information in General tab to configure it correctly.

Connection Manager Name : Name of the connection.
Description : Description(anything you want to write).
File Name : Here gives the path of the file in file system.
Locale : Default Value.
Code Page : Default Value.
Format : Choose Delimited(you have options Delimited, Fixed width, Ragged right choose appropriate, in our case delimited.)
Text Qualifier : “(In our case)
Header row delimiter : {CR}{LF} (default).
Header rows to skip : 0 (if you want to skip some rows you can put it there like 2 or 10 or whatever).
Column names in first row : Leave it uncheck.

Fill up the following connection and click on Columns Tab you will see the columns of your file in this tab, you can also change many properties of your columns go to Advance Tab and you will options like Data Type, OuputColumnWidth etc. you can alter them as per need. Now click OK and you will get Flat File Source Editor window again just once check the columns tab and click ok and you are done with configuring Flat File Source.

Setting up an OLE DB Destination

We have a Flat File Source from which we can take data from a flat file, now we have to configure a OLD DB Destination through which we will put our data into SQL Table. For adding OLE DB Destination in your Data Flow Task, go to Data Flow Destinations block in the toolbox and locate OLE DB Destination (as shown in image below) and double click on the control or drag and drop it to your Data Flow Task window.

Now the OLE DB Destination control needs to be configured. To configure the control, right click on the control and choose edit and you will see a new window named OLE DB Destination Editor. In the Connection Manager tab of OLE DB Destination control you have to provide information like connection, load method and the table name in which you have to insert data. Click on the New button and Configure OLE DB Connection Manager window will open up(see image below) you can create a New Connection or you can choose an existing database connection.

In this example we are going to choose already created connection in previous post (you can create a new as shown in previous post) or you can alternatively choose to New Connection as Create it as shown in previous post.

Click ok after setting up a connection to database. In Data Access Mode option of OLE DB Destination Editor choose Table or view- Fast Load. Now you have to choose table in the database in which you want to insert data if you already have table or view in database in which you want to insert data then you can choose it directly from the dropdown else you have to create a New Table for this you have to click New button provided in front of dropdown box after clicking that a Create Table window will open up, we are using a New Table in this example (as shown below in image).

Now click Ok. Now navigate to Mappings option in OLE DB Destination Editor here you have to set the mappings between source and destination. Mapping decides which input column is in inserted to which output column. We map input and output columns as shown below in image and click Ok.

Now you are done column mappings and back on OLE DB Destination Editor now click Ok on this window and you are done with configuring OLE DB Destination and so your data flow task, your final Data Flow Task will looks like as shown below in image.

So now you are done with your package just save this package and run it by just right click on your package in solution explorer and click Execute Package. If your package succeeded than it will look like this( as shown in image below).

You can check the results by querying the newly created table in your database. For this you have to query your newly created table like this

SELECT * FROM tbl_names

and you will find 500 records in your table.

So in this way you can create a package which takes data from a text file and insert it into an SQL Server table, your file type may be different like you may have CSV, Fixed Width or Tab Separated file still you can insert data into SQL Server table using this kind of files you just have to choose appropriate file type in Flat File Source.

If you found any problem in configuring this package or came across to any error, just post that error or problems in comments we will make sure that it will be rectified as soon as possible.