Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Package Not Importing Last Row in Flat File Expand / Collapse
Author
Message
Posted Tuesday, May 31, 2011 4:06 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 7, 2014 4:41 PM
Points: 238, Visits: 652
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.
Post #1117776
Posted Tuesday, May 31, 2011 11:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 13,570, Visits: 10,448
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1117861
Posted Monday, June 20, 2011 2:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 1:38 PM
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
<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.



Post #1128566
Posted Monday, June 20, 2011 2:48 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 7, 2014 4:41 PM
Points: 238, Visits: 652
Thank you all for replying to my post. We ended up installing SP2 and that seems to have corrected the issue.
Post #1128571
Posted Monday, July 11, 2011 10:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 26, 2011 8:14 AM
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
Post #1139881
Posted Tuesday, July 19, 2011 5:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 7, 2012 9:40 AM
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
Post #1144589
Posted Wednesday, October 10, 2012 12:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 4:20 PM
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
<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.
Post #1370700
Posted Thursday, October 11, 2012 10:01 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:24 PM
Points: 543, Visits: 1,654
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.
Post #1371632
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse