May 31, 2007 at 11:59 am
I have a dataflow task which has a delimited flat file as a source with following settings
Header row delimiter = Comma (,)
Text Qualifier = Double
Header rows to skip = 0
Column names in the first data row
My problem is after last column name there is a comma, so SSIS considers it as a new column where as in DTS it ignores the last comma.
Because of this the new column contains my next row data, which is wrong.
Is their a way where I can get rid of my last comma in my SSIS package itself, without changing my input file as even if I want I can’t change my file.
------------
Prakash Sawant
http://psawant.blogspot.com
June 6, 2007 at 7:28 am
Are the column names consistent for each data file? If so, just skip the first row of data (Header rows to skip = 1).
hth
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
June 6, 2007 at 1:16 pm
If I understand you correctly, you are saying that because of the comma at the end of the line SSIS is automatically generating a blank column...
You should be able to resolve this by going into the advanced properties of the flat file connection manager and removing the last column, then set the row delimiter (column delimiter of what is supposed to be the last column) to {,}{CR}{LF} (or whatever combination is right for your source) and you should find that the trailing column dissappears.
If you still have problems, one common way to deal with unreliable source files is to stage them first using a script component to remove unwanted characters.
Hope this helps
Kindest Regards,
Frank Bazan
June 6, 2007 at 1:31 pm
I already tried {,}{CR}{LF} this option but it didn't work.
Now I am using Script Task for getting rid of that extra comma. so far its working fine.
Thanks all for your help.
------------
Prakash Sawant
http://psawant.blogspot.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply