SSIS Data Flow Components for Salesforce fail when package is executed from command line

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Cloud Applications
Post Reply
JFlanigan
Posts: 11
Joined: Thu 04 Aug 2016 13:32

SSIS Data Flow Components for Salesforce fail when package is executed from command line

Post by JFlanigan » Wed 21 Sep 2016 14:44

I have developed an SSIS package using SSIS Data Flow Components for Salesforce. It runs in Visual Studio. When run from the command line using dtexec, there are errors.

Code: Select all

Started:  10:38:13 AM
Error: 2016-09-21 10:38:13.85
   Code: 0xC00490F7
   Source: Data Flow Task Data Flow Task (Lookup [109])
   Description: The managed pipeline component "Devart.SSIS.Salesforce.SalesforceLookup, Devart.SSIS.Salesforce.130, Version=1.0.0.0, Culture=neutral, PublicKeyToken=09af7300eec23701" could not be loaded.  The exception was: Could not load file or assembly 'Microsoft.SqlServer.DTSRuntimeWrap, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified..
End Error
Error: 2016-09-21 10:38:13.85
   Code: 0xC0047067
   Source: Data Flow Task Data Flow Task (SSIS.Pipeline)
   Description: The "Lookup" failed to cache the component metadata object and returned error code 0x80131600.
End Error
Error: 2016-09-21 10:38:13.85
   Code: 0xC0047076
   Source: Data Flow Task Data Flow Task (SSIS.Pipeline)
   Description: Component Lookup, 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: 2016-09-21 10:38:13.85
   Code: 0xC0048021
   Source: Data Flow Task Data Flow Task (Lookup [109])
   Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "".
End Error
Error: 2016-09-21 10:38:13.85
   Code: 0xC004801F
   Source: Data Flow Task Data Flow Task (SSIS.Pipeline)
   Description: The component metadata for "Lookup" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
End Error
Error: 2016-09-21 10:38:13.85
   Code: 0xC00490F7
   Source: Data Flow Task Data Flow Task (Salesforce Destination [166])
   Description: The managed pipeline component "Devart.SSIS.Salesforce.SalesforceDestination, Devart.SSIS.Salesforce.130, Version=1.0.0.0, Culture=neutral, PublicKeyToken=09af7300eec23701" could not be loaded.  The exception was: Could not load file or assembly 'Microsoft.SqlServer.DTSRuntimeWrap, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified..
End Error
Error: 2016-09-21 10:38:13.85
   Code: 0xC0047067
   Source: Data Flow Task Data Flow Task (SSIS.Pipeline)
   Description: The "Salesforce Destination" failed to cache the component metadata object and returned error code 0x80131600.
End Error
Error: 2016-09-21 10:38:13.85
   Code: 0xC0047076
   Source: Data Flow Task Data Flow Task (SSIS.Pipeline)
   Description: Component Salesforce Destination, 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: 2016-09-21 10:38:13.85
   Code: 0xC0048021
   Source: Data Flow Task Data Flow Task (Salesforce Destination [166])
   Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "".
End Error
Error: 2016-09-21 10:38:13.85
   Code: 0xC004801F
   Source: Data Flow Task Data Flow Task (SSIS.Pipeline)
   Description: The component metadata for "Salesforce Destination" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
End Error
Error: 2016-09-21 10:38:13.86
   Code: 0xC004801F
   Source: Data Flow Task SSIS.Pipeline
   Description: The component metadata for "Lookup, clsid {33D831DE-5DCF-48F0-B431-4D327B9E785D}" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
End Error
Error: 2016-09-21 10:38:13.86
   Code: 0xC004801F
   Source: Data Flow Task SSIS.Pipeline
   Description: The component metadata for "Salesforce Destination, clsid {33D831DE-5DCF-48F0-B431-4D327B9E785D}" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
End Error
Info: 2016-09-21 10:38:13.86
   Code: 0x4004300A
   Source: Data Flow Task SSIS.Pipeline
   Description: Validation phase is beginning.
End Info
Error: 2016-09-21 10:38:13.86
   Code: 0xC001000E
   Source: FIS to Salesforce 
   Description: The connection "{C336E1CB-63D5-42E0-9A01-FBB7528E80B2}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Error: 2016-09-21 10:38:13.86
   Code: 0xC004800B
   Source: Data Flow Task SSIS.Pipeline
   Description: Cannot find the connection manager with ID "{C336E1CB-63D5-42E0-9A01-FBB7528E80B2}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "FIS Source.Connections[IDbConnection]" in the connection manager collection of "FIS Source". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.
End Error
Error: 2016-09-21 10:38:13.86
   Code: 0xC0047017
   Source: Data Flow Task SSIS.Pipeline
   Description: FIS Source failed validation and returned error code 0xC004800B.
End Error
Error: 2016-09-21 10:38:13.86
   Code: 0xC004700C
   Source: Data Flow Task SSIS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2016-09-21 10:38:13.86
   Code: 0xC0024107
   Source: Data Flow Task 
   Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
What configuration is necessary for Data Flow components to run outside of Visual Studio? Note that Visual Studio may not be installed on the run time platform.

Thank you.

Regards,
John

Devart SSIS Data Flow Components v1.5.283 (Salesforce)
Last edited by JFlanigan on Wed 21 Sep 2016 18:11, edited 1 time in total.

JFlanigan
Posts: 11
Joined: Thu 04 Aug 2016 13:32

Re: SSIS Data Flow Components for Salesforce fail when package is executed from command line

Post by JFlanigan » Wed 21 Sep 2016 15:59

I am able to run an otherwise empty package with a "Devart Salesforce Connection Manager" defined from both Visual Studio and the command line (dtexec). When I add a "Devart Salesforce Source" component, I see the following errors (truncated) when I run from the command line:

Code: Select all

Error: 2016-09-21 11:34:33.50
   Code: 0xC00490F7
   Source: Data Flow Task Data Flow Task (Devart Salesforce Source [2])
   Description: The managed pipeline component "Devart.SSIS.Salesforce.Salesforc
eSource, Devart.SSIS.Salesforce.130, Version=1.0.0.0, Culture=neutral, PublicKey
Token=09af7300eec23701" could not be loaded.  The exception was: Could not load
file or assembly 'Microsoft.SqlServer.DTSRuntimeWrap, Version=13.0.0.0, Culture=
neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system
 cannot find the file specified..
End Error
Error: 2016-09-21 11:34:33.50
   Code: 0xC0047067
   Source: Data Flow Task Data Flow Task (SSIS.Pipeline)
   Description: The "Devart Salesforce Source" failed to cache the component metadata object and returned error code 0x80131600.
End Error
Could this issue be 32-bit/64-bit related?
Last edited by JFlanigan on Wed 21 Sep 2016 18:13, edited 1 time in total.

JFlanigan
Posts: 11
Joined: Thu 04 Aug 2016 13:32

Re: SSIS Data Flow Components for Salesforce fail when package is executed from command line

Post by JFlanigan » Wed 21 Sep 2016 17:56

The error reported
Could not load file or assembly Microsoft.SqlServer.DTSRuntimeWrap
occurs with both 32-bit and 64-bit command line.

Similar test packages using ADO NET and OLE DB data sources do not produce this error.

Please open a ticket on this issue.

JFlanigan
Posts: 11
Joined: Thu 04 Aug 2016 13:32

Re: SSIS Data Flow Components for Salesforce fail when package is executed from command line

Post by JFlanigan » Thu 22 Sep 2016 00:00

Confirmed that

Microsoft.SqlServer.DTSRuntimeWrap, Version=13.0.0.0, PublicKeyToken=89845dcd8080cc91

exists in the 32-bit GAC (C:\Windows\Microsoft.NET\assembly\GAC_32).

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: SSIS Data Flow Components for Salesforce fail when package is executed from command line

Post by Shalex » Fri 23 Sep 2016 17:21

I cannot reproduce the error at the moment.

A test environment is Windows 10 (64-bit) with VS2015, SQL Server 2016 (64-bit), Devart SSIS Data Flow Components v1.5.287.

Created a simple Package.dtsx: Devart Salesforce Source -> Flat File Destination. Executed it successfully in Visual Studio.

Opened Command Prompt (C:\Windows\system32\cmd.exe):

Code: Select all

C:\Users\Test>dtexec /Decrypt 11111 /f "C:\_tickets\t=34305_forum\Integration Services Project1\Package.dtsx"
Microsoft (R) SQL Server Execute Package Utility
Version 13.0.1601.5 for 32-bit
Copyright (C) 2016 Microsoft. All rights reserved.

Started:  8:03:42 PM
Progress: 2016-09-23 20:03:43.21
   Source: Data Flow Task
   Validating: 0% complete
