Flat File Source Problem...

I have a weird thing happening.
I have an .csv file. When I try to load it into a table, I can do it easily in DTS 2000. But when I am trying to do it in SSIS 2005 with exactly the same settings (like Text qualifier, row delimiter etc.), I am getting an error: "The last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly." I looked at the file and it looks complete to me.

What could be the problem ?

P.S. DTS 2000 is on 32-bit Windows, and SSIS 2005 is on 64-bit Windows 2003. Could that we a problem ?

Victor_V wrote:

I have a weird thing happening.
I have an .csv file. When I try to load it into a table, I can do it easily in DTS 2000. But when I am trying to do it in SSIS 2005 with exactly the same settings (like Text qualifier, row delimiter etc.), I am getting an error: "The last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly." I looked at the file and it looks complete to me.

What could be the problem ?

P.S. DTS 2000 is on 32-bit Windows, and SSIS 2005 is on 64-bit Windows 2003. Could that we a problem ?

I suppose it could be a problem, but DTS has historically been more forgiving when processing files. Are you sure you don't have a carriage/line-feed as the last line in the file? Sometimes this happens, and SSIS will think that is an incomplete row (It is, technically).|||It is true that the parsing behavior in SSIS is very different from DTS 2000. DTS was very forgiving as Phil mentioned, and there could be an extra carriage/line-feed which is causing this. Also DTS used to process files even if not all columns were populated in a row. However, SSIS would bail out on such files.|||

How do I check for this extra carriage return character ? It's not really visible. What editor would I use ?

Also, I usually get those "pipe" delimited files from Excel files, saving them as .CSV files.

Thanks,

Victor.

|||

Victor_V wrote:

How do I check for this extra carriage return character ? It's not really visible. What editor would I use ?

Also, I usually get those "pipe" delimited files from Excel files, saving them as .CSV files.

Thanks,

Victor.

Go to the last line of the file. Does the cursor sit at the end of a row of data, or on it's own line? That's one way.|||It sits on it's own line (at the beginning of a new line)..|||

Victor_V wrote:

It sits on it's own line (at the beginning of a new line)..

Hit backspace on that line and then save the file. Try running it again. Or try a ragged-right format.|||

Actually, I just opened this file in UltraEdit in HEX view, and the last 2 characters in the file are "OD OA", which is the end of the line, just like at the end of every row...

That's very strange...

|||

Victor_V wrote:

Actually, I just opened this file in UltraEdit in HEX view, and the last 2 characters in the file are "OD OA", which is the end of the line, just like at the end of every row...

That's very strange...

That's not strange, that's the point. SSIS sees that as a row, but there aren't any columns in it.|||No, what strange is that those 2 characters ARE the last characters in the row (and each row), and there is nothing else there. That's how it looks like in UltraEdit HEX editor. But when I open the same file in Notepad and hit Ctrl+End to go to the end of the file, that's when the cursor goes to the new row, instead of stopping at the end of the last row.|||I want to be sure on a few things...

The format is: Delimited
The text qualifier is: " (if needed)
The Header row delimiter is: {CR}{LF}

Under columns:

The Row delimiter is: {CR}LF}
The Column delimiter is: Comma {,}|||

The format is: Delimited
The text qualifier is: <none>

The Header row delimiter is: {CR}{LF}

Under columns:

The Row delimiter is: {CR}LF}
The Column delimiter is: Comma {|}

|||Phil ? Anyone ? Any thoughts ?|||

Victor_V wrote:

Phil ? Anyone ? Any thoughts ?

Can you post a sample line from the csv file?|||

Here are the last 5 rows in my .csv file:

12|Community Network Services, Inc|MI|C|T1020-Low|Personal care services, per diem |Blank||Per Diem||49|13461|13461|359677.92|26.72|26.72||0|13,461|13,461|0|615013.79|99.60%||0.00%|433.3|0.07%|2031.64|0.33%|617,479|0.00|45.69|0.00|0.03|0.15|45.87|24.96|20.63|33.51|22.81|29.7|same method from prior year
12|Community Network Services, Inc|MI|C|T1020-Moderate|Personal care services, per diem |TF||Per Diem||29|7391|7391|619809.26|83.86|83.86||0|7,391|7,391|0|490943.23|99.63%||0.00%|265.36|0.05%|1554.76|0.32%|492,763|0.00|66.42|0.00|0.04|0.21|66.67|68.16|53.58|68.53|54.88|67.43|rates reflect similar to prior years
12|Community Network Services, Inc|MI|C|T2003|Non Emergency Transportation|||Trip||2|148|148||0.00|0.00||0|148|148|0|544.16|100.00%||0.00%||0.00%||0.00%|544|0.00|3.68|0.00|0.00|0.00|3.68|0|7.99|0|7.92|0|
12|Community Network Services, Inc|MI||GF Pharmacy|GF Pharmacy||||||||||||||||||||||||753,848||||||||||||
12|Community Network Services, Inc|MI||H0038|Drop-in Center||||||||||||||||||||||||447744.18||||||||||||

Thanks, Phil.