Click here to Skip to main content
Click here to Skip to main content

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

By , 19 Feb 2013
Rate this:
Please Sign up or sign in to vote.

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. 

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>   

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)

About the Author

Rajesh Manjarekar
Software Developer (Senior)
United States United States
No Biography provided
Follow on   Google+

Comments and Discussions

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