End Progress
Progress: 2016-09-23 20:03:48.70
   Source: Data Flow Task
   Validating: 50% complete
End Progress
Progress: 2016-09-23 20:03:48.70
   Source: Data Flow Task
   Validating: 100% complete
End Progress
Progress: 2016-09-23 20:03:48.82
   Source: Data Flow Task
   Validating: 0% complete
End Progress
Progress: 2016-09-23 20:03:48.84
   Source: Data Flow Task
   Validating: 50% complete
End Progress
Progress: 2016-09-23 20:03:48.84
   Source: Data Flow Task
   Validating: 100% complete
End Progress
Progress: 2016-09-23 20:03:48.84
   Source: Data Flow Task
   Prepare for Execute: 0% complete
End Progress
Progress: 2016-09-23 20:03:48.84
   Source: Data Flow Task
   Prepare for Execute: 50% complete
End Progress
Progress: 2016-09-23 20:03:48.85
   Source: Data Flow Task
   Prepare for Execute: 100% complete
End Progress
Progress: 2016-09-23 20:03:48.85
   Source: Data Flow Task
   Pre-Execute: 0% complete
End Progress
Progress: 2016-09-23 20:03:48.87
   Source: Data Flow Task
   Pre-Execute: 50% complete
End Progress
Progress: 2016-09-23 20:03:48.87
   Source: Data Flow Task
   Pre-Execute: 100% complete
End Progress
Progress: 2016-09-23 20:03:49.42
   Source: Data Flow Task
   Post Execute: 0% complete
End Progress
Progress: 2016-09-23 20:03:49.43
   Source: Data Flow Task
   Post Execute: 50% complete
End Progress
Progress: 2016-09-23 20:03:49.43
   Source: Data Flow Task
   Post Execute: 100% complete
End Progress
Progress: 2016-09-23 20:03:49.45
   Source: Data Flow Task
   Cleanup: 0% complete
End Progress
Progress: 2016-09-23 20:03:49.45
   Source: Data Flow Task
   Cleanup: 50% complete
End Progress
Progress: 2016-09-23 20:03:49.45
   Source: Data Flow Task
   Cleanup: 100% complete
End Progress
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  8:03:42 PM
Finished: 8:03:49 PM
Elapsed:  7.297 seconds

C:\Users\Test>
Could you please specify what should be changed in the steps to reproduce the issue you have encountered?

JFlanigan
Posts: 11
Joined: Thu 04 Aug 2016 13:32

Re: SSIS Data Flow Components for Salesforce fail when package is executed from command line

Post by JFlanigan » Tue 15 Nov 2016 16:57

Thank you for investigating this issue.

I see that you used 32-bit. The file

Code: Select all

Microsoft.SqlServer.DTSRuntimeWrap, Version=13.0.0.0, PublicKeyToken=89845dcd8080cc91
only exists in GAC_32. Version 12 exists in both GAC_32 and GAC_64. Is the dependency on version 13 baked in or is there something wrong with our environments?

I need to execute the package in 64-bit mode on a server that has 64-bit drivers installed.

JFlanigan
Posts: 11
Joined: Thu 04 Aug 2016 13:32

Re: SSIS Data Flow Components for Salesforce fail when package is executed from command line

Post by JFlanigan » Wed 16 Nov 2016 11:10

In production, the SSIS project executes on Windows Server 2012 R2 and SQL Server 2014.
Developers use Visual Studio 2015 and SSDT for SQL Server 2014.

A new build of SQL Server 2014 and Analysis Server show 32 and 64-bit versions of Microsoft.SqlServer.DTSRuntimeWrap, Version=12 installed. If your developers are using SQL Server 2016, that would explain why the component is looking for version 13.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: SSIS Data Flow Components for Salesforce fail when package is executed from command line

Post by Shalex » Fri 18 Nov 2016 20:15

The installation of Devart SSIS Data Flow Components for Salesforce deploys the following assemblies on target workstation depending on SQL Server versions available:
=================================================
"C:\Program Files\Microsoft SQL Server\110\DTS\Connections\Devart.SSIS.Salesforce.110.dll"
"C:\Program Files\Microsoft SQL Server\110\DTS\PipelineComponents\Devart.SSIS.Salesforce.110.dll"

