Flat File Import leaving last record behind

I've got a pkg that imports a .txt flat file that doesn't contain headers and loads into a staging table. The pkg works fine in my DEV environment. When I deploy the pkg to the PROD env and run it to load the exact same flat file as in DEV, it consistently does not load the last record.

I could understand if it skipped the first rec - then I'd just check the data src property to make sure the PROD version had "column names in headers" checked. But I've...

• deleted the PROD pkg
• deployed a fresh copy of the pkg that just ran successfully from DEV to PROD
• verified the connections / configs are set correctly

The pkg in PROD doesn't error, so I'm stuck scratching my head as to what could possibly be different btwn the two envs. Same pkg, same source file...

Can anybody think of something I haven't checked?

Thx,

Greg

Hmm, you should know how many lines you have on the file right ? If you skip n-10 row on the file and previewed it, does the last line shows up ?

All I can think of is the last line doesn't have the right linefeed like CR LF, but tell me what you find out

Thanks for the tip, I was able to see the end of file in Preview mode. Interestingly, the last record is there when I preview the Flat File Source. However, when I added a grid data viewer to the output of the Flat File Source and I executed that task, the last record does not show up.

I opened the source file in TextPad w/ binary view and I can see the last two characters of the file are 0x0d 0x0a (CR LF). The same file works when I copy it to DEV and run it there using the same package copied to that server.

So, I'm thinking it must be an environment thing. I can't think of a difference between the DEV and PROD env. Both are Windows Server 2008R2 64bit, running SQL 2008 SP1 (though DEV has CU 4 applied). Since the data source for the Flat File is controlled by a For Each Loop Container, there's no config file to worry about.

This one has me stumped. I'll be happy wear egg on my face if it's something stupid - usually is - but I've checked everything I can think of.

Anyone know where the "ignore last record" config setting is stored? ;-)

gharwood said:

Interestingly, the last record is there when I preview the Flat File Source. However, when I added a grid data viewer to the output of the Flat File Source and I executed that task, the last record does not show up.

Let's do this then:

1. Check the destination table. Are you sure the last row is not inserted into the destination ? The row number on the DFT is sometimes different than the real number (happened a few time with me). And please check if there's a NULL or a ',' somewhere in the destination table.

2.Try with smaller file like let's say 10 row of the whole file as a source (create a new file), see if the last row appeared. If it does try separating the file into a few smaller file and see if only one of those file doesn't show the last row and you'll find the "culprit" row

BTW how many row does this file have and what's the column separator ? All I can think of is there's a problem in one of the rows inside the file which might be hard to find if the file is very big.

Good luck

In the process of deleting all but the last record from the flat file, I noticed that the text qualifier for the flat file was an odd value: _x003C_none_x003E_.

A quick google of that and I'm seeing other folks are having similar problems. If I edit the package on PROD and replace "_x003C_none_x003E_" with "<none>", the package runs fine and pulls in the last record. Btw, 0x3c and 0x3e are "<" and ">" respectively.  I'm still not sure why the text qualifier gets changed.

I test the pkg in DEV, qualifier is "<none>", works fine. Deploy using BIxPress,  or even if I copy the dtsx file manually, open the package on PROD and the text quarlifier is changed to "_x003C_none_x003E_".

Will post more when I find out why the qualifier is being altered.

Hyblade, thanks for your persistence!

Greg

Good for you, at least it will work now 

I am having this SAME issue.

Package runs fine on my own box, we move it to dev environment, and the last row is missing.

I Have <none>  as the text qualifier  on my package on my box, but I am not sure what it is on the DEV environment.

We do not have BIDS on the dev box installed, so, is there a way to see if the text qualifier has changed to something other than <none>  which seems the issue above ?  

OR,  is there a way to make sure this does not change when you copy the dtsx package from my box to the dev box ?

Worse comes to worse, I will see if we can install BIDS there, however, I will definitely not be able to do it on my TEST or PRODUCTION boxes, so, I will need to be able to verify that this is not an issue there.

HELP PLEASE  :-(

hesil

SOLVED,  I hope this helps someone else out there

It turns out that we had to set the text qualifiers at the COLUMN level to false as well, and then, it worked  ;-)

ANY IDEA WHY ?  :-)

Cheers,

Hesil