none
import .csv file.

    Question

  • Hi,
    In ssis package I have a flat file connection which connects to .csv file.
    The textqualifier is " i.e. double quotes.
    Each .csv file has fields with data.
    At the end of each .csv file, there is a long text i.e. "This is the end of the data..."

    In the process where I place the data from the file connection to the database table, how can I ignore the last text which is i.e. "This is the end of the data..."
    This is because this text appears in the first column of the .csv and therefore the ssis complains that the text is too long, etc because it tries to put it into the first column in the database table.

    Question:
    How can I ignore the text which appears at the end of the .csv files.
    Note that this text is the same at the end of each .csv file.
    Thanks

    Wednesday, May 02, 2012 3:54 PM

Answers

  • There is no "skip footer lines" setting on a flat file connection.

    You can pre-process the file using a Script Task or another Data Flow Task.  (Using the Data Flow Task, connect to the file, but set it up to only have one column of a very long string, and use a conditional split to filter out the "This is the end of the data..." line.)

    Or you can route errors from the Flat File Source through a Conditional Split to determine if it's the error you expect (in which case you filter it out), if not, report it and/or fail the task.


    Todd McDermid's Blog Talk to me now on

    • Marked as answer by arkiboys Wednesday, May 02, 2012 10:41 PM
    Wednesday, May 02, 2012 5:12 PM
    Moderator

All replies

  • There is no "skip footer lines" setting on a flat file connection.

    You can pre-process the file using a Script Task or another Data Flow Task.  (Using the Data Flow Task, connect to the file, but set it up to only have one column of a very long string, and use a conditional split to filter out the "This is the end of the data..." line.)

    Or you can route errors from the Flat File Source through a Conditional Split to determine if it's the error you expect (in which case you filter it out), if not, report it and/or fail the task.


    Todd McDermid's Blog Talk to me now on

    • Marked as answer by arkiboys Wednesday, May 02, 2012 10:41 PM
    Wednesday, May 02, 2012 5:12 PM
    Moderator
  • There is no "skip footer lines" setting on a flat file connection.

    You can pre-process the file using a Script Task or another Data Flow Task.  (Using the Data Flow Task, connect to the file, but set it up to only have one column of a very long string, and use a conditional split to filter out the "This is the end of the data..." line.)

    Or you can route errors from the Flat File Source through a Conditional Split to determine if it's the error you expect (in which case you filter it out), if not, report it and/or fail the task.


    Todd McDermid's Blog Talk to me now on

    Thank you
    • Edited by arkiboys Wednesday, May 02, 2012 10:41 PM
    Wednesday, May 02, 2012 10:03 PM
  • When you route errors to the error output, you can examine the contents of the error, and count the number of errors.

    If your source file structure dictates that you'll always get one row in error, and it's always caused by the first column, then you can set up the data flow to report the error only if there's more than one row.  When you route errors to the error output, the task doesn't fail, so you'll have to set up some other failure detection mechanism - like routing the error output into a rowcount, which sets a variable, which causes a conditional precedence constraint to send an email or something like that.


    Todd McDermid's Blog Talk to me now on

    Wednesday, May 02, 2012 10:41 PM
    Moderator