Load flat file into SQL Server table in SSIS

This article demonstrate how to load flat file into SQL Server table using SSIS.

Lets see step by step implementation of simple basic SSIS package which load the flat file data into SQL Server table.

As you can see, here we have a sample text file which contains customer data and have 9 records only. This file will be considered as Source file while the destination will be SQL Server Table.

Destination is SQL Server table named tblCustomer as shown below. You can also see currently there are no records in table as data will be loaded from Flat file to this table through SSIS package later.

Lets open the SQL Server Data Tool (SSDT) and create a new SSIS package.

Here we create a SSIS package in existing solution for demo purpose.

If you do not have project already created then you can create new SSIS project solution otherwise can add new SSIS package to an existing solution as shown below.

Right click on SSIS packages folder and select New SSIS Package.

Once you select New SSIS package, a new package with default name as Package1 is added inside SSIS packages folder as shown below.

Lets give it a meaningful name as LoadFlatFileToSQLServer.

Note: do not change or remove the package extension .dtsx.

Now go to SSIS toolbox and drag Data Flow Task into Control Flow container or view. The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved.  

While the Control Flow is used to build and modify the control flow in the package.

Now double click on Data Flow Task, it navigates you to Data flow container tab for selected Data Flow Task as shown below.

Data Flow is used to build and modify the data flow for selected data flow task.

Now you can see a Data Flow container opens, for selected Data flow task.

As our source is flat file, So in Toolbox, go to Other Sources  and expand it and select Flat File Source component and drag it into Data Flow container.

Now double click on flat file source component.

Once you double click on Flat file Source component, a Flat File Source Editor window opens.

Here you can select an existing flat file connection manager or create new connection.

Lets click on New.. button.

Once you click on New.. button, a Flat file Connection Manager Editor window opens.

Here you can specify the source file properties, file format and so on..

In General tab, once you specified the flat file name, you can also select the row delimiter, skip any row and tick the checkbox, if flat file has column names in first row.

Now in Columns Tab, you can verify and modify row and column delimiter, also can see the data preview.

Next, in Advanced tab, you can check column data type, and size also can add new column, modify the details of an existing column, and delete them if required.

After that in Preview tab, you can see the preview of source data.

If everything looks fine just click on OK button.

Once you click on OK button, you get back to Flat file Source Editor window.

Now go to Columns Tab, you can select required output columns also can change the output column names.

After that in Error Output Tab,  you can specified the behavior of the component in case of failure, you can specified any one option as follows:

1. • Ignore Failure: It ignores any failure while reading rows from source and the package will continue executing even any error occurred.
   • Redirect Row: It redirects the failed rows to other component which is connected with the error precedence constraints.
   • Fail component: It stops the execution of package in case of any failure.

We go with default selected value that is Fail component.




After that click on OK button.

Now flat file source is configured, also you can see a connection named FlatFlieConnection is created for Flat file that can be seen in Connection Managers window at the bottom of Data Flow Container.

Also note that, you can also create a connection directly in connection managers.

For this Just right click on Connection Managers window and select New flat file connection... as shown below.

And you will see it opens same Flat file Connection Manager Editor window, and follow same step as we did above.

Here we will not go with this as we have already created a flat file connection.

As we have taken a Flat file source component, now we will add a Destination component into Data flow container.

As our destination is SQL Server table so we will add a OLEDB Destination, for this go to Other Destinations in toolbox tab and expand it and then select OLEDB Destination and drag into Data Flow container.

After that we will add the path, means connect the Flat file source component which is the source of path to the OLEDB destination component which is the end of path.

For this select Flat file source component then drag the arrow and connect it to OLEDB destination as shown below.

A path connects two components in a data flow by connecting the output of one data flow component to the input of another component. It has a source and a destination

Once you connect Flat file Source component to OLEDB Destination component, it will look like as shown below.

Now double click on OLEDB destination component. You will see a OLEDB Destination Editor window opens.

Here you can select an existing connection or configure new Database connections.

To create a new connection click on New.. button.

After that a Configure OLEDB connection Manager window opens, click on New.. button.

Once you click on New button..  a connection Manager window opens.

Here you can specified a Server name, Authentication mode, also can select Database name.

Once you done with all these you can also verify connection by clicking on Test connection button.

After that just click on OK button.

Once you click on OK button, you can see a connection string is created as you specified.

Once you click on OK, you will get back to OLEDB connection Editor window.

In Connection Manger tab, you can select the table to which you want to load flat file data.

So in Data access mode option select as select table or view, and in Name of the table or the view option select table name that is tblcustomer.

Now in Mappings tab, you can map the appropriate input columns with output columns. You can see by default source file columns that is input columns and destination table column that is output columns are mapped automatically.

If any columns are not mapped correctly or missed then you can rectify or mapped them manually.

In our case you can see CustName input column is not mapped with CustomerName output column.

Lets mapped them, just select the CustName input column and drag it over to CustomerName output column.

You can also notice that for input column Education there is no mapping in destination columns, as we do not have Education column in our table. So we will not  load data for Education column in table.

Lets click on OK button, and you will see a Destination connection is created in Connection Manages window.

Now time to execute the package, for this right click on Package LoadFlatFileToSQLServer.dtsx and select Execute package from context menu.

Once you execute the package, you can see package run successfully and can be seen there are 9 rows successfully loaded to destination (SQL Server table) from source file (flat file).

Lets quickly check the SQL Server table tblCustomer.

And you can see data from flat file to table are loaded successfully.

Now you can click on link to get back the design mode, or to stop the debugging mode as shown below.

Also Read..

Create a new SSIS project.

Load Excel file data into SQL Server table using SSIS