Problem

When using the Draycir Accounting Service to connect Credit Hound using you may encounter error running the SQL DTS / SSIS packages in environments where the SQL Server version does not match the version of the assembly used by the accounting service.

Cause / Solution

This issue is caused by the Draycir Accounting Service loading the latest version of Microsoft.SqlServer.ManagedDTS and Microsoft.SqlServer.Dts.DtsClient assemblies installed on the machine, which is different from the version of Microsoft SQL Server that is executing the packages. 

This can be seen in the AccountingServiceHost.log file:

START DATE TIME: 01/26/2017 15:26:53
OPERATING SYSTEM: Microsoft Windows NT 6.2.9200.0
CLR VERSION: 4.0.30319.42000
MACHINE NAME: XXXXXXXXXXXXXXXXXX
USER NAME: Administrator
CURRENT DIRECTORY: 'C:\Windows\system32'
SYSTEM DIRECTORY: 'C:\Windows\system32'
Entry Assembly: AccountingServiceHost (1.4.0.0)
01/26/2017 15:26:53 00000004 INFORMATION: Starting service host. (BaseAddress: 'net.tcp://sage1000trainvm:21359/CreditHoundService/', Mode: Normal)
01/26/2017 15:26:53 00000004 INFORMATION: Loaded service licence key. (Licence: <XXXXXXXXXX><XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX>)
01/26/2017 15:26:53 00000004 INFORMATION: Added service endpoint. (InactivityTimeout: 1.00:00:00, ReceiveTimeout: 1.00:00:00, SendTimeout: 00:00:10)
01/26/2017 15:26:53 00000004 INFORMATION: Added service information endpoint. (InactivityTimeout: 00:01:00, ReceiveTimeout: 00:10:00, SendTimeout: 00:00:30)
01/26/2017 15:26:53 00000004 VERBOSE INFORMATION: Session monitoring initialised. (Check Interval: 1.00:00:00)
01/26/2017 15:26:59 00000008 INFORMATION: Service information created. (Software Version: 1.4.0.6, Compatibility Version: 3.0, Service Contracts: IAccountingService;IServiceInfo, Callback Contracts: IAccountingServiceNotification)
01/26/2017 15:26:59 00000008 VERBOSE INFORMATION: Client verification successful. (CurrentSessionId: urn:uuid:da95753e-c459-4474-a383-790ef64f2497)
01/26/2017 15:26:59 00000008 INFORMATION: Client connected to dataset. (CurrentSessionId: urn:uuid:315f67f4-858d-41c9-9e4a-2fbb321e576b, DataSetId: 9759426b-dc63-41a2-9e63-be9e82c3dc76)
01/26/2017 15:26:59 00000008 INFORMATION: Retrieving company information. (CurrentSessionId: urn:uuid:315f67f4-858d-41c9-9e4a-2fbb321e576b, DataSetId: 9759426b-dc63-41a2-9e63-be9e82c3dc76)
01/26/2017 15:26:59 00000008 VERBOSE INFORMATION: Synchronising company information. (InitiatingSessionId: urn:uuid:315f67f4-858d-41c9-9e4a-2fbb321e576b, DataSetId: 9759426b-dc63-41a2-9e63-be9e82c3dc76)
01/26/2017 15:26:59 00000008 VERBOSE INFORMATION: Performing initial synchronisation operation.
01/26/2017 15:26:59 00000008 INFORMATION: Hooking into assembly resolve event to bind to correct version of SSIS API assemblies.
01/26/2017 15:26:59 00000008 VERBOSE INFORMATION: Attempting to resolve SSIS assembly. (Name: Microsoft.SqlServer.Dts.DtsClient, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91)
01/26/2017 15:26:59 00000008 VERBOSE INFORMATION: Attempting to load assembly using partial name. (Name: Microsoft.SqlServer.Dts.DtsClient, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, Partial name: Microsoft.SqlServer.Dts.DtsClient)
01/26/2017 15:26:59 00000008 VERBOSE INFORMATION: Attempting to resolve SSIS assembly. (Name: Microsoft.SqlServer.Dts.DtsClient)
01/26/2017 15:26:59 00000008 INFORMATION: Assembly loaded using partial name. (Name: Microsoft.SqlServer.Dts.DtsClient, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, Partial name: Microsoft.SqlServer.Dts.DtsClient)
01/26/2017 15:26:59 00000008 VERBOSE INFORMATION: Attempting to resolve SSIS assembly. (Name: Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91)
01/26/2017 15:26:59 00000008 VERBOSE INFORMATION: Attempting to load assembly using partial name. (Name: Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, Partial name: Microsoft.SqlServer.ManagedDTS)
01/26/2017 15:26:59 00000008 VERBOSE INFORMATION: Attempting to resolve SSIS assembly. (Name: Microsoft.SqlServer.ManagedDTS)
01/26/2017 15:26:59 00000008 INFORMATION: Assembly loaded using partial name. (Name: Microsoft.SqlServer.ManagedDTS, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, Partial name: Microsoft.SqlServer.ManagedDTS)
01/26/2017 15:26:59 00000008 ERROR: Failed to synchronise company information. (InitiatingSessionId: urn:uuid:315f67f4-858d-41c9-9e4a-2fbb321e576b, DataSetId: 9759426b-dc63-41a2-9e63-be9e82c3dc76)
01/26/2017 15:26:59 00000008 EXCEPTION:
System.Exception: The package failed to execute.
   at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReaderInThread()
   at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReader(CommandBehavior behavior)
   at g0.a(String A_0)
   at kx.a(fc A_0)
   at b6.a(fc A_0)
   at bc.a(iv A_0)

01/26/2017 15:26:59 00000008 EXCEPTION SOURCE: Microsoft.SqlServer.Dts.DtsClient
01/26/2017 15:26:59 00000008 EXCEPTION TARGET SITE: System.Data.IDataReader ExecuteReaderInThread()

Update the registry

Below are the registry values you’ll need to set to instruct the Draycir Accounting Service to load a specific version of these assemblies.

SQL 2008 & SQL 2008R2

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Draycir\Accounting Service\AssemblyStrings]

"Microsoft.SqlServer.Dts.DtsClient"="Microsoft.SqlServer.Dts.DtsClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

"Microsoft.SqlServer.ManagedDTS"="Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

SQL 2012

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Draycir\Accounting Service\AssemblyStrings]

"Microsoft.SqlServer.Dts.DtsClient"="Microsoft.SqlServer.Dts.DtsClient, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

"Microsoft.SqlServer.ManagedDTS"="Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

SQL 2014

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Draycir\Accounting Service\AssemblyStrings]

"Microsoft.SqlServer.Dts.DtsClient"="Microsoft.SqlServer.Dts.DtsClient, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

"Microsoft.SqlServer.ManagedDTS"="Microsoft.SqlServer.ManagedDTS, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

SQL 2016

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Draycir\Accounting Service\AssemblyStrings]

"Microsoft.SqlServer.Dts.DtsClient"="Microsoft.SqlServer.Dts.DtsClient, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

"Microsoft.SqlServer.ManagedDTS"="Microsoft.SqlServer.ManagedDTS, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

SQL 2017

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Draycir\Accounting Service\AssemblyStrings]

"Microsoft.SqlServer.Dts.DtsClient"="Microsoft.SqlServer.Dts.DtsClient, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

"Microsoft.SqlServer.ManagedDTS"="Microsoft.SqlServer.ManagedDTS, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

SQL 2019

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Draycir\Accounting Service\AssemblyStrings]

"Microsoft.SqlServer.Dts.DtsClient"="Microsoft.SqlServer.Dts.DtsClient, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

"Microsoft.SqlServer.ManagedDTS"="Microsoft.SqlServer.ManagedDTS, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

Restart the Service

Restart the Draycir Accounting Service