I recently ran across an issue while working with a package developed by a co-worker. It’s one I don’t fully understand and I hope someone will chime in with a better explanation than I have.
The package isn’t all that complicated. It takes start and end dates as variables and selects data based on the date range. Then it does some lookups and finally inserts into some tables. Both the source and destination databases are on the same SQL 2005 server. The package is being run as a SQL Agent job on a SQL 2008 server. Bother servers are running Windows 2008 R2, and the package was developed using Visual Studio 2008.
For the first few runs the package ran fine, going through small data sets of less than 500,000 rows. After that the SQL job began to return error code 0x80004005. The messages were “Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLD DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 Description: “Protocol error in TDS stream”. This would be followed by a few more with different descriptions; “Communication link failure” and “TCP Provider: An existing connection was forcibly closed by the remote host”.
This is where I came in. I exported the package from SQL to a new BIDS project on the same server it runs from. I was able to run it successfully from BIDS, so I thought there was just some issue with running it as a SQL job. Then BIDS started giving me the same errors with a few new ones; “-1073450952, 0x, SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “xxxxxx” returned error code 0xC0202009”. There’s a bit more but just pointing out there were errors prior to that one.
I did notice that the error always occurred at the same place, in the same data flow on the same OLE DB Source component. Just as a quick test I dropped the existing component and replaced it with another OLE DB Source, using the same data source and query to fetch the data. And voila! No more errors. I redeployed it to SQL and now it runs as a SQL job just fine.
At this point though I’m at a loss over just what happened. My only guess right now is that the original package was developed on a server running the full Visual Studio 2008 IDE. He only has SQL 2008 tools installed and I’m betting he never updated them. The server is running just the BIDS shell that comes with SQL 2008, but it is patched at SP 3. So possibly there was a patch on the server that affected BIDS. I haven’t had a chance to go over what was in each 2008 SP, I’ll do that tomorrow. And as I said, I’m just guessing.
So if anyone who reads this has any insight, please add a comment. I’d love to find out just what happened so I can be ready if it happens again.