Missing last row when package is run as a job from SQL 2008 R2

Question

I have one package which reads a CSV and fills a table. This is a simple extraction. When i add the package to the server storage (SQL 2008), make a job en run it from that job, there is no problem.   

When I make the same job at our SQL 2008 R2 server and i run it, i'm missing the last row. It happens with all CSV's. When i run the package directly from the storage, there is no problem. So it only happens when its started as a job.

Is this a bug? Or am i missing something?

 

Thanks in advance!

J2.0

 

 

Answer 1

That certainly is odd. The last row and the one before that, does it have the correct row delimiters?

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 2

Thanks for the fast reply. The delimiter is set as {CR}{LF}. This is conform the file.

Answer 3

Not solved yet!

Anyone any idea's?

Answer 4

For all your CSV's what is the number of rows? do they have huge rows or with fewer rows?

---

Thanks Karthikeyan Anbarasan www.F5Debug.net

Answer 5

Are you aware that when you "run the package directly from storage" you are actually running it from your machine, and not on the server itself?  This can result in many execution differences.

Have you enabled Package Logging?  Please do.

Do you have any error redirection in your Data Flow?

---

Talk to me now on

Answer 6

Thanks so far. I wasn't realizing that i was running from my machine instead of the server. I executed the package from  the server resulting in the same (last) row missing. I did notice a warning in the logging saying: "Warning: There is a partial row at the end of the file"

I started searching and found a bunch of the same problems but no awnsers i.e.:

http://int.social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/510b9dbe-1e76-4d3b-8dad-aaf292a2c0fa

Answer 7

Please use an editor that can display hidden characters (we want to inspect the row endings at the bottom of the file).

Notepad++ would be one of them, from its View command in the toolbar go to Show Symbols->Show All Characters.

Please paste an image of how the records end here.

---

Arthur My Blog

Answer 8

I tried different endings:

last 3 lines:

AO ;;;5728;1,1;2011;10;11.800[CR][LF]
AO ;;;3758;1,1;2011;11;11.800[CR][LF]
AO ;;;7538;1,1;2011;12;11.800[CR][LF]

I also tried:

AO ;;;5728;1,1;2011;10;11.800[CR][LF]
AO ;;;3758;1,1;2011;11;11.800[CR][LF]
AO ;;;7538;1,1;2011;12;11.800

and

AO ;;;5728;1,1;2011;10;11.800[CR][LF]
AO ;;;3758;1,1;2011;11;11.800[CR][LF]
AO ;;;7538;1,1;2011;12;11.800[CR][LF]
[CR][LF]

 

Answer 9

I tried different endings:

last 3 lines:

AO ;;;5728;1,1;2011;10;11.800[CR][LF]
AO ;;;3758;1,1;2011;11;11.800[CR][LF]
AO ;;;7538;1,1;2011;12;11.800[CR][LF]

I also tried:

AO ;;;5728;1,1;2011;10;11.800[CR][LF]
AO ;;;3758;1,1;2011;11;11.800[CR][LF]
AO ;;;7538;1,1;2011;12;11.800

and

AO ;;;5728;1,1;2011;10;11.800[CR][LF]
AO ;;;3758;1,1;2011;11;11.800[CR][LF]
AO ;;;7538;1,1;2011;12;11.800[CR][LF]
[CR][LF]

 

What input is given to you (that you need to process)?

---

Arthur My Blog

Answer 10

What exacly do you mean? Its an excelfile which is saved as csv.

Answer 11

If you save it manually you supposed to get

AO ;;;7538;1,1;2011;12;11.800[CR][LF] format

So how about we stick to processing this kind of file?

---

Arthur My Blog

Answer 12

If you save it manually you supposed to get

AO ;;;7538;1,1;2011;12;11.800[CR][LF] format

So how about we stick to processing this kind of file?

---

Arthur My Blog

So if you agree with the above, lets run this file off the file system on the SQL Server 2008 R2 box. Do you get any errors or warnings?

---

Arthur My Blog

Answer 13

If you save it manually you supposed to get

AO ;;;7538;1,1;2011;12;11.800[CR][LF] format

So how about we stick to processing this kind of file?

---

Arthur My Blog

So if you agree with the above, lets run this file off the file system on the SQL Server 2008 R2 box. Do you get any errors or warnings?

---

Arthur My Blog

Just the warning: "Warning: There is a partial row at the end of the file"

Answer 14

Means you have an extra CRLF that perhaps you can just cut off with a derived column transformation.

---

Arthur My Blog

Answer 15

I found out just now that when i add a text qualifier (just a random one i guess), the package runs correctly. All rows are in the table. My flatfile does not contain any text qualifiers. My guess is that the file is read as text and the CRLF is not reconized at the last line.

 

 

Answer 16

Hi,

This solution does not work in SQL 2008 R2. Anyone has solution to this problem on R2?

Thanks,

Yogesh

---

http://sqlworkday.blogspot.com/

Answer 17

I had the exact same problem, and tried all kinds of things - your idea of adding text qualifier worked 100%.

Thanks!!

Answer 18

Hi:

I have the same problem. I have 26 rows of data in my CSV file but only the first 25 get imported.

When I preview the data in the Flat File Connection object, I see all 26 rows.

What do you mean by adding a Text qualifier? I added couple of blank rows but that did not resolve the issue.

venki

Answer 19

In the Connection Manager, in your SSIS package, open the File Connection you configured to read the CSV files. In the properties window, there's a property called Text Qualifier. The default value is <none>. Change it to ' and it should work.

Answer 20

Binho:

Awesome. I just noticed that the Text Qualifier had a wierd value. I set to to <none> and it worked fine.

Thank you very much.

Venki

Answer 21

I found an answer at http://dba.stackexchange.com/questions/6132/ssis-flat-file-manager-skips-the-last-record and it works great for me.  In BIDS, it was to edit the flat file data source in the advanced tab and set the text qualification drop down to false for all columns.  Package now runs and imports all rows (SQL 2008 R2 X-64).