SSIS is not throwing error. dataflow task accepting the data into target table even source file has different column name

  • In SSIS i have dataflow task which transforming data from flatfile to SQL table. But sometime flatfile is coming with invalid column name. Still transformation getting completed with no errors as i defined data type nvarchar(4000) for all columns. Is there anyway ssis can recognize source flat file column names and throw error messageif column name not matches.

    What's the bestway to catch these errors when column name mismatches from source flatfile?

  • Possible options

     - define the file as not having headers on it, load into a staging table with all columns defined similar to what you did, and then parse the header row to see if all columns are as desired. If not fail the package, otherwise allow remaining process to proceed ignoring the header row.
    - add a script task to read (direct file io) the first line of the file prior to the dataflow  - if headers are invalid fail the package and do not process the load.

  • frederico_fonseca - Wednesday, July 18, 2018 1:13 AM

    Possible options

     - define the file as not having headers on it, load into a staging table with all columns defined similar to what you did, and then parse the header row to see if all columns are as desired. If not fail the package, otherwise allow remaining process to proceed ignoring the header row.
    - add a script task to read (direct file io) the first line of the file prior to the dataflow  - if headers are invalid fail the package and do not process the load.

    Thank you. Please advise the steps for "add a script task to read (direct file io) the first line of the file prior to the dataflow - if headers are invalid fail the package and do not process the load. "

Viewing 3 posts - 1 through 2 (of 2 total)

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