SSIS is dropping a record on flat file source impo

Question

am experiencing a very strange issue in SSIS (2008).

Basic workflow is as follows..

Using a flatfile source (CSV), bring into SSIS, push into SQL.

When process is run on dev environment, everything works perfectly.

When the dtsx package is placed in production.. 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 delimeters, rebuilding the flat file source connection.

Has anyone seen anything like this before?

The CSV file contains 10 records and has 4 columns. It is comma delimited and line delimited by {CR}{LF}. The file was produced by Excel and has a blank line at the end of the CSV file.

Let me know if more information is required. Im not sure what else i can offer.

Answer 1

I had this exact same problem. What I found out was that when I copy an SSIS package from one server to another sometimes the text qualifiers for flat file sources get messed up, so instead of <none> it will have _x003C_none_x003E_. Once I fixed that no records were dropped.

Answer 2

I had the same problem, and I resolved it yesterday by making sure the individual columns were not TextQualified. Setting them all to false magically made mine work. I hope that helps.

Answer 3

I had faced similar issue before. This typically happens because your production SQL box is x64 and development is X32. It is still surprising but OLEDB drivers for x64 SQL server are NOT totally supported. However the same for x32 bit are very much supported.

It was always missing last record and was found in data reconciliation later. I was using flat file source adapter and then with trial and error using different DFT's found that the error goes away if you use BULK INSERT as it is not dependent on X64 drivers.