I'm upgrading our ETL solution and databases to SQL server 2012. I have tested this upgrade but we have a debate about running an SSIS 2008 package with the Job on the SQL 2012 instance. I understand that the 2008 R2 instance job runs:
Message Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 2:10:11 AM DTExec: The package execution returned DTSER_SUCCESS (0)...
and the 2012 instance job does an in-place upgrade of the packages and runs:
Message Executed as user: xxxx. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 8:29:18 AM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 8:29:18 AM Finished: 8:59:46 AM Elapsed: 1816.76 seconds. The package executed successfully. The step succeeded.
My question is this: Are there any reports of failure for such scenario and is it safe to assume there is a backwards compatibility?
From this page, DTExec 2012 converts earlier version packages in-memory to 2012 format. It is conceivable that the conversion could fail, however, it should be possible to test the packages, and if they run successfully once (i.e. convert successfully) then they should do so every time. Their behavior should be similar to a high degree, but I would not expect complete equivalence in all circumstances.
The most likely scenario for conversion failure is if the package includes third-party components that are not available for SSIS 2012.
I would not assume that a package can be run by a later version of DTExec without testing, but if it runs successfully once, there is a high probability that it will run successfully every time.