I have a c# based console app that loads and executs an SSIS package. This has been working great... until we upgraded from SQL 2008 R2 to SQL 2012.
I posted this on a forum site (http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/03bae0fc-b51c-4258-a80a-8426e855576b) and it was suggested that I repost to Connect. So here it goes:
After quite a bit of testing, I can reliably conclude there is an issue with SQL 2012's version of the ManagedDTS assembly. Here's the short of what I did that lead me to that conclusion:
In VS2012, I created a new C# console app, targeting the .NET 4 framework. I referenced the ManagedDTS assembly that came with my SQL 2012 install. I recreated my load/execute code with a simple few lines, hard coding the path to a test SSIS package to load and execute.
Then I created a new, from-scratch SSIS package using SSDT (which ironically is based on VS2010, not VS2012). I started with a simple package that had 2 connection managers. One connection was wired up to my SQL 2012 test database and the other a flat file. I added a simple Data Flow task to query some data straight out of a table and export it to a text file.
The package ran fine in SSDT. Then, I ran my test console app to load and execute that test package as well. And, it worked just fine (without the extra attribute being required in my new app.config).
In analyzing my original console app and package logged failures, I saw that it failed on the very first task in the package which was an Execute SQL Task. So, in my new package, that I had verified was working, I added a new Execute SQL Task. I set it up to execute a simple stored proc in my database.
With that one additional task, my console app now failed when attempting to execute the package (with the same message about "mixed mode" issues).
I think the only logical conclusion here is that Microsoft must have compiled against a v2 reference... there is nothing I can do to "fix" this other than add the useLegacyV2RuntimeActivationPolicy attribute to my app.config.