SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



Import Data From Comma Seperated Values .csv Flat File Into SQL Server Database Table using SQL Server Integration Services SSIS


In this step by step guide I want to show you with an example how you can use the SQL Server 2008 Integration Services (SSIS) to import data from a flat file into a SQL Server 2008 database table.

irst of all, navigate to Microsoft SQL Server 2008 on the start menu and select sub-menu item SQL Server Business Intelligence Management Studio (BIDS). Then create a new SSIS (Integration Services) project.

SQL Server Integration Services project (SSIS)

In the Solution Explorer window of the new project, drill through to the SSIS Packages. Then create a new SSIS package by a right-click on the SSIS Packages item or rename the default ssis package Package.dtsx for a meaningful name for your integration services package. I gave the name ContactType_Import_Package.dtsx to the new created SSIS package.

I have placed a .csv (comma seperated values) file titled PersonContactTypesCSV.csv which has data that I want to import into a SQL Server 2008 database table named ContactTypes.

In order to finish the import task, first place a Data Flow Task from the Control Flow Items.

SQL Server Integration Services Data Flow task

When you double-click the Data Flow Task item, the BIDS will activate the Data Flow tab on the screen.
On the Toolbox window select Flat File Source among Data Flow Sources.

SSIS Flat File Source for data import task

Double click Flat File Source to open the Flat File Source Editor window.

configure flat file source for SQL Server data import task

Click on the Connection Manager and click the New... button in order to create a new flat file connection manager.
I named the new flat file connection as SampleCSVConnectionManager.

Select the source flat file by browsing it using the Browse... button.
In the Locale dropdown combobox select Turkish (Turkey) if not selected since I'm working from Turkey. And setting the Locate to Turkish (Turkey) will also set the Code page to 1254 (ANSI - Turkish). Note that the Unicode checkbox is also checked.
Since most of the samples are all in English, you may get errors while directly executing such SSIS samples.
To run those ssis sample packages successfully you should consider setting the correct code page for the source flat file.

I selected the Delimited format for the source .csv file. And the header row delimiter is {CR}{LF}
And since my sample flat file source has column names in the first row, I checked the column names in the first data row checkbox.

SSIS flat file connection manager

If you select an other tab other than the currently active tab "General" the OK button at the bottom of the Flat File Connection Manager Editor will be active. Click OK to finish the flat file connection manager for our sample.

Click OK to end configuring in the Flat File Source Editor screen.

The recently created connection manager will be displayed at the bottom of the Data Flow editor screen.

Now add a new OLE DB Destination control from the Data Flow Destinations group in the toolbox.

SSIS OLE DB destination component for data import to SQL Server

After the OLE DB Destination object is added to the design surface, set up connections from Flat File Source to OLE DB Destination.

set up connections between source and destinationimport data from csv file into SQL Server

We will create now a new OLE DB connection manager object for the OLE DB Destination control and point to the SQL Server 2008 instance and sql database we want to import the data to.

Integration Services OLE DB destination editor

Click New button.

SQL Server Integration Services OLE DB connection manager

Edit the connection manager object for the destination and test the connection configuration and verify whether the configuration is successfull or not.

OLE DB connection manager data access mode

Select the data access mode as "Table or view" and I selected the ContactTypes table as the target or the destination of the data flow task.

By the way the table definition is as follows:

CREATE TABLE ContactTypes
(
 ContactTypeID nvarchar(50),
 Name nvarchar(50),
 ModifiedDate nvarchar(50)
)

Click OK to finish the destination configuration. If OK is not activated click the Mappings tab then click OK button.

Now ready to run the package. Click Start Debugging button or press F5 to execute the package.

start debugging SSIS project

If the package is successful then a green view will be displayed like below.

successful SSIS package execution for data import task


Download Sample SSIS Project and Comma Seperated Flat File Source

You can download the sample .csv file from PersonContactTypesCSV.csv
And you can also download the sample project from Sample Integration Services Project.







SSIS-Integration Services

SSIS Tutorial List

SQL Server Articles

SQL Blog

Certification Exams Blog



Free Exam Vouchers










Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems