10

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.

Grant
  • 11,138
  • 32
  • 94
  • 140
  • Do you have the exact same patch level for SQL Server on Dev vs. Live? Compare the results of `SELECT @@VERSION` for both servers. – Will A May 27 '11 at 06:04
  • Production = [Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 x64], Development = [Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 x86] – Grant May 27 '11 at 06:12
  • It's worth getting production up to SP2 as soon as you can - this could well solve your problem. – Will A May 27 '11 at 06:13
  • 1
    This link (and the two offshoots) could well prove useful. http://forums.asp.net/t/1339724.aspx/1 – Will A May 27 '11 at 06:15
  • 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 – Grant May 27 '11 at 06:23
  • I dont know how soon i will be able to update the server. if anyone has any other ideas.. – Grant May 27 '11 at 06:32
  • Does deleting the blank line at the end of the file have any effect on the import? – Will A May 27 '11 at 06:33
  • 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. – Tom Pickles May 29 '11 at 10:32

3 Answers3

9

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.

Dan
  • 91
  • 1
  • 2
3

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.

Eric Lynes
  • 151
  • 1
  • 6
  • This fixed my issue. A similar problem/solution was also posted on dba.stackechange here: http://dba.stackexchange.com/questions/6132/ssis-flat-file-manager-skips-the-last-record – Invalid Character Jul 01 '13 at 21:27
0

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.

Ram
  • 793
  • 7
  • 14
  • Is that the answer? Does it help to use the /X86 option on dtexec.exe? http://msdn.microsoft.com/en-us/library/ms162810.aspx – Alan McBee Jul 27 '11 at 22:07
  • 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. – Ram Jul 28 '11 at 14:26