none
Pipeline Delimeter issue in SSIS flatfile.

    Question

  • I have flat file with pipeline delimeter with 9 columns, each columns having diffrent data lenth. i am having issue with one on of the column Address.

    User enter the value with PIPELINE symbol in his address, so that my developed SSIS packages got messed up bcoz my package is consider user enterted additional pipeline is one column. so my package is got failed. please help me, how to sort this issue or i need to consider this record as bad data.

    Monday, August 11, 2014 3:29 PM

Answers

  • If the file has no text qualifier for this field then consider replacing the delimiter as I did in my blog:

    http://geekswithblogs.net/Compudicted/archive/2011/09/19/ssis-how-to-remove-occasional-quotes-and-replace-the-column.aspx


    Arthur My Blog

    Monday, August 11, 2014 3:36 PM
    Moderator
  • I would say you would have to consider this as bad data, and then process rest of the records as pipe is the only way one can identify the change in the columns, the way to identify bad data is, 

    a) Use a script task before your DFT, check each row for number of pipes expected ex: if you expect 10 columns then pipe should be 9 .

    b) Compare this condition and write a new file with only valid records and process this file.


    Abhinav http://bishtabhinav.wordpress.com/

    Monday, August 11, 2014 3:37 PM
    Moderator

All replies

  • If the file has no text qualifier for this field then consider replacing the delimiter as I did in my blog:

    http://geekswithblogs.net/Compudicted/archive/2011/09/19/ssis-how-to-remove-occasional-quotes-and-replace-the-column.aspx


    Arthur My Blog

    Monday, August 11, 2014 3:36 PM
    Moderator
  • I would say you would have to consider this as bad data, and then process rest of the records as pipe is the only way one can identify the change in the columns, the way to identify bad data is, 

    a) Use a script task before your DFT, check each row for number of pipes expected ex: if you expect 10 columns then pipe should be 9 .

    b) Compare this condition and write a new file with only valid records and process this file.


    Abhinav http://bishtabhinav.wordpress.com/

    Monday, August 11, 2014 3:37 PM
    Moderator