Click here to Skip to main content
13,447,006 members (42,607 online)
Click here to Skip to main content
Add your own
alternative version


3 bookmarked
Posted 19 Feb 2013

SSIS 2008 drop last data row from *.csv or *.txt file while import data using SSIS DataFlow Task..

, 19 Feb 2013
Rate this:
Please Sign up or sign in to vote.
I found out that a possible cause is the difference between the SSIS 32 bits and 64 bits environment


Using a flat file source (CSV), bring into SSIS, push into SQL. When process is run on Local environment, everything works perfectly. When the dtsx package is placed in DEV environment. using the exact same flat file source, the last record in the file is dropped by the time it gets to the start of the SQL proc. 

Have gone over everything I can possibly think of including line delimiters, column delimiters, rebuilding the flat file source connection.   


What I found out was that when I deploy SSIS package from development machine to Application Server text qualifiers for flat file sources get messed up, so instead of <none> it will have _x003C_none_x003E_. Once you fixed that no records were dropped.

There is a bug in SSIS when exporting data from SQL to a comma delimited text file where you specified no text qualifier. 

This bug apparently only occurs when you develop the SSIS on a x64 win7 PC and copy the .dtsx file (windows explorer copy/paste) to network path of a x86 SQL server and schedule the job to run from SQL Agent on the same x86 SQL server. 

<o:p />

Using the code 

I found out that a possible cause is the difference between the 32 bits and 64 bits environment. We now fix the packages by opening them on the 64 bits environment
and remove the text qualifier (_x003C_none_x003E_) save the package. 

There are other ways to resolve issue. The quickest one which I found is just open package in notepad and replace source flat file connection text Qualifier as mention below.   

 Flat file Text Qualifier. 

<DTS:Property DTS:Name="TextQualifier">_x003C_none_x003E_</DTS:Property> 

 Replace Text Qualifier with below value. 

<DTS:Property DTS:Name="TextQualifier">&lt;none&gt;</DTS:Property>   


v1.0 SSIS Tips and Tracks On Feb 19 2013


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Rajesh Manjarekar
Technical Lead
United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01-2016 | 2.8.180318.2 | Last Updated 19 Feb 2013
Article Copyright 2013 by Rajesh Manjarekar
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid