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.
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.
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.
Double click Flat File Source to open the Flat File Source Editor window.
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.
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.
After the OLE DB Destination object is added to the design surface, set up connections from Flat File Source to OLE DB Destination.
 
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.
Click New button.
Edit the connection manager object for the destination and test the connection configuration and verify whether the configuration is successfull or not.
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.
If the package is successful then a green view will be displayed like below.
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.
|