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

127 Comments. Leave new

  • I have a folder which has multiple files and their names are dates (e.g. 04-08-2014.CSV,04-09-2014.CSV) and in my SSIS package I want to load the most recent one by filename. How can I achieve it?

    I am currently using a hardcoded filename in my Connection Manager.

    My SO question is here:

    All help is greatly appreciated.

    Thanks.

    Reply
  • curious_programmer
    April 15, 2014 7:43 am

    Is it possible to get files from a FTP Server in stead of a local source? I really need some steps in how to do this.

    Reply
    • hi all,

      i am trying to load dynamically from csv files to sql database,
      i face this error can any one help me?

      Error: System.Exception: Failed to initialize ‘Write data into table’ metadata. Exception from HRESULT: 0xC02020E8 —> System.Runtime.InteropServices.COMException (0xC02020E8): Exception from HRESULT: 0xC02020E8
      at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
      at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicDbDestination.Setup()
      — End of inner exception stack trace —
      at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicDbDestination.Setup()
      at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.DynamicSetup._Setup(Boolean thunk)
      at CozyRoc.SqlServer.SSIS.DataFlowTaskPlus10.Execute(IDTSConnections100 connections, IDTSVariableDispenser100 variableDispenser, IDTSComponentEvents100 events, IDTSLogging100 log, Object transaction)

      Regards,
      venu

      Reply
  • thanks a lot , it works fine

    Reply
  • Hi all,

    I have a 7Years data in db. i need to stored each and every month data into FlatFile, and next flatfile will be generate automatically. By using SSIS. It will take all updates.

    Reply
  • Hi,
    In my case I would like to import 2 csv files with different names and the latest dropped file for each one of them, the validation and table to be insert it into are the same. How would I go about doing so? Thanks!

    Reply
  • Very helpful and images do help. Thank you for posting this SSIS tutorial.

    Reply
  • Hi Pinal,

    Keep updating for the ssis packages….

    Reply
  • Pinal,

    I feel I can really use your valuable help. I am trying to import this csv file into my SQL destination table. I have my flat file source component and OLEDB destination component for my SQL table. The error is follows:

    “SFH OPS File [43] Error: The column delimiter for column “Scheduler_Full_Name” was not found.

    Even though the delimiter is there, it says not found. Since this is the last column i have it set to CRLF.

    I can post the .dtsx and the flat file for further discussion.

    Thanks,
    Fahad Zia

    Reply
  • Hi Pinal,
    How can i load a single file with different data sets, like 3 different table structure which are separated by 1 blank row into 3 tables using SSIS. I know i have to use script task or component. Can you help on this

    Reply
  • Hi, I am trying to import data from csv file to SQL Server using SSIS, however in one column there are more that 20000 character in it, I unable to load it due to columns size 8000 character, even I have set nvarchar(max) it is not working, please let me know what is the way to import that column data. if anybody come across this scenario please let me know and appreciate it. Thanks

    Reply
  • What if we have embedded text qualifier as shown bellow. In BI development tool 2008 if we assign ” as text qualifier it works fine and gets (Mailing “Inactief”) as description correctly. However in Visual studio data tool 2012 and 2013 it does not work (Error: The column delimiter for column “Description” was not found.). The easiest solution seems to be setting TextQualified property in Advance Editor to False and then delete ” at the beginning and end of the text using a script or conversion task. Why it does not work in 2012 and 2013 as it does in 2008?

    ID~Description~Customer_ID
    1~”Mailing “Inactief””~3

    Reply
  • Rajesh Reddy N
    December 8, 2015 9:13 pm

    Thanks a ton Dave.. This helped me lot in my automation activities.

    Reply
  • This a good example, how would I modify it to have a variable as a file name? I have filenames which change as the date changes daily. Could I have a pre-process that builds the file name and then the flat file gets referenced and processed into my table? How would I do that?

    Also, once the project works how do I deploy it to our SQl server and get it scheduled?

    Reply
  • privateStaticID (@privatestaticid)
    April 19, 2016 3:42 pm

    When I need something critical about sql server, this blog always appears at google result.. Thank You, master Dave

    Reply
  • A great resource, i was able to create my first SSIS package using this resource, it has created a bit of a buzz in the team and are now looking to use SSIS for more flat file conversions/imports/exports

    Many Thanks

    Reply
  • It’s helpful ! Thanks

    Reply
  • When inserting data from a CSV file into my SQL database using a SSIS package, I lose data; 200 rows out of 2 million, do you have any idea why this is? I was wondering if this was a SQL database size issue but the data doesn’t look like it is cutting/truncating the last chunk of information out. Are there any limitations with using this approach on a large data set with over 2m rows?

    Reply
  • Hi

    I nedd to load the data into db and i am using a txt file.what should be the derived column expression should i used show that the data with negaive value at the end of the column data can be insert easily.

    secnario

    Suppose for a clomun ABC the value in text file is 12345+ and i need to load the data into db as 123.45+, using DT_NUMERIC() i can convet this a numeric fild, but i am not getting the + sign in the db.

    I guess a suitable derive column can help me in doing this.

    I have used a derived column that can insert signd at the beginning but i not able to do when at the end.

    Reply
  • It might be the case of stored proc,try to create two or more update flow(split the data or column into half ) in your package and then try.I was facing the same issue when i was loading the same amount of data.

    Reply
  • Kirti Kanojiya
    June 27, 2016 11:27 am

    Can you please while we import how can we get null values in table against blank values in csv or excel file?

    Reply

Leave a Reply

Menu