Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Configure the Flat File Source in SQL Server Integration Services 2012 to read CSV files

MSSQLTips author Dallas Snider By:   |   Read Comments (9)   |   Related Tips: More > Integration Services Excel
Problem

I am new to SSIS and need to know how to read a comma-separated value (CSV) file into an SQL Server Integration Services (SSIS) 2012 package?  What are the steps that you need to follow?  Check out this tip to learn more.

Solution

For this solution, we will use the CSV file shown below. The file is named tip.csv and it has five columns and a header row.

The file is named tip.csv

The first step is to drag a Data Flow Task onto the package palette as shown below.

drag a Data Flow Task onto the package palette

Double click on the Data Flow Task or click on the Data Flow tab.

Next, drag a Flat File Source from the SSIS Toolbox onto the Data Flow palette as shown below.

drag a Flat File Source from the SSIS Toolbox

If you see a white X in the red circle, hover over the object to show the help message. The message below states that we need to set up a connection to the flat file.

hover over the object to show the help message

At this point there are two ways to initiate the setup of the flat file connection. The first way is to right click in the Connection Manager window and select "New Flat File Connection..." as shown below.

right click in the Connection Manager window

The second way is to double click on the Flat File Source to bring up the Flat File Source Editor and then click on "New...".

double click on the Flat File Source

Either of the above ways will initiate the Flat File Connection Manager Editor.

Flat File Connection Manager Editor

