Bad data in input file.

  • I wrote a package that is rather straight forward. It reads a flat file and places it in a table. I am having a problem in that some of data is formated bad. The file is comma delimited with Double qoutes (") around texts. Well one of the texts has two sets of double qoutes in it. I have tried redirecting error rows to a flat file and this would be acceptable. I continue to get an error the packge fails. Any ideas of how to get the package to reject the offending row and move on?

    The error message is

    [Input File [1]] Error: The column delimiter for column ""NABP"" was not found.

    and

    [Input File [1]] Error: An error occurred while processing file "C:\MSSQL\Packages\Input\ImportBillingData\RawFolder\Input Monthly Hospice" on data row 1636.

  • I shall refer you to the following topic that discusses the same problem, but it unfortunately doesn't offer any direct solutions:

    http://www.sqlservercentral.com/Forums/Topic638571-148-1.aspx

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I have used a script object to clean up input files before the import.

    read the source file, evaluate the line, then output to a new file.

    or

    ignore the "" qualifier on import.

    use derived columns to strip out the double quotes using the replace function.

    Usually I can push back on the provider of the data to clean it up before sending it to me

  • nheller (3/9/2011)


    ignore the "" qualifier on import.

    use derived columns to strip out the double quotes using the replace function.

    What if there is a string with a comma in it? Won't that mess up your columns?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This appears to be a problem with SSIS not using escape characters as other parts of the Windows Universe. Any one agree?

  • SW_Lindsay (3/10/2011)


    This appears to be a problem with SSIS not using escape characters as other parts of the Windows Universe. Any one agree?

    Or a problem with people not choosing a decent delimiter 😉

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply