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 )

(追記) (追記ここまで)

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 ReplyCancel reply

[フレーム]

Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 22 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.

Pinal has authored 14 SQL Server database books and 97 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,800 database tech articles on his blog at https://blog.sqlauthority.com.

Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at pinal@sqlauthority.com.

Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).

Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.

Comprehensive Database Performance Health Check

Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.

Once you learn my business secrets, you will fix the majority of problems in the future.

SQL Server Performance Tuning Practical Workshop

Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.

Essentially I share my business secrets to optimize SQL Server performance.

AltStyle によって変換されたページ (->オリジナル) /