Complete the Flat File Connection Manager Editor:

  • For the "Connection manager name" on the "General" tab, I typically will use the actual file name or the type of file if there are multiple files of the same layout.
  • Just remember that what is entered for the name will be displayed in the Connection Manager window.
  • For the "File name", click on "Browse..." to navigate to the file or enter the file name manually. When using the browse feature, there are predefined filters for *.txt and *.csv files.
  • Because the Description column in our CSV file uses double quotes to qualify text strings, we must place a double quote in the "Text qualifier:" box.
  • For our file, the "Header row delimiter:" is the default of {CR}{LF} (carriage return/line feed).
  • "Header rows to skip:" also remains at the default of 0 because we only have one header row in this example and we make sure that "Column names in the first data row" is checked.

    Flat File Connection Manager Editor

    Now, we click on "Columns" tab to review the Columns page. We leave the "Row delimiter" set to the default of {CR}{LF} (carriage return/line feed). We leave the "Column delimiter" set to the default of Comma {,}. The preview section on this page allows us to see that SSIS is reading the file properly according to the format. This preview page will also allow us to make changes as necessary to accommodate the file's format.

    click on "Columns" to bring up the Columns page

    Next, we click on "Advanced" tab to review the Advanced page. By default, SSIS sets the "DataType" for each column to string [DT_STR], "OutputColumnWidth" to 50, and "TextQualified" to True. Setting each column to the proper data type as it is read in from its source eliminates the need to convert the data type downstream.

    By default, SSIS sets the "DataType" for each column to string [DT_STR], "OutputColumnWidth" to 50

    We change the data type of the ID column to a four-byte signed integer [DT_I4] and "TextQualified" to False.

    TextQualified

    We change the data type of the Amount column to currency [DT_CY] and "TextQualified" to False.

    MSSQLTips.com Sample Image

    We change the length of the Category column to 1 and "TextQualified" to False.

    change the length of the Category column

    We change the data type of the EntryDate column to database date [DT_DBDATE] and "TextQualified" to False.

    change the data type of the EntryDate column

    We leave the attributes of the Description column unchanged because we want the DataType to be string [DT_STR], the OutputColumnWidth to be 50, and TextQualified to be True.

    leave the attributes of the Description column unchanged

    Click on the "Preview" tab to review another preview page. This preview page differs from the one shown previously because the user has the capability to skip a specified number of rows to look into their file the way the SSIS Flat File Source views it.

    Click on OK when you are done.

    SSIS Flat File Source

    On the "Connection Manager" page of the Flat File Source Editor, make sure the newly created Flat File connection is selected.

    the Connection Manager page of the Flat File Source Editor

    On the "Columns" page of the Flat File Source Editor, the columns to be output from the Flat File Source are displayed.

    the Columns page of the Flat File Source Editor

    On the "Error Output" page of the Flat File Source Editor, we will leave the default values to have SSIS fail the Flat File Source component on truncation or an error. Click the OK button when you are finished.

    the Error Output page of the Flat File Source Editor

    When setup correctly, the SSIS window Data Flow window should appear as shown below.

    the SSIS window Data Flow window should appear as shown below
    Next Steps


  • Last Update: 4/3/2013


    About the author
    MSSQLTips author Dallas Snider
    Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

    View all my tips


    print tip Print  
    Become a paid author





    join MSSQLTips for free SQL Server tips     



    Learn more about SQL Server tools
    Post a comment or let the author know this tip helped you.

           All comments are reviewed, so stay on subject or we may delete your comment.

    *Name   *Email Notify for updates



           Note: your email address is not published. Required fields are marked with an asterisk (*)


    Get free SQL tips:

    *Enter Code refresh code     



    Friday, July 04, 2014 - 5:54:39 AM - Thomas Aimiuwu Read The Tip

    AE_ATTENDANCE_NUMBER AE_ATTENDANCE_CATEGORY
    7.32981E+11 1
    7.33131E+11 1
    7.38011E+11 1

    Above is a CSV file. The yellow highlighted column is supposeD to be a number but comes out like that. how do suppressed it in other for CSV to accept it like 732981111110?

    Thanks in advance


    Wednesday, March 05, 2014 - 5:33:44 AM - Saumyadeep Read The Tip

    @Shekhar:

    If you have below kind of flat file -

    TABBLE_1|Abc|1234|A12

    TABBLE_1|Def|6789|B45

    TABBLE_2|Xyz|1234|Z12|QWER|100

    TABBLE_2|Rts|6789|X45|ASDF|200

    Please convert it to -

    TABBLE_1|Abc|1234|A12||

    TABBLE_1|Def|6789|B45||

    TABBLE_2|Xyz|1234|Z12|QWER|100

    TABBLE_2|Rts|6789|X45|ASDF|200

    to maintain equal no of vertical pipes in each row.

    Now, check the below properties of flat file connection manager   -

    1) General - Header Row Delimiter - select vertical bar{|}

    2) Columns - Add same no of column(s) + 1 as you have Pipe(s) in each row. Here in this case, I am creating 6 columns for 5 pipes in each row.

    3) Advanced - Column Delimiter - select Vertical Bar{|}. In case of last column, by default, it changes to {CR}{LF}, so you may get preview like this - QWER|100.  

    4)Preview  - Check if you need to skip any row & to confirm the preview as expected.

    Voila! :)

     


    Thursday, February 20, 2014 - 8:57:16 AM - Gonzalo Read The Tip

    Hello, thanks for your tutorial.

    Question:

    How you can add a validation to check if the file exists in an specific folder before we run the data flow?

     

    Many thanks!

    Gonzalo.


    Tuesday, April 16, 2013 - 1:57:09 PM - Shekhar Read The Tip

    Hi Snider,

    I have a flat file like below

     

    TABBLE_1|Abc|1234|A12

    TABBLE_1|Def|6789|B45

    TABBLE_2|Xyz|1234|Z12|QWER|100

    TABBLE_2|Rts|6789|X45|ASDF|200

     

    In SSIS 2008 if i create Flat File Connection manager to this file and only mention 2 columns then getting preview like below with {|} as column delimiter and {LF} as Row Delimeter

    Column 0                 Column 1

    TABLE_1                   Abc|1234|A12

    TABLE_1                   Def|6789|B45

    TABLE_2                   Xyz|1234|Z12|QWER|100

    TABLE_2                   Rts|6789|X45|ASDF|200

     

    But when i preview same file in SSIS 2012 i am getting below preview

    but in SSIS 2012 i want column 1 values like below (output same as 2008 SSIS)

     

    Column 1
    Abc|1234|A12
    Def|6789|B45
    Xyz|1234|Z12|QWER|100
    Rts|6789|X45|ASDF|200

    How to achive this? Beacuse otherwise i can not upgrade packages from 2008 to 2012 without code change. I have a file that i use to populate into 5 different table with diff column numbers in each using conditional split on 'column 0'. Since column 1 is 'text ' datatype (>8000) in my case that i write into 5 files and then upload them into tables.

    I am not sure is this a new feature OR any bug, but these changes in SSIS 2012 are not working for me.

     

    Thanks,

    Shekhar


    Wednesday, April 10, 2013 - 1:18:58 PM - Shivanand Kamath Read The Tip

    Try using Suggest Type opiton in the advanced.


    Wednesday, April 10, 2013 - 12:01:47 PM - sree Read The Tip

    But the file which we got from vendor had 90+ Columns in it out of which most of them have 8000+ charecters in fields. Am looking for a way to set this DT_TEXT at a single shot for all the columns instead of changing it individually for each and every column...which is really annoying, especially dealing with more number of columns. Can this be done in SSIS?


    Wednesday, April 10, 2013 - 10:54:03 AM - Shivanand Kamath Read The Tip

    Set the field data type to DT_TEXT, in the advanced.


    Wednesday, April 10, 2013 - 10:10:39 AM - sree Read The Tip

    Hi Snider - How would you handle this if the field has more than 8000 Charecters? I remember, I had truncation issues in tha past...

    Thanks-Sree!


    Thursday, April 04, 2013 - 9:18:22 AM - Shivanand Kamath Read The Tip

    Thanks for the great article. I have an issue with using Text Qualifier double quote ".  I have a pipe delimited text file, with Text Qualifier ". I have some records in name field with double quote. for example the record would  1 |"Toys "R" Us"|"888-999-1234".

    When I try to import the file, it errors, in SSIS SQL 2012. But it works with no issue in SSIS SQL 2008 R2. Can you help ?

    Shiva

     

     




     
    Sponsor Information







    Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
    privacy | disclaimer | copyright | advertise | about
    authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
    Some names and products listed are the registered trademarks of their respective owners.