Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Package Not Importing Last Row in Flat File


SSIS Package Not Importing Last Row in Flat File

Author
Message
david.tyler
david.tyler
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 663
Hi all. I have an SSIS package that imports a bunch of .csv flat files. I am using a Flat File connection for each file type and running each file though a standard data flow. The data ends up flowing into a OLE DB Destination. When I run the import through Visual Studio all rows are imported however when I schedule the package in a job, the last row in each file is getting left behind. I verified each file row has a cr lf through a hex editor. This is driving me crazy since the same file set will import just fine using Visual Studio. Any pointers would be greatly appreciated.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16461 Visits: 13207
Are you sure you didn't do any error redirection?
Any error/warning at all?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
HLGEM
HLGEM
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 22
We have had the issue of dropping the last record when using SSIS pacakages in the file system. Somehow the SSIS XML file text was changed in copying (do not yet know why) from dev to prod when the text qualifier was set to none.

The text qualifer XML we used to fix the problem was
<DTSTongueroperty DTS:Name="TextQualifier"><none></DTSTongueroperty>

We just opened the SSIS XMl and replaced the text qualifier line that ws in it with the above and saved.

Even though I'm not sure why this is happening, I pass this on in the hopes it might help your problem.



david.tyler
david.tyler
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 663
Thank you all for replying to my post. We ended up installing SP2 and that seems to have corrected the issue.
nzack
nzack
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 24
We have the same issue with two of our packages developed in BIDS 2008 and deployed on SQL Server 2008 R2.

We do not have the issue in our DEV environment which is at version:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1790.0 (X64) Apr 22 2011 11:55:34 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

PROD does not have the Cumulative 4 Pack on yet and that is where we see the issue. We will be putting on the cu soon, but it is not a known issue fixed in this KB.

PROD version: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)


The import files are .csv and there are no apparent anomalies in the column delimiters or designated row identifier (even for the last row). When running one package as part of a job from the SQL Agent, the last record is skipped every time. Text qualifier is set to none. We are baffled.

Anyone with a resolution or work around?


Thanks
Covi-451424
Covi-451424
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 205
Hello,

I've just discovered this issue as well and in production, SS2k8. I don't have the issue in ss2k8 Dev and UAT, they are patched with SP2.

I noticed on my input file that I can add an '**EOF**' to end of the last row and we process all the rows. In fact I can add a ** to the last row and process all rows. We can then locate the row and remove the '**EOF**'

I am working on applying SP2 into production and hoefulle that will resolve my issue.

Have Fun - Covi
minnie.db
minnie.db
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 15
HLGEM (6/20/2011)
We have had the issue of dropping the last record when using SSIS pacakages in the file system. Somehow the SSIS XML file text was changed in copying (do not yet know why) from dev to prod when the text qualifier was set to none.

The text qualifer XML we used to fix the problem was
<DTSTongueroperty DTS:Name="TextQualifier"><none></DTSTongueroperty>

We just opened the SSIS XMl and replaced the text qualifier line that ws in it with the above and saved.

Even though I'm not sure why this is happening, I pass this on in the hopes it might help your problem.


The above 'XML code, change to <none>' worked for me, I did not want to go to SP2 path as I am not DBA and shall not install patches on production DBs even though I prefer it should be done.
Ed B
Ed B
Mr or Mrs. 500
Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)Mr or Mrs. 500 (594 reputation)

Group: General Forum Members
Points: 594 Visits: 1775
We have the same problem moving packages between different versions of sql.

The text qualifier gets changed from <none> to _x003C_none_x003E.

Dev and Prod are OK, but Test is on an older version. We have to fix the text qualifier to <none> on every connection or it drops the last row.

I will try the xml solution suggested.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search