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.

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.

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

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

Related Posts

127 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
  • Apoorv Deshmukh
    September 12, 2017 1:57 am

    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
  • Vivekanand Kola
    December 12, 2017 11:41 am

    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
  • Steven Riddle
    February 5, 2019 1:23 pm

    Very helpful. Very clear! Thanks!

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

    Reply
  • how we can use Semicolon in text qualifier if we have Semicolon as a delimited.
    Please help

    Reply

Leave a Reply

Menu
Exit mobile version