September 25, 2018 at 12:23 pm
I have a SQL Agent job that has been running without issue for a number of years. It runs a file-system based SSIS package. The package was developed under SQL Data Tools for 2014.
I have recently updated the package that runs under SQL Agent to also use a custom data flow component.
This updated package runs fine under my development machine in SQL Data Tools (do we still call it BIDS?). On that machine the component is installed in the GAC and also under the SQL SDK pipeline tools folder so that it shows up in the designer.
I deployed the updated package to a separate test server. I installed the custom component to the GAC on that machine.
When I run the updated package from DTEXECui (i.e. double click the package name in Explorer), the package loads and runs to completion including invoking the functionality of the custom component.
However, if I run the component using DTEXEC from a command line, or I execute the package from under the SQL Agent job, I get an error that the component cannot be loaded. Specifically, it states that the component cannot be upgraded, though I do not know why this would a) be called since there is no upgrade and b) would fail.
The custom component is newly developed under .NET4.5 and targeted for SQL2014. It was created with a new name from an existing component that has previously been running well in hundreds of packages, built under .NET 3.5 and targeting SQL 2008 with no issues at all. It has been running for many years. Key point is that these components have different names.
I am at a loss as to why this would work under BIDS and DTEXECui and not under DTEXEC or SQL Agent.
Here is the information reported when run under SQL agent which seems a bit more complete that what I get out of DTEXEC:
Executed as user: XXX\SQLSVC.
Microsoft (R) SQL Server Execute Package Utility Version 12.0.5590.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved.
Started: 1:12:21 PM
Error: 2018-09-25 13:12:21.98 Code: 0xC00490F7 Source: Appointment Appointment (Row Hasher [229])
Description: The managed pipeline component "XXXXXX.SSISBulkLoad.RowHasher2014, SSISBulkLoadRowHasher2014, Version=2.0.6707.33489, Culture=neutral, PublicKeyToken=727996a72800d6c4" could not be loaded.
The exception was: Could not load file or assembly 'SSISBulkLoadRowHasher2014, Version=2.0.6707.33489, Culture=neutral, PublicKeyToken=727996a72800d6c4' or one of its dependencies.
The system cannot find the file specified..
End Error
Error: 2018-09-25 13:12:21.98 Code: 0xC0047067 Source: Appointment Appointment (SSIS.Pipeline)
Description: The "Row Hasher" failed to cache the component metadata object and returned error code 0x80131600.
End Error
Error: 2018-09-25 13:12:21.98 Code: 0xC0047076 Source: Appointment Appointment (SSIS.Pipeline)
Description: Component Row Hasher, clsid {33D831DE-5DCF-48F0-B431-4D327B9E785D} failed to initialize due to error 0xC0047067 "The "%1" failed to cache the component metadata object and returned error code 0x%2!8.8X!.".
End Error
Error: 2018-09-25 13:12:21.98 Code: 0xC0048021 Source: Appointment Appointment (Row Hasher [229])
Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "XXXXXXX".
End Error
Error: 2018-09-25 13:12:21.98 Code: 0xC004801F Source: Appointment Appointment (SSIS.Pipeline)
Description: The component metadata for "Row Hasher" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
End Error
Error: 2018-09-25 13:12:22.09 Code: 0xC004801F Source: Appointment SSIS.Pipeline
Description: The component metadata for "Row Hasher, clsid {33D831DE-5DCF-48F0-B431-4D327B9E785D}" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
End Error
Error: 2018-09-25 13:12:22.21 Code: 0xC0048021 Source: Appointment Row Hasher [229]
Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "XXXXXX".
End Error
Error: 2018-09-25 13:12:22.21 Code: 0xC0047017 Source: Appointment SSIS.Pipeline
Description: Row Hasher failed validation and returned error code 0xC0048021.
End Error
Error: 2018-09-25 13:12:22.21 Code: 0xC004700C Source: Appointment SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2018-09-25 13:12:22.21 Code: 0xC0024107 Source: Appointment
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:12:21 PM Finished: 1:12:22 PM Elapsed: 0.5 seconds. The package execution failed. The step failed.
Here is the perform upgrade method which is the same code under 2008 and does nothing of note:
public override void PerformUpgrade(int pipelineVersion)
{
// Obtain the current component version from the attribute.
DtsPipelineComponentAttribute componentAttribute =
(DtsPipelineComponentAttribute)Attribute.GetCustomAttribute(this.GetType(), typeof(DtsPipelineComponentAttribute), false);
int currentVersion = componentAttribute.CurrentVersion;
// Update the saved component version metadata to the current version.
ComponentMetaData.Version = currentVersion;
}
Here is the GAC information which shows the matching version information to what was reported above:
Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.33440
Copyright (c) Microsoft Corporation. All rights reserved.
The Global Assembly Cache contains the following assemblies:
SSISBulkLoadRowHasher2014, Version=2.0.6707.33489, Culture=neutral, PublicKeyToken=727996a72800d6c4, processorArchitecture=x86
Number of items = 1
I am at a loss to figure out what is wrong here.
Can anyone help?
Are there any gotcha's in going from SQL2008 to SQL2014 (or later) target for a custom pipeline component? And why does it work under BIDS and DTEXECui and not under DTEXEC nor SQL Agent?
TIA
Steve
September 25, 2018 at 1:15 pm
Both BIDS/SSDT and the gui are 32 bit - server is running 64 bit so this is most likely the issue
install the 64 bit version of that component - or run the SSIS package in 32 bit mode (not something that I would normally advise)
September 25, 2018 at 2:34 pm
frederico_fonseca - Tuesday, September 25, 2018 1:15 PMBoth BIDS/SSDT and the gui are 32 bit - server is running 64 bit so this is most likely the issue
install the 64 bit version of that component - or run the SSIS package in 32 bit mode (not something that I would normally advise)
Although SSDT is a 32-bit application, when you debug a package in SSDT it spawns an instance of DTExec.exe in 64-bit mode (unless you have turned on 32-bit debugging).
So I do not think that this will explain the problem.
Note to OP: it's only called SSDT now.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 25, 2018 at 8:46 pm
I found this post: Running SSIS Packages in 64-bit which spoke to running the wrong DTEXEC.
Sure enough. that was the issue. The 64-bit DTEXEC was the one that ran due to the PATH settings. That one failed. When I explicitly ran the 32-bit version (found in \program files (x86)\Microsoft SQL Server\120\DTS\Binn), there was no error and it ran successfully. Then when I checked the "use 32-bit runtime" in the SQL Agent job definition and ran the job it succeeded.
So that's that: 32-bit versus 64-bit run-times.
I'll move forward with the 32-bit run-time for now and when the opportunity presents itself, convert the custom SSIS component to a 64-bit version and switch back to 64-bit.
Thanks for the thoughts on 32-bit versus 64-bit which caused me to find the referenced link and the solution.
Steve
September 26, 2018 at 5:43 am
It's great that you've solved your problem, but I stand by my comment.
The following statement suggests that it was running fine in 64-bit mode too, unless your development machine is running a 32-bit o/s?
This updated package runs fine under my development machine in SQL Data Tools (do we still call it BIDS?). On that machine the component is installed in the GAC and also under the SQL SDK pipeline tools folder so that it shows up in the designer.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply