May 31, 2011 at 4:06 pm
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.
May 31, 2011 at 11:36 pm
Are you sure you didn't do any error redirection?
Any error/warning at all?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 20, 2011 at 2:41 pm
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
<DTS:Property DTS:Name="TextQualifier"><none></DTS:Property>
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.
June 20, 2011 at 2:48 pm
Thank you all for replying to my post. We ended up installing SP2 and that seems to have corrected the issue.
July 11, 2011 at 10:32 am
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
July 19, 2011 at 5:33 pm
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
October 10, 2012 at 12:18 am
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
<DTS:Property DTS:Name="TextQualifier"><none></DTS:Property>
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.
October 11, 2012 at 10:01 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply