Later, I ran into what appeared to be a random connectivity issue using Netezza OLE DB provider. The packages would unit test just fine, but some of them would fail when ran concurrently from a ‘parent’ package.
Each time the parent package ran, the result was the same: The first x numbers of package succeeded while the remaining ones failed. Packages would fail randomly as shown in the 2 examples in the picture above. The error messages generated by SSIS and Netezza were not very helpful:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE_DST NZ Table" (1776) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
After a lot of troubleshooting I was able to make some observations:
- The first set of packages, as given by MaxConcurrentExecutables property in parent package, completed successfully. If I ran it in my laptop, with 2 cores, 2 packages would succeed. If I ran it in the server, a 16 core machine, then the first 16 or so packages would succeeded.
- Watching the activity in Netezza admin console revealed that the first x connections remained open for extra 1-3 minutes after the first set of packages completed.
To make the story short, after 5 months and exchanging 122 emails with Netezza support, they finally were able to reproduce the issue and acknowledge there was an problem with their OLE DB driver. Their final email says:
The issue is in our OLEDB driver relating to error-handling in a multi-threaded environment.The fix will be in patch 5.0.10P8. The release date will be sometime in mid to late December
I have not been able to verify if the issue was actually fixed because our internal operations team does not have a date for applying that patch. And if you are wondering what did I do to get around the problem, I ended up using a script task to pause the execution of.packages for 3 minutes, long enough for the open connections to close, before the next set of packages got executed. This is not by any mean an approach I would put into production, but was good enough for me since this was a 1-time data migration.
Here are the details of my environment:
• Windows Server 2003 R2 SP2 Enterprise x64 edition
• 64 GB RAM - 16 cores
• SQL Server 2005 SP2 Enterprise edition (64 bit)
• Netezza OLE DB Diver 5.0.8 (but we got same error with 5.0.6 version)
I am wondering how many people is using SSIS to load data into Netezza and running into similar issues?