.NET Call to SSIS package not running execute sql task

Question

I have a .NET app that calls an SSIS package that moves data from one database server to another database server, and then runs an Execute SQL Task to update data in the original database server. It was originally written for SQL 2005 and ran fine. I updated it to SQL 2012 last week. The first part runs fine - it moves data from one database server to another. However, the Execute SQL Task does not execute.

If I run the SSIS package in using the Execute Package Utility, it runs both without issue.

The .NET code just starts a DTS Application and Package object and then executes. It returns back successful. But the Execute SQL Task does not execute.

I tried removing the first step and leaving the SSIS package to have only the Execute SQL Task and it still doesn't execute. But it still does work in the Execute Package Utility.

Anyone have a similar issue? It would seem that I'm not calling the package from .NET correctly, but part of it runs, so it must be correct there. I can't find any other information on .NET calling the Execute SQL Task object to see if there is something new since 2005 that I need to add.

Should be nothing special with regard to that step firing. I assume there's no precedent constraint preventing the task from firing? What is the statement in the Execute SQL Task? What connection type (OLE, ADO, ODBC)? Are parameters involved? Any chance it's throwing an error and that's why it's not firing? If you replace the Execute SQL query with something safe, like print 'Here' does it work? — billinkc
There was a constraint on the data being moved from one server to the other. That has always run successfully. I removed everything but the Execute SQL Task so there are no constraints. I am using an OLE connection. No parameters. The query is basically 'update x set y = z'. When I run it outside of .NET in the Execute Package Utility, it runs correctly. So it can't be throwing an error in the package . There has to be something with the connection from .NET. I don't think doing the print 'here' would work since the .NET call is not expecting a return value so I won't see if it works. — Jeff Nichols

Jeff Nichols Jeff Nichols · Accepted Answer · 2020-12-02T20:47:16

Found the issue. Unbeknownst to me, SSIS has a logging feature that you must turn on inside the package. Once configured, I ran the .NET process and found an error in the log file that the .NET project was 4.0 and was trying to run 2.0 assemblies. The error was 'The Execute method on the task returned error code 0x80131621 (Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.). The Execute method must succeed, and indicate the result using an "out" parameter.'

I used the following two sites as a guide to fix (setting the 'Generate Serialization Assemblies' from 'Auto' to 'Off' and then adding to the app.config file)

Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime - Researched solutions not working

https://social.msdn.microsoft.com/Forums/en-US/92381c34-7962-49dd-8127-9727dfa60d6f/error-calling-dtsx-package-from-c?forum=sqlintegrationservices

.NET Call to SSIS package not running execute sql task

1 Answers