Import CSV File into Database Table Using SSIS

How to Import CSV File into Database Table Using SSIS?

Hello all today i have share my knowledge with you how to import Data into sql server using SSIS package from .csv file.

Create the table where we want to insert the CSV file.

Where i will import my CSV.

1 2 3 4 5 6

CREATE TABLE [dbo].[CSVFile](        [col1] [varchar](50) NULL,        [col2] [varchar](50) NULL,        [col3] [varchar](50) NULL ) ON [PRIMARY] GO

Note:-I have try with very small data you people can change according to your requirement.

Open SQL Server Business Intelligence studio.Create a new project and save it.(I am using SSDT tools 2010)

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

 Double click on the Control Flow task.

In Execute SQL Task I am delete data from table.

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 (“) Where we have Col2 has value 23,000 which may consider Col2 = 23 and Col3 = 000.

Trick is to wrap value by double quote - "23,000" and use Text Qualifier = “(In Flat File Connection manager)

Click on Columns and adjust Output Column Width – match it with width of your original data. If you do not know leave it as default (at 50).

Click 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 demonstrated below.

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

 Double click on 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 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.

 The below is my final output.

THANK YOU SEE YOU AGAIN (: