SSIS Package Fails Sometimes, But Not Always (Unsupported Parameter Type)

I've been working with an SSIS package which has exhibited some strange behavior. Currently, we run this package through the Task Scheduler which calls a .bat file that refers to the package. This package fails quite frequently, but will always run successfully (result 0x0) if it is re-run enough times. Our Task Scheduler task for this process is set to automatically re-run if it returns a result that isn't 0x0. Usually it runs within the first 3 tries, often on the first. Since I performed an update a few months back, it has taken longer than usual on a few occasions, failing more often than usual.

Unfortunately, re-running is really just a stopgap measure and is not a long term solution to our problems. However, given where we are in the current cycle of things, a complete overhaul of this package is not feasible. For more background, this package performs an ETL process.

The other day, I ran the package through SSMS Integration Services to get an idea of where the package was failing and why. I received the following errors:

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "ASEOLEDB" Hresult: 0x00000000 Description: "[00000] [Native Code: 30071] [ASEOLEDB]Unsupported parameter type".

Error: OLE DB error occurred while fetching parameterized rowset. Check SQLCommand and SqlCommandParam properties.

Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Lookup 1" (70326) failed with error code 0xC0208253 while processing input "Lookup Input" (70327). 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.

Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DIST data" (1660) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

The DTS_E_PRIMEOUTPUTFAILED error and the preceding line occurred for 10 different components, but I don't think listing them all out is helpful since it's the same error.

I've done some reading up and from my understanding, this error may have to do with the Buffer size. What I don't understand is why this error might occur sometimes and not others. The first error about unsupported parameter types was something I wasn't able to find much on, but the same applies: why would it fail sometimes and work sometimes?

I was really hoping someone might be able to explain why these errors could occur sometimes but not always, and if there is a potential quick fix that I could look into so that the package would run successfully more often.

Any insight would be greatly appreciated.

SSIS error messages tend to not always reflect the truth. The issue you're seeing is more likely to be due database connectivity issues. And the fact that the error is random just make this possibility even stronger.

When the error appears again, try to run the same query you have in the OleDb stage presenting the error through Management Studio or command line as well as activating the SQL Server logs to check if the connectivity to the database is dropping somewhere along the way. This would be the first thing I'd tackle instead of looking into the ssis package because if it works once, then it should always work considering all the external factors wouldn't change which in this case could not be true.