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?

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/

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?

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.

Permit me to disagree, SSIS has nothing to do with DTS.Arthur My Blog

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

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?

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

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.

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 [1]] Warning: There is a partial row at the end of the file. [Source File [1]] 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.

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?

Please count me as well. I have the same problem. The last row is not processed.

I too have this issue. Has anyone figured out a solution? Thanks