SSIS is dropping a record on flat file source import
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
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.