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.

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.

SQL SERVER - Import CSV File into Database Table Using SSIS import1

Click on Control Flow and drag Data Flow Task to the right side pan.
SQL SERVER - Import CSV File into Database Table Using SSIS import2

Double click on the Control Flow task.

SQL SERVER - Import CSV File into Database Table Using SSIS import3

It will take you to Data Flow pan.
SQL SERVER - Import CSV File into Database Table Using SSIS import4

Drag Flat File Source from Toolbox to Data Flow task pan.
SQL SERVER - Import CSV File into Database Table Using SSIS Import5

Configure New Connection by clicking New.
SQL SERVER - Import CSV File into Database Table Using SSIS import6

Select the Path of the file and specify Text Qualifier. For me the text qualifier is comma(,).
SQL SERVER - Import CSV File into Database Table Using SSIS import7

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).
SQL SERVER - Import CSV File into Database Table Using SSIS import8

Click on on following screen.

SQL SERVER - Import CSV File into Database Table Using SSIS import9

Now Select OLE DB Destination from right side Toolbox and drag to below the Flat File Source.
SQL SERVER - Import CSV File into Database Table Using SSIS import10

Put them near to each other ad demonstrated below.
SQL SERVER - Import CSV File into Database Table Using SSIS import11

Connect Green Arrow to Flat File Source with OLE DB Destination.
SQL SERVER - Import CSV File into Database Table Using SSIS import12

Double click on the OLE DB Destination and connect to the database and table created earlier in the code.
SQL SERVER - Import CSV File into Database Table Using SSIS import13

After configuring connection the mapping needs to be adjusted as well.
SQL SERVER - Import CSV File into Database Table Using SSIS import14

Now on the mappings tab connect both the size. I have not connected very first column as it is identify column for me.
SQL SERVER - Import CSV File into Database Table Using SSIS import15

Clicking OK will bring me to following screen.
SQL SERVER - Import CSV File into Database Table Using SSIS import16

Now click on F5 and it will execute the package in debug mode.
SQL SERVER - Import CSV File into Database Table Using SSIS import17

Do let me know your opinion about this article.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Resource Database ID – 32767
Next Post
SQL SERVER – Vote for My Session in SQL PASS

Related Posts

126 Comments. Leave new

  • while i am importing csv file through import wizard (from database to task option) . In csv file last column only have header there is no data in this column . some data imported wrongly while i am importing . what delimeter i need to use to sort this issue. please suggest.

    Reply
  • silentheartbreaker
    October 15, 2016 9:29 pm

    HI PInal,
    when we pull data from flat file we need to push the file into our DB Column . How we can do that.
    eg. Data-01.csv ,….. Data-10.csv

    Reply
  • Mark Cichecki
    April 17, 2017 8:14 pm

    Great article. I have a very vague idea about SSIS packages and tasks in general, yet achieved my goal with this walkthrough very quickly. Thank you very much.

    Reply
  • Hi Pinal Dave,

    Is it possible to move data from csv to mysql using SSIS?

    Thanks

    Reply
  • While connecting to OLE DB source the error on my Remote machine tells “Components OLE DB Source has no inputs, or all of its inputs are already connected to other outputs. You may be able to edit the components to add new inputs to it.”
    What does this means? How can I get it resolved?

    Reply
  • SANDEEP REDDY TALLA
    September 28, 2017 11:47 am

    Hi Pinal,
    Can we import a CSV/Xlsx file with 7000+ columns using SSIS job?
    I am getting weird errors while trying to do this.

    Reply
  • Excellent Dave!

    Reply
  • I need to load flat files which have dynamic columns (columns are not fixed they change sometimes). How do I create a stored procedure which loads these flat files into sql table. These files are nearly 100 and seat in one location.

    Sample of the flat file:

    OUTPUT_2
    NUMLINES, 4
    VARIABLE_TYPES,I,N,I,N,N,T,N,T,Ddd/mm/yyyy
    !,SPCODE,ADD_DEFER_M,ADD_TERM_M,AGE_AT_ENTRY
    *,1,0,0,21,0,”SP Cap Bond”,0,”SP Cap Bond”,19740401,
    *,1,0,0,17,0,”END Linked”,0,”END Linked”,19950901,
    *,1,0,0,36,0,”END Linked”,0,”END Linked”,19961201

    Reply
  • CSV file contain Name which contain(R’amesh,Sureshe’s,Raj’esh) so I want to ignore the ‘(single quotes) in source level without using transformation,Could you please let me know how to reslove it

    Thanks,
    Vivek.

    Reply
  • Very helpful. Very clear! Thanks!

    Reply
  • may god bless you…awesome article..really helpful

    Reply

Leave a Reply

Menu