The APX -> Dynamics Integration runs an SSIS package in a SQL Agent job on our APX Database server.


  • The package is deployed to the Integration Services Catalog
    • Catalog Name: SSISDB
    • Folder: Dynamics
    • Project: APX Dynamics Integration
    • Package: Dynamics.dtsx
  • The SSIS Package syncs the following entities to Dynamics
    • Firms
    • Companies
    • Investment Goals
    • Cities
    • Contacts
    • Portfolios
    • Joint Owners
  • The package relies on the Kingswaysoft SSIS Dynamics Toolkit to connect to Dynamics 365. More information about this can be found here: KingswaySoft SSIS Dynamics Toolkit
  • The package relies on two SSIS Environments that contain connection information stored in variables
    • DynamicsTest
      • DynamicsSecret -- This secret is used for authenticating to Dynamics from the SSIS package.  This is stored in an App Registration in Azure.  More information about this app registration can be found here: Kingswaysoft Dynamics Integration
      • SSISUser -- This secret is the password for the SQL user "ssis_user" and is used for querying the APX database
      • DynamicsUrl -- This is the URL for the CIMDevIT environment
    • DynamicsProd
      • DynamicsSecret -- This secret is used for authenticating to Dynamics from the SSIS package.  This is stored in an App Registration in Azure.  More information about this app registration can be found here: Kingswaysoft Dynamics Integration
      • SSISUser -- This secret is the password for the SQL user "ssis_user" and is used for querying the APX database
      • DynamicsUrl -- This is the URL for the Confluence Production environment
  • The package relies on the CIM-Communication service as well as the CIM-EmailService in Azure to send notifications and alerts. More information about this process can be found here: Azure Email Service
  • Logs for the integration can be found on the APX Database server. These logs contain error output from individual entities if there is a failure, as well as the message  output from the SQL job/task.:
    • DynamicsTest: C:\Automation\SSIS\Dynamics\Test\
    • DynamicsProd: C:\Automation\SSIS\Dynamics\Prod\