Microsoft Integration: Loading a Flat file into SQL server Databases

Aim:

To convert a flat-file into SQL database file by using SQL Server Integration Services.

Required Tool & files:

• SSIS tool.

• A flat file

• SQL server management studio.

Steps to convert Flat file into SQL database file:

Step 1: 

Open both tools in your system and open you flat file with contain data and take a look at you flat-file what column does it hold and what is a type of it.

Create a project in SSIS and name it accordingly.

Create a SQL table in SQL server management studio with same column name as flat-file have.

Step 2:

After creating the SQL table in the desired database, open SSIS project and start making your connection with your source file (flat file source).

And attach you flat file with source connection.

Step 3:

Now open control flow tab in Package design and drag and drop Data flow in package design from SSIS tool> favourites>data flow.

And double click on data flow icon and add flat file source from SSIS tool box in Data flow tab.

Step 4:

Now set up the connection by double click on flat file source icon in the data flow tab and add a connection in the flat file connection manager.

Now add data Destination by drag on drop OLE DB data destination from SSIS tool.

Step 5:

Now set up a connection for Data destination by right-clicking on connection manager window and select New OLE Db connection.

A new window will pop and then click on new and set you SQL server and right database and click ok.

Step 6:

Now attach connection between source and destination by join arrow from source to destination.

 Double click on destination icon and select table in which you are exporting data to.

Step 7:

To convert data type into the correct format we have to right-click on data source icon in data flow tab and choose advance editor option > Input-output properties > Output Columns > change data into a correct data format which is acceptable in SQL database.

Now when everything is done, we can execute our package.

Before

And see our result in the SQL database after package execution.

Note: While converting Flat file into SQL database file don’t forget to convert data type into a proper Unicode format. 

S. No.	SQL data type	Desired Unicode
1	Int	Four or Eight byte unsigned int [DT_UI4 or DT_UI8]
2.	Varchar(n)	String [DT_str]
3.	Nvarchar(n)	Unicode_string [DT_wstr]
4.	money	Currency[DT_CY]
5.	date	Date[DT_date]

.

And other data types are pretty much the same.