SQL Server 2008 SSIS Bug
Running SQL Server 2008 Standard SP1 64 bit on a standalone machine running Windows Server 2008 R2. We receive a file that sometimes contains an extra CRLF at the end of the file. Integration Services will note a warning during manual execution, Warning: There is a partial row at the end of the file. But the package will complete successfully and load all records from the file with the exception of the final record. This is a tab delimited .txt file that we receive from a vendor which cannot be changed. If you manually remove the extra CRLF it loads all records, including the final record, with no problem. We installed the DTS runtime on another server to process these files and as everyone knows DTS is much more forgiving and loads all records with no problem even with the extra CRLF present. However, with the investment we've put into SQL Server 2008 we would like to use SSIS rather than DTS. I've searched this problem on the web but haven't had much luck in finding a solution. What needs to be done to correct this?
October 18th, 2010 3:41pm
I don't think it as a bug. there are lots of ways to handle this case. 1>you can use conditional split and check the length of the row or columns ie len(column1)=0. Process only those rows which have data. OR 2> Use Script Component let us know if you have more doubt. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/
October 18th, 2010 4:16pm
Perhaps I'm misunderstanding. The old tool works without any additional steps/coding/effort. The new tool requires additional steps/coding/effort. This isn't a bug?
October 18th, 2010 4:21pm
Here is an additional twist to this issue. When I run the package manually with my domain id that has admin access on the server and sysadmin within SQL Server, it processes the entire file without any issues. When my counterpart executes with his id that has the same permissions all but one of the records is inserted into the table.
October 18th, 2010 4:30pm
Permit me to disagree, SSIS has nothing to do with DTS.Arthur My Blog
October 18th, 2010 4:47pm
I think you need to step back and look at your expectation. It takes 5 min to set your SSIS properly up (installing DTS runtime was a waste of time IMHO). The file comes sometimes with an extra CRLF. Here is the hurdle. You can change the file. You just cannot get a "clean" file. I would pre-process the file you are loading, by either sniffing out the extra CRLF before doing anything in a script task as ETL vs ELTL suggested (gives an opportunity to save it aside as a proof of a bad file) or the conditional split.Arthur My Blog
October 18th, 2010 4:54pm
So, do additional work on the file that you never did additional work on when you were importing it into a sql server 2000 database prior to upgrading to sql server 2008?
October 18th, 2010 6:05pm
The answer is yes. In DTS you did not really deal with it, you just closed your eyes on it relying totally on the DTS handling that extra CRLF for you, now SSIS is actually more issue "aware" than its counterpart and tried to be your friend warning about the partial record.Arthur My Blog
October 18th, 2010 6:09pm
So now when I close my eyes and rely on SSIS to deal with it I receive a warning yet it completes the package successfully although not inserting the final row from the file. I still don't understand why it simply doesn't leave out the blank row between the CRLF's. Yet it apparently leaves that row out in addition to the complete row that has data at the end as well.
October 18th, 2010 6:21pm
I think I have the same problem... Package works fine when I run in BIDS on my local machine & it processes all the rows in the Flat File source...even the last row. Package works fine when I deploy same package to SSIS Package store on the 64Bit server & run from a connection to SSIS via SSMS. Package does not process the last row when I run same package on the server from a SQL Server Agent job with an SSIS step. I figured it was a permissions problem related to the SQL Server Agent service account...so I RDP'ed into the server as the SQL Server Agent service account & tried to run the package from BIDS as the SQL Server Agent service account user. It did not process the last row as expected. That's when I noticed the following warnings: [SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console. [Source File ] Warning: There is a partial row at the end of the file. [Source File ] Information: The total number of data rows processed for file "\\MyServer\InboundData\\InboundFile.txt" is 1. Since the the first row is the header...there were really no rows processed. I don't think you're crazy & I don't think you should have to use DTS. I think we are talking about the same thing & it seems like it is permissions related to me. When the 1st warning goes away...so does the partial row problem. I will see if I can science this out & I will report back. I've been battling this problem for a while...when I deploy the same package to a different server it works fine...Where it does not work is on a new server with a SQL Server 2008 R2. Very frustrating but you shouldn't have to write your own Flat File Source task in a script...this is why we invested in SQL Server.
December 15th, 2010 11:26pm
I am also having the same problem, except unlike Tim Bridges, I have modified my file so that there is not an extra CRLF at the end of the file. And just like Pete Molnar, the package runs fine processing the last line when run via BIDS locally, but not when executed via the SS Agent job. I even tried taking the last several rows in the file, cutting them out and pasting them to the middle of the file, leaving the last row a row that was processed during the first run. Still, the last row doesn't process via SS Agent job, so it's not a file formatting issue. This is definitely a bug. Any updates MS?
February 14th, 2011 10:03pm
Please count me as well. I have the same problem. The last row is not processed.
April 14th, 2011 7:43am
I too have this issue. Has anyone figured out a solution? Thanks
May 24th, 2011 8:09pm
I too have this issue. Is there a way to overcome this? Any latest updates / SPs to correct this?
August 29th, 2011 4:33am
I too have this issue. Is there a way to overcome this? Any latest updates / SPs to correct this? could you please start new thread with your problem in detail.... as this is actually DTS related problem. Let us TRY this | Mail me My Blog :: http://quest4gen.blogspot.com/
August 29th, 2011 4:50am
There are two things occurring here. One is that the problem doesn't seem to happen when running in 32bit (note that when running from SSMS integration services on a 64bit machine it actually runs in 32bit WoW - don't ask me why) and if so then this is definitely a 64 bit bug. Secondly the Flat file source component is very limited in its ability to handle file errors such as the one described. The organisation I work for wrote their own component in the end - sadly it is not available elsewhere. I agree with an earlier post, it should be possible to use a script task to look for and strip out the final row in the file before you attempt to load it.
August 29th, 2011 5:52pm