SQL SERVER – Import CSV File into Database Table Using SSIS

It is very frequent request to upload CSV file to database or Import CSV file into 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

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.

import1 SQL SERVER   Import CSV File into Database Table Using SSIS

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

Double click on the Control Flow task.

 SQL SERVER   Import CSV File into Database Table Using SSIS

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

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

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

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

Click on Columns and adjust OutputColumnWidth – match it with width of your original data. If you do not know leave it as default (at 50).
import8 SQL SERVER   Import CSV File into Database Table Using SSIS

Click on on following screen.

import9 SQL SERVER   Import CSV File into Database Table Using SSIS

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

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

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

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

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

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

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

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

Do let me know your opinion about this article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

111 thoughts on “SQL SERVER – Import CSV File into Database Table Using SSIS

  1. Hi, pinal!

    Have you ever used a custom delimiter in CSV files?
    I used bulk load task and I couldn’t use delimiter for example ASCII(7)… SSIS have failed on compilation…

    Best regards,


    • Eugene,

      I’ve only used commas, pipes and tabs as main delimiters myself, but you should be able to split the columns up using the derived column transformation.

      I find that derived column transformation to be a bit of a pain to use though so what i normally do is just load the data into a scratch table with a varchar (or nvarchar) field that’s large enough to fit the entire row, or section that contains the custom delimiter, then write a t-sql procedure to split it out into columns and load to the final table.



  2. Pinal,

    Nice primer on SSIS. I should point out though that you mention your text qualifier is a comma. This is not the case, and generally wont be the case for a CSV file.

    In setting up the flat file connection there is a “columns” section where you specify the column delimiter: http://cl.ly/6fzm

    The text qualifier you mention is used when you have a text field, like notes and such, that might contain commas and the qualifier prevents the import process from dividing that field up every time it sees a comma. Often if you’re exporting from Access this is a double quote (“).



  3. Good article, i have had better luck using CSV files to import data as compared to excel files. In excel i do run into the issue of coulmn having unicode types and i have had to use the Data Conversion task within the data flow to covert unicode to nonunicode and then import the data into sql server.


  4. Hi Pinal Dave,

    I’m a young DBA just getting started and I LOVE articles like these. Thank you so much for taking the time to share your knowledge with us! As I mature I hope to someday “give back” in a similar fashion.




  5. Hi Pinal,

    Lots of People don’t understand the MS BI (SSIS/SSAS/SSRS)

    It’s been nice article for new SSIS people.
    How SSIS Works.



  6. Hi Pinal,

    Your article is good for beginning.Here are some points:
    When you are using CSV file or text file and your file have Column Names in first Data row then check the check box says “Column Names in First Data Row”, It will automatically takes Column Name as the CSV or Text file has. If not checked or your file don’t have column names then you have to follow the process which you defined in your BLOG.
    I would like to ask one question what is the use of “,” Comma in Text Qualifier in your article.


      • Sorry madhivan, but that is incorrect, the column delimiter is the comma, but the text qualifier is useful for splitting out text strings that contain the delimiter. for example:


        the text qualifier ” means that the string aae,sdef would be treated as one column


      • Hi Madhivanan,

        As Barny says,Text Qualifier is used for a different purpose. Suppose you CSV has a column Address and in this You have a address like “Street no.5,Delhi,1″. Now when you use Comma ad a Column delimiter then SSIS will delimit Address into three Columns, but It must be read into a single column.
        For this we take ” in Text Qualifier and SSIS will read Address (Street no.5,Delhi,1) into a single column.


  7. Hi Pinal,

    can you explain me how to set the properties for TEXT Qualifier and Format.

    could you plz explain me with an example.

    in fomat we have—->Delimited,fixed width, ragged right.

    what are the cases that we go for above three.

    Thanks in advance,


  8. Pingback: SQL SERVER – Running SSIS Package From Command Line Journey to SQLAuthority

  9. Pingback: SQL SERVER – Running SSIS Package in Scheduled Job Journey to SQLAuthority

  10. i have once csv file like this

    name sal
    ____ ___

    sarath 20000
    raja 30000
    sara,d 12121

    but not getting proper way of result


    • Hi Sanjay…

      No matter how many CSV file you have you can import them by using SSIS provided the Structure of CSVs should be same.
      Example: CSV1
      You just have to use the ForEachLoop Container and in Collection use “Foreach File Enumerator” specify the folder where all your CSV files are and in Files use *.CSV and Retrieve file name as Fully Qualified…
      And claick OK after that Drag A Data Flow Task inside the For Each Loop Container and use the CSVs as you wish..


  11. Hi Pinal,

    Can I create/extract master detail tables and data with SSIS project?, I am new in SSIS, so will you please explain How can I extract data from csv in master detail table?

    EmpId Emp_name designation
    1 abc1 IT Manager
    2 abc2 Sr. Software Developer
    3 abc3 Sr. Software Developer
    4 abc4 Sr. Software Developer
    5 abc5 Software Developer
    6 abc6 Software Developer
    7 abc7 Web Developer
    8 abc8 Web Developer
    9 abc9 Web Developer

    I want to create two table from single table like

    EmpId Emp_name designationId
    1 abc1 1
    2 abc2 2
    3 abc3 2
    4 abc4 2
    5 abc5 3
    6 abc6 3
    7 abc7 4
    8 abc8 4
    9 abc9 4

    and another one is

    DesignationId Designation
    1 IT Manager
    2 Sr. Software Developer
    3 Software Developer
    4 Web Developer


    • Hi Harish,

      Yes, you can create the master detail table data with SSIS. In your given data you can achieve this by follow the below steps:
      In SQL: Make Three tables
      1.For store Designation and DesignationID calls Designation.
      2.For Store EmpId,Emp_name and designationId Calls Employees.
      3.Staging table for storing the data as it is in the example data given by you.
      CREATE TABLE Stg_Employee
      EmpID INT,
      Emp_Name VARCHAR(100),
      Designation VARCHAR(100)

      IN SSIS:
      1.First Create a SSIS Package.
      2. Drag a DataFlow Task on Control Flow Surface and Double click on this and take a FlatFile Source and OLEDB Destination.
      3. Configure to both FlatFile Source and OLEDB Destination. FlatFile Soure to your main data and OLEDB Destination to the Staging table that we have created in our SQL part.
      3. Map the appropriate Column names.
      4. Drag Another DataFlow Task on Control Flow add the Precedence Constraint of previous DataFlow Task to this one and
      Double Click on it.
      5. Take a OLEDB Source and OLEDB Destination and in OLEDB Source choose SQL Command as Data Access Mode.
      6. Write the query which should be like
      SELECT DISTINCT Designation,DENSE_RANK()OVER(ORDER BY Designation) AS DesignationId FROM Stg_Employee
      7. Map the columns of this query to the table which you made for store Designation and DesignationID.(Suppose the name of this table is Designation)
      8. This is the trickiest part of this process for this again a DataFlow Task on Control Flow and join the Previous Precedence Constraint with it.
      9. Double click on it and take a OLEDB Source LookUP Transformation and OLEDB Destination.
      10.Take Stg_Employee as a source and take Designation table in LookUP transformation and match the Designation of stg_Employees
      to Designation of Designation table and on the bases of match records take DesignationID.
      11. In OLEDB Destination take Employees Table as A Source. Now you have Emp_ID,Emp_Name which will come from stg_Employees table and DesignationID which will come from Designation Table.
      Click OK and Run The SSIS packge…
      Note: Since we are not dealing the Errors in this process this may happen that due to some mismatch records in LookUP transformation the Package Fail.
      And Please let me know if you have any more queries related to this.


  12. Hi Pinal, This is a good introduction on how to import flat files into tables. In my case, I need to import three files having different number of data elements into three tables having the same corresponding number of columns. My questions are:
    1) Which is the better and recommended method to use? – Using Bulk Insert command in a stored procedure OR using SSIS?
    2) The file location, database server and database name are different for development, system integration, user testing and production environments. If I want to use SSIS to do the file import, how can I create one SSIS package that will dynamically get the file location, server name and db name from some configuration file and execute in the appropriate environment?


    • Hi Mallikarjun,

      In my opinion, You can better achieve this by using SSIS.
      For making the SSIS connections dynamic use SSIS configuration.
      Just make 4 configuration files for each and every environment ands use them in your SSIS package for this do right click on control flow and enable Package Configuration and make a configuration file.
      For more information do some googling on configuration file in SSIS.


  13. Pinal, two more questions after my previous email.
    1) How do we truncate table before import from the file? I did not find any option in OLE DB connection setup to do that.
    2) Can we import of 3 files having different number of fields in the same SSIS package?


    • Hi Mallikarjun,

      1. Use Execute SQL Task on Control Flow of SSIS and make the configure it with OLEDB connection and in query use the TRUNCATE table statement.
      2. For this you can use three Data Flow Tasks on Control Flow Tab and when you do double click on Data Flow task choose your Source,Transformation and Destination to configure them
      Le me know if you have any more question on this topic.


  14. @Ramdas
    Hi I believe u said that u used data conversion tool.

    I am using the same tool but still have the error saying cant convert from unicode to non-unicode where my source data type is DT-STR

    please reply me with a solution


  15. HI Pinal

    I am a big fan of yours and really value your articles. I use DTS to import flat files for our company and using temp tables to hold the data before sending it via standard interface that company has written to filter the data. My issue is here that you have not mentioned somthing to filter data such as trasformations used in ssis. I very much like to see thease trnsformation conditional split, data conversion etc. in action. Hope I am not asking a lot.


    Nilanka Sooriyabandara


  16. Hello, I am very new in SSIS,
    I want to import many txt files into sql many tables,
    the table name is the same with the txt file name,
    like this: file P.txt —- table P

    Can I do this in one package?

    How to do this?


  17. Hi Pinal,

    I am not able to find the solution for my problem.

    here is my question :-

    i have a data flow task to copy a csv file into a table. the file has 84 columns(8145 rows). when i do run my dft it runs fine(all green) .. I can see the data in preview and in data viewer too. I can see the data in data flow also like 1113 rows, 3000 rows …. and finally 8145 rows.

    but at end it says

    [DTS.Pipeline] Information: “component “OLE DB Destination” (9)” wrote 0 rows.

    I have few columns with comma separated value in my CSV file. tht might be creating problems.


    • Hi
      I would also like to know this as I am doing something similar.
      I have an SSIS package set up which goes to an FTP site and downloads a csv file for me then brings it into SQL.
      However each day I need to go in and change the filename from: filename_011020011_02102011 to filename_02102011_03102011
      When in fact all I want it to do is pick up the lastest file each day.


      • Hi Nick,

        Have you find a way to figure this out. Actually for doing so .. we need to use Script task component and has to write the C# or VB script inside the Script task component. I would like to have the script. Could you please send me the script.


  18. Hi Pinal,

    I am new to SSIS.

    Here are my questions:

    How can we ensure SSIS picks up the latest CSV File from the folder?
    How would SSIS Package be scheduled?


    • Hi Saranya,
      Am also looking for the solution for the same problem (both questions). Kindly reply if you have any idea to do the same. Thanks in advance.


  19. Hi Pinal

    I have a .csv file which needs to be inserted in a column with datatype varchar(max). I am really new to import and export. how can i do this? Any type of help would be really appreciated.


  20. Hello Pinal Dave,

    I have clearly understood the process of how we can upload a CSV file to a table in a database (sql server 2008 ) using SSIS package.

    Although I want to execute the package from stored procedures.

    I got some code which allows me to execute the package which involve upload from excel to database table through Stored Procedure.(which is working)

    and using the same code I am not able execute package which involve .csv to database table upload.

    –Step -1
    USE master
    EXEC sp_configure ‘show advanced options’, 1
    EXEC sp_configure ‘xp_cmdshell’, 1
    EXEC sp_configure ‘show advanced options’, 0

    — Step 2

    DECLARE @returncode int

    EXEC @returncode = xp_cmdshell ‘dtexec /f “c:\your path\package.dtsx”‘

    Pleas reply me ASAP.
    Thanking You.


  21. Hi Pinal Dave,

    Like to read different posts of this website. It is a very useful website for getting solutions to various problems.

    But i am having a problem with this particular .csv file

    I have a .csv file which has a data in first row and then the headers for columns below it. Example
    Company Name = “ABC DEF”
    Then comes all the columns with headers. So if i want to parse this company name to a column in same table where i am going to pass all the remaining columns from this .csv file. How do i do that using SSIS?

    Can you help me on this?


  22. Hi Pinal,

    I have the flat file source like this:

    “xyz@yahoo.com”,”test”,”test2″,”814 42nd st”,”NULL”,”beach”,”FL”,”33407″,”5618424410″,”″,”xyz.COM”,”2010-10-05 10:36:00″
    “yyyyyy@hotmail.com”,”john”,”test”,”7243 thoreau cir”,”NULL”,”atlanta”,”GA”,”30349″,””,”″,”newsite.COM”,”2010-10-05 09:33:00″

    When we insert the data to the table it will be added with double quotes (“), please give your suggestion how to resolve it.




  23. Hi Pinal

    Thanks for this solution. My client however requires that I write a procedure to import data with any number of fields. The Import doesnt care how it is formed. This requires that the table gets created from the data rather than pre-empted by the programmer. This is a problem if a table is required first. How can i implement this? A dynamic way you could say.



  24. Hello Everyone!

    First of all, I’m impressed from the level of the article. Simply – a genious at work!

    My question:
    I’ve loaded already 350 CSV files into SQL server 2008 using the SSIS package (with loop container), but now I have to be able to read every day the current log of the day, formatted CSV, but updating every minute.
    What I need is a way to connect to the ‘live’ CSV file and every new line written to it – to get it into SQL server 2008 (using some convertions, etc., like I did in SSIS).

    Is there a way to do this (with SSIS or any other way)? Or I’ll just have to wait and get the full file after midnight (when the log file is rotated)?

    Many many thanks for a good answer!


    • I’m still looking for a solution to my problem.

      I see many questions after mine got an answer, and – for some reason – My question left alone.



      • Are you open to using PowerShell to parse your CSV to fetch new records? Are you familiar with PowerShell? In SQL server 2008 and greater you can trigger PwerShell scripts as a step within a SSA job and follow it up on the next step with an SSIS job to load the new records.


        • Marcus (and others),

          I’ll do whatever needed to be able to read those live files.

          I don’t know about PowerShell yet, but if you’ll tell me that’s the correct way to do it – I’ll do it.

          Looking forward hearing a solution,


    • Dear everybody.

      I’m still waiting for a solution to load current data from live dynamic CSV file (mail server log file).

      How can I make the connection to it, and read it line by line whenever a line is added?



  25. HI Pinal

    The article is very helpful.However I have another challenge. If the data already entered in SQL database comes again in the flat or csv file, the sql database will get duplicate enteris. I can make the coulmn as primary, however that will generate the error and the pkg will fail. Is there a process i can check in the sql database before importing into sql. Kindly suggest how can i achieve this through SSIS.



  26. Hi,

    Thanks for the nice article. I had some developers create a SQL Server 2008 based application for me. However, somewhere in the transformation of the data, some large numbers get rounded. They have looked and looked but can’t find where the rounding is occuring. Has anyone else experienced this using SSIS?



  27. Hi,
    Thanks for these article. But i want another article i have 200 flat files .
    I want to create tables in SQL SERVER database with each FLAT file with different tables means(200 tables ) using SSIS please respond me my
    EMAIL ID IS : [email removed]


  28. HI! Dave,

    If the flat files contains datetime column with dd/mm/yyyy hh:mm:ss mis format how can i set this to mm/dd/yyyy format and import, i am always having problems with the date formats, it always swaps the date to month and the months to date while importing. how does this been corrected in ssis as per your above steps in sql 2008


  29. i have 5 csv file in a folder. that i want to transfer to sql server table. i did that but in sql server table there is another field called csvfilename, means once i will put the dataflow task in the foor loop container, the data will process and simultaneously the name of the csv file will go to that table.like wise 5 csv will go to sql table with the CSV filename .

    pinal, or anyone else can you help me on this. its very urgent.

    what i am able to do-
    i am able to move the content from all the csv file to one sql server table

    what i am not able to do-
    the csv file name is not coming in sql server table. everywhere it is coming as “null”, because the default value is null. i am not able to pass the file name from foor loop container.


  30. I am following this simple example, and it is not working. I setup a Data Flow Task on the Control Flow tab, and a Flat File Source and OLE DB Destination on the Data Flow tab. The problem is that I cannot find my tables in SQL, which are clearly there. I am anle to use a query to define the destination…

    SELECT TOP (1000) keynoun, abb
    FROM EngineeringDocCon.dbo.tblKeyNoun

    …and I am able to map the columns

    I’m getting Valucadion errors about an inability to convert between unicode and non-unicode string types. Both destination fileds are of type nchar.

    A little discouraged that the examples are not working, as I believe I am doing something really simple…

    Thank you in advance.


  31. Hi Pinalbhai,
    I am working as BI Developer today I came up with the new requirements that I need to create dynamic excel file with everyday date (like ‘Reports_02_09_2012.xls’) on our network drive. Pl. can you help me how to create a dynamic excel file? the data source is SQLServer Table and destination is .xls file. PLssssssssss


  32. Hi Pinal,

    I did the same thing in creating an SSIS package for importing data from a flat file. But this had the confusing and hilarious effect of interpreting dates inconsistently. Any dates where the day was greater than the 12th of the month would be correctly interpreted as dd/mm/yyyy but anything less than that would be backwards.

    So 01/21/2011 would be correctly read as the 21st of Jan 2011.

    But 01/06/2008 would happily be interpreted as 1st of June 2011.


  33. Thanks Deepak for such a good solution. One thing i just want to ask that how we can schedule data loading from a particular text file.


  34. Hello Sir,
    I m Raghavendra…Can you tell me how to dump 4 files of different strucutured data to 9 tables..???

    I hope that you will reply soon..


  35. I am getting package validation error message box stating Error at Data Flow Task [SSIS.Pipeline]: input column “” (239) has lineage ID 203 that was not previously used in the Data Flow Task.
    There are few more exception messages being written like these! How to get over it?


  36. Hi der,

    I am transformning txt n csv files into database through SQL plus/loader.

    I have file of transections where there are more than 1 columns holds negactve values. Now the problem is that in txt file a negative sign occurs on the right side of the number. .So I have multiply certain colums contents with (-1) it works for 1 colum, but what can I do with more than 1 colums containing negatives at the end.


    |Jusq. 0 |Jusq. 30 |Jusq. 60 |Jusq. 90 |Jusq. 180 |

    |760.288 – | 653.612 | 145.456- | 117.814- | 170.807 |
    |760.288 | 653.612-| 145.456 | 117.814- | 170.807- |
    |760.288 – | 653.612 | 145.456 -| 117.814- | 170.807- |
    |760.288- | 653.612-| 145.456 | 117.814 | 170.807 |
    |760.288 | 653.612-| 145.456 | 117.814- | 170.807- |


  37. Hello Pinal,

    I need your help in Import 4GB .CSV file with 340 colums.

    Its not allow me to import it



  38. Pingback: SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video « SQL Server Journey with SQL Authority

  39. Hi Pinal,
    I upload csv file but it execute operating system error… What can i do for this error. Any updation for OS. I am using XP sp1 .



  40. Hi Everybody,

    Could you please suggest me how can I insert data from one flat file to different tables in sql server Database using SSIS or SQL code?


    I have a data like this format in flat file. I need to insert it into different 3 tables in sql server 2008 , named TB1, TB2 and TB3. here in raw file : fieldterminator = ‘*’ and rowterminator = ‘~’
    1st and 4th rows records should be inserted in TB1 TABLE.
    2nd and 5th rows records should be inserted in TB2 table
    and 3rd row’s records should be inserted in TB3 table.



  41. How can I load the latest csv file in a folder into the database table.I have 5 csv files with different modified times….I want to load the latest file.



  42. Just got a doubt, if the file name changes everyday then what would be the mechanism. As in this case the file name is CSVfile.txt. Lets put an example. The file name would be CSVfile_20121008.txt (where 20121008 is today’s date). And this value will be changing regularly and the file would be placed in the same location(so the old file is deleted and only the new file as of current date is present). Is there any process for the same with keeping with the approach same of importing the data.


  43. Pingback: SQL SERVER – Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video « SQL Server Journey with SQL Authority

  44. Hi Pinal,
    I’ve worked with and it worked great for a while. Now I have an issue. Somewhere in the csv file there is something bad. The file has about 200k lines in it, so manually searching for the error is not possible (although I’ve done that anyway). I seem to have no issues when reading the file into Excel or Access (for trying to find the error). Do you have an addition to the solution show, that enables me to exactly pinpoint the line that is causing the error?. By the way, I have tried to redirect the line containing the error to an alternative table. My (probably crude) attempt was not successfull


  45. Great article! I am just learning how to use SSIS (today) and your article is very helpful. I am wondering if there is a way to load a CSV file and change all blank strings to NULL across all columns without writing it out column by column?


  46. Hi Pinal, Great article to begin with, i am working in SSIS and am not sure on backward compatibility stuff supported in 2008, i have made a package with 97-2003 as source but if the version of excel is 3.0/4.0, would the behaviour of ssis change, do we need to explicitly handle this and also if we wanna make this .xlsx compatible what would be a good practice?
    Jinesh Rajesh Parekh


  47. Pingback: SQL SERVER – Weekly Series – Memory Lane – #029 | SQL Server Journey with SQL Authority

  48. On column6, wat delimiter did u use? I have a csv file, all separated by commas. But mine seems to have A problem with the last column. Kept saying row and column cannot have same delimiter. I’ll greatly appreciate it if I can be sent the source code. Thanks


  49. Hello,

    How to import csv file from gzip file using gzip stream? I would not like extract import files because it’s very very large file > 20 GB and wouldn’t like spent time for extract and save csv file to disk.


  50. 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.


    • 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)



  51. 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.


  52. 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!


  53. 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.

    Fahad Zia


  54. 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


  55. 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


  56. 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?

    1~”Mailing “Inactief””~3


  57. 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?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s