SQL SERVER – Import CSV File into Database Table Using SSIS

It is a very frequent request to upload CSV file to database or Import CSV file into a database. I have previously written article how one can do this using T-SQL over here SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server. This time, let us import CSV file into database tables using an SSIS package.

One of the request which I quite often encounter is how I can do the same using SSIS package and automate it. Today we will understand the same using images.

First of all, let us create the table where we want to insert the CSV file.

Here is the sample table which I am creating where I will import my CSV.

CREATE TABLE [EmployeeImported](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[ContactID] [int] NOT NULL,
[ManagerID] [int] NULL,
[Title] [varchar](100) NOT NULL,
[MaritalStatus] [char](1) NOT NULL,
[Gender] [char](1) NOT NULL,
[HireDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

Now let us open SQL Server Business Intelligence studio. (Path: Start >All Programs > Microsoft Sql Server 2008 R2 > SQL Server Business Intelligence Development Studio. Select Integration Services Project. Create a new project and save it.

Click on Control Flow and drag Data Flow Task to the right side pan.

Double click on the Control Flow task.

It will take you to Data Flow pan.

Drag Flat File Source from Toolbox to Data Flow task pan.

Configure New Connection by clicking New.

Select the Path of the file and specify Text Qualifier. For me the text qualifier is comma(,).

Click on the columns and adjust OutputColumnWidth – match it with the width of your original data. If you do not know leave it as default (at 50).

Click on on following screen.

Now Select OLE DB Destination from right side Toolbox and drag to below the Flat File Source.

Put them near to each other ad demonstrated below.

Connect Green Arrow to Flat File Source with OLE DB Destination.

Double click on the OLE DB Destination and connect to the database and table created earlier in the code.

After configuring connection the mapping needs to be adjusted as well.

Now on the mappings tab connect both the size. I have not connected very first column as it is identify column for me.

Clicking OK will bring me to following screen.

Now click on F5 and it will execute the package in debug mode.

Do let me know your opinion about this article.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.