I have an SSIS package deployed to both my DEV and QA environment, both QA and DEV are under the same server and SQL Instance. The package has only three objects in the dataflow: an MDX query, a derived column transformation, and the destination table. When I run that package it does so successfully on both DEV and QA.
I then created another package with just a simple logic that validates whether the original package is to be run or not depending on the month. I ran it and the validation works fine. For this new package I added a File connection to the original package and then added an Execute Package Task which I then mapped to the File Connection created previously.
I manually copied the DTSX file created and placed it in the Server shared folder hosting the SQL Server. When I run this new package from my DEV folder it works with no issues When I run the new package from the QA folder it fails with the following error
ERROR MESSAGE: Code: 0xC0202009 Source: Data Flow Task Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. End Error Code: 0xC004701A
Source: Data Flow Task SSIS.Pipeline Description: component failed the pre-execute phase and returned error code 0xC0202009. End Error DTExec: The package execution returned DTSER_FAILURE (1). The package execution failed. The step failed.
The error indicates the issue is on the Data Flow task but if I run the package that contains that data flow by itself it does work on both environments. Why would that package fail only when called by another package and only for the QA schema? I'm on SQL Server 2008 R2 by the way.