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.
<DTS:Property DTS:Name="TextQualifier">_x003C_none_x003E_</DTS:Property>
Replace Text Qualifier with below value.
<DTS:Property DTS:Name="TextQualifier"><none></DTS:Property>
History
v1.0 SSIS Tips and Tracks On Feb 19 2013
