3

When executed via the SQL Server Agent under the same user context, is there any reason a package would successfully run when using the file system (i.e. DTExec.exe), but fail to execute when launched via the SSIS Catalog (i.e. ISServerExec.exe)?

I have been helping to troubleshoot an SSIS package which fails with an "out of memory" error (generated by a 32-bit PostgreSQL ODBC driver) when launched from the catalog on an SQL Server 2014 server (on both CU4 and CU6).

This package succeeds under the following circumstances:

  • It is executed from Data Tools for BI (DOMAIN\MYUSER)
  • It is executed as an SQL Server Agent Job with a Package source of File system (DOMAIN\MYSERVICEACCOUNT)

It fails under the following circumstances:

  • It is executed from an Integration Services Catalog via right-click -> Execute (DOMAIN\MYUSER)
  • It is executed as an SQL Server Agent Job with a Package source of SSIS Catalog (DOMAIN\MYSERVICEACCOUNT)

There are no configuration overrides in place, the job is running in the same mode (32-bit) in all cases, and all tests are being performed directly on the server. Approximately 20GB of memory is free on the server, and the working set of the package is only around 1GB.

We are using MaxConcurrentExecutables as a workaround, which allows the execution to complete even when run via the IS Catalog, and are configuring 64-bit ODBC drivers. However, I don't understand why the behaviour is different in the first place.

As I understand it, the IS Catalog uses a CLR assembly to launch the package execution via ISServerExec.exe, as opposed to DtsDebugHost.exe (Data Tools), or DTExec.exe (SQL Agent). The app.config files are unmodified from their defaults and I can't see any differences between them.

Why would a package fail only when it is executed via the IS Catalog?

  • 1
    Might be worth a support ticket with MS. Sounds odd to me. Otherwise, I'd try to reduce the memory footprint of the package(s) by reducing the number of concurrent operations, removing async components from data flows, etc but that's only treating the symptom and not the root cause (as you likely already know) – billinkc Apr 2 '15 at 14:44
  • Thanks @billinkc. Switching to 64-bit ODBC drivers was the best workaround for us (and a better long-term solution anyway) and lodging a Microsoft ticket is likely to drag us into a lengthy troubleshooting process for a problem that we're not likely to run into again. It seems like a rare issue but I do hope someone else has run into this before and can provide some information. – Nathan Jolly Apr 8 '15 at 3:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.