"C:\Program Files\Microsoft SQL Server\120\DTS\Connections\Devart.SSIS.Salesforce.120.dll"
"C:\Program Files\Microsoft SQL Server\120\DTS\PipelineComponents\Devart.SSIS.Salesforce.120.dll"

"C:\Program Files\Microsoft SQL Server\130\DTS\Connections\Devart.SSIS.Salesforce.130.dll"
"C:\Program Files\Microsoft SQL Server\130\DTS\PipelineComponents\Devart.SSIS.Salesforce.130.dll"

"C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Connections\Devart.SSIS.Salesforce.110.dll"
"C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents\Devart.SSIS.Salesforce.110.dll"

"C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Connections\Devart.SSIS.Salesforce.120.dll"
"C:\Program Files (x86)\Microsoft SQL Server\120\DTS\PipelineComponents\Devart.SSIS.Salesforce.120.dll"

"C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Connections\Devart.SSIS.Salesforce.130.dll"
"C:\Program Files (x86)\Microsoft SQL Server\130\DTS\PipelineComponents\Devart.SSIS.Salesforce.130.dll"

"C:\Windows\assembly\GAC_MSIL\Devart.Data\" (folder)
"C:\Windows\assembly\GAC_MSIL\Devart.Data.Salesforce\" (folder)
"C:\Windows\assembly\GAC_MSIL\Devart.SSIS.Salesforce\" (folder)

"C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Devart.SSIS.Salesforce.110\" (folder)
"C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Devart.SSIS.Salesforce.120\" (folder)
"C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Devart.SSIS.Salesforce.130\" (folder)
=================================================

Devart.SSIS.Salesforce.dll is designed for SQL Server 2008.
Devart.SSIS.Salesforce.110.dll is designed for SQL Server 2012.
Devart.SSIS.Salesforce.120.dll is designed for SQL Server 2014.
Devart.SSIS.Salesforce.130.dll is designed for SQL Server 2016.

So if you developed your package for SQL Server 2014, but then deployed it on SQL Server 2012, you should open the *.dtsx package with XML Editor and replace "Devart.SSIS.Salesforce.120" with "Devart.SSIS.Salesforce.110".

Does this help?

gsergii
Posts: 2
Joined: Thu 16 Nov 2017 09:58

Re: SSIS Data Flow Components for Salesforce fail when package is executed from command line

Post by gsergii » Thu 16 Nov 2017 10:07

I have a similar issue.
windows 10 x64
installed sql server 2014 express
sql management studio 2017
visual studio 2015
ssdt
devart dotconnect for salesforce, and after installation Devart tools did not appear in SSIS toolbox, so I added file Devart.SSIS.Salesforce.120 to folders
C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Connections\Devart.SSIS.Salesforce.120.dll"
"C:\Program Files (x86)\Microsoft SQL Server\140\DTS\PipelineComponents\Devart.SSIS.Salesforce.120.dll
After that devart tools started to appear in SSIS toolbox and I was able to create a Salesforce Destination, and use Salesforce devart connection. But when executing the package it gave me the following error:
"[SSIS.Pipeline] Error: Cannot find the connection manager with ID "{6913F8B7-B667-4241-B541-1827D6FF4CD1}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "Devart Salesforce Destination.Connections[Devart Salesforce]" in the connection manager collection of "Devart Salesforce Destination". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.
"

What should I check to fix it?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: SSIS Data Flow Components for Salesforce fail when package is executed from command line

Post by Shalex » Fri 17 Nov 2017 11:08

We have answered you at viewtopic.php?t=35839.

JohnGilbert
Posts: 1
Joined: Tue 20 Mar 2018 23:22

Re: SSIS Data Flow Components for Salesforce fail when package is executed from command line

Post by JohnGilbert » Wed 29 Aug 2018 19:39

FYI:
I found this post when looking for answers to a problem I had where I was getting this same exception running my first Devart package.
The issue, (which I finally resolved) had to do with the TargetServerVersion of the Visual Studio project. It was set as 2017, but my SQL Server is 2016. When I changed that value, visual Studio "converted" the packages, but not quite. Inside the .dtsx files, the source controls still referenced the wrong version (.140). I manually changed it to version .130 and that fixed it.

Hope this will help somebody someday save some time.
JG

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: SSIS Data Flow Components for Salesforce fail when package is executed from command line

Post by Shalex » Thu 30 Aug 2018 08:46

Thank you for sharing your experience.

Post Reply