SSIS is dropping a record on flat file source import

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.

Unpeg

Do you have the exact same patch level for SQL Server on Dev vs. Live? Compare the results of SELECT @@VERSION for both servers. – Bligh

Production = [Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 x64], Development = [Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 x86] – Unpeg

It's worth getting production up to SP2 as soon as you can - this could well solve your problem. – Bligh

This link (and the two offshoots) could well prove useful. forums.asp.net/t/1339724.aspx/1 – Bligh

I had a look at the links, they dont seem to be too relevant but ill see about getting SP2 on their and see if that fixes the problem – Unpeg

I dont know how soon i will be able to update the server. if anyone has any other ideas.. – Unpeg

Does deleting the blank line at the end of the file have any effect on the import? – Bligh

Having your dev and production environments at different patch/SP levels is asking for trouble. Dev env should always be as near to a clone as prod env as possible. – Cephalometer

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.

Milissamilissent

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.

Disharoon

This fixed my issue. A similar problem/solution was also posted on dba.stackechange here: dba.stackexchange.com/questions/6132/… – Slowdown

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.

Lattonia

Is that the answer? Does it help to use the /X86 option on dtexec.exe? msdn.microsoft.com/en-us/library/ms162810.aspx – Aqualung

The package was run as part of a sql server job and I did point it to use SSIS in type drop down in the step of the job and then tabbed to execution options to "use 32 bit runtime" but it didnt help. Weird it was still showing the error and on forums it was suggested to use BCP or bulk insert as these tasks are not bit-dependent. – Lattonia