SSIS 2012: Packages runs in SQL agent, not in BIDS or cmd-line

2

I have a development server that will run packages from SQL Agent (according to schedule, and manually via Job Activity Monitor), but not by running it from BIDS or by two different ways on the command-line.

The error we get in BIDS is:

0xC0010026 at ACME Raise Error: The task has failed to load. The contact information for this task is "".

I also have a production server where the same set of packages can be run in both BIDS and SQL Agent/Job Activity Monitor. If we need to debug these packages, we must do so on production :-o

At one stage, debugging (ie running via BIDS) was working on the development server, for another user. After several weeks' absence, they logged into the server and found it wasn't working. It doesn't work for me, either.

Detail

We want debugging functionality on the development server!

Both servers should be identical. They run Windows 2012, SQL-Server 2012, SSIS 2012, SSMS 2012, Visual Studio 2012 (and ... SQL Server Data Tools for Visual Studio 2012 & Visual Studio 2010 Shell came along for the ride).

Some of the info out there is vague/contradictory, so to be clear, when I say "running in BIDS", what I'm doing is either

  1. Double-click a shortcut labelled SQL Server Data Tools, which brings up Visual Studio 2010 Shell, after which I drag my Portal Data Retrieval.sln file from a Windows Explorer into it. I then pick any of the several packages inside, and either right-click > Execute, or load it in and Debug > Run With Debugging (F5), or Run Without Debugging.
  2. Double-click a shortcut labelled SQL Server Data Tools for Visual Studio 2012, with similar method and similar results.

The full error is

SSIS package "D:\Acme-Dev\KiwiNick\Portal Data Retrieval\G9\G9_CostsFixedForecast_ACMEPortal.dtsx" starting. Error: 0xC0010026 at ACME Raise Error: The task has failed to load. The contact information for this task is "". Error: 0xC0024107 at ACME Raise Error: There were errors during task validation. Error: 0xC0010025 at G9_CostsFixedForecast_ACMEPortal: The package cannot execute because it contains tasks that failed to load. SSIS package "D:\Acme-Dev\KiwiNick\Portal Data Retrieval\G9\G9_CostsFixedForecast_ACMEPortal.dtsx" finished: Failure. The program '[8836] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

This is when I try to run within BIDS.

The other user had tried to re-GAC certain DLLs, but I don't have more detail.

We have recently migrated these packages from SQL-2005. There were a few issues, but they were all resolved, and packages are able to run with the right results (and we can debug on production).

Research

I have tried internet searches similar to

SSIS package runs in SQL agent but "not in BIDS"

or

SSIS error Cannot create a task from XML for task due to error 0x800700C1 "%1 is not a valid Win32 application."

or

SSIS debugging Error: 0xC0010026 The task has failed to load. The contact information for this task

Most of the results have been frustrating to look at, eg a lot of them where the user complains that it does run in BIDS and not in SQL Agent. Or they talk of custom C# Task that fails when moved onto production. Or a 32/64 bit setting. Or a custom task which can be run manually. A lot of the forum threads end without the participants ever locating the cause.

During the research, I discovered the various ways of running a package:

  • within BIDS (with or without debugging) - errors as above
  • [incorrect command-line] On the command-line as dtexec.exe /f G9_CostsFixedForecast_ACMEPortal.dtsx - gives numerous errors
  • [corrected] On the command-line as dtexec.exe /Project bin\Development\G9.ispac /Package G9_CostsFixedForecast_ACMEPortal.dtsx
  • [probably incorrect] Running dtexecui.exe on a command-line, then running the package in the resulting GUI - also gives numerous errors.
  • In SQL Agent according to a schedule - no errors seen, and correct data in database
  • In Job Activity Monitor (right-click > execute from step) - no errors seen, and correct data in database.

I suspect there's something wrong with the development server, but I'm not sure what information to post from the servers - I'd appreciate advice on info I should be providing.

Additional info requested - TAKE 2

User billinkc points out (when is in the project deployment model and is actually using project artifacts @[$Project::ACMEADONET]), a different command-line is required.

D:\Acme-Dev\KiwiNick\Portal Data Retrieval\G9>dtexec.exe /Project bin\Development\G9.ispac /Package G9_CostsFixedForecast_ACMEPortal.dtsx

Microsoft (R) SQL Server Execute Package Utility
Version 11.0.6020.0 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started:  10:54:44 AM
Error: 2017-01-19 10:54:52.40
   Code: 0xC0010026
   Source: ACME Raise Error 
   Description: The task has failed to load. The contact information for this task is "".
End Error
Error: 2017-01-19 10:54:52.40
   Code: 0xC0024107
   Source: ACME Raise Error 
   Description: There were errors during task validation.
End Error
Error: 2017-01-19 10:54:52.40
   Code: 0xC0010025
   Source: G9_CostsFixedForecast_ACMEPortal 
   Description: The package cannot execute because it contains tasks that failed to load.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  10:54:44 AM
Finished: 10:54:52 AM
Elapsed:  7.469 seconds

As you can see, it's (now) quite similar to BIDS' output window. The custom DLL that provides the "ACME Raise Error" logic (ABC_CustomScriptTasks.dll) has also been copied across to the Production server, and is GAC'd in both places (and verified as such in both places). This particular task has Delayed Validation = False in its properties window.

Update 2: The development server was rebooted. No change.

Update 3: Discrepancies in the various copies of ABC_CustomScriptTasks.dll were discovered on the development server (file-size was a dead give-away). All straightened out, and SQL Server Integrations Service (in Computer Management > Services) has been restarted. No change. All copies of ABC_CustomScriptTasks.dll in the production server were all the same (checked by CRC32 from zipping them - yes a little crude). Made no difference.

Update 4: The decision has been made to abandon this server as a development server (will continue to be a deployment point, as it acts as a test environment). A jump-host (which has less fingers fiddling with it) will be used instead. Thanks all who put thought into this question.

sql-server
ssis
ssis-2012
asked on Stack Overflow Jan 17, 2017 by Kiwi Nick • edited Jan 20, 2017 by Kiwi Nick

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0