Click here to Skip to main content
15,867,765 members
Articles / Database Development / SQL Server
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
19 Feb 2013CPOL1 min read 19.1K   4   1
I found out that a possible cause is the difference between the SSIS 32 bits and 64 bits environment

Introduction

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.   

Background 

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. 

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

 Replace Text Qualifier with below value. 

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

History 

v1.0 SSIS Tips and Tracks On Feb 19 2013

License

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


Written By
Technical Lead
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
PraiseThanks! Pin
Lolo19863-Aug-18 8:36
professionalLolo19863-Aug-18 8:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.