In order to install and run SSIS Integration Toolkit for Dynamics Marketing, your system must have the following component(s) installed.
- SQL Server 2016, 2014 or 2012
- SQL Server 2008 R2 or earlier is not supported due to the limitation of Azure Service Bus assembly used
For SSIS runtime, the installation should be done by using the corresponding SQL Server installation media (most likely in CD/DVD format), and you must select the "Integration Services" component during the installation (as shown below).
Note that for SSIS runtime, SQL Server Standard Edition or above is required to run our software. SQL Server Express or Web editions are not supported due to their own limitations.
Note that when using SQL Server 2014, a cumulative update is required (a recent service pack, such as one of the following, is more preferred) in order to run our software during runtime.
- SQL Server 2014 Service Pack 2: https://www.microsoft.com/download/details.aspx?id=53168
- SQL Server 2014 Service Pack 1: https://www.microsoft.com/download/details.aspx?id=46694
For SSIS design-time, you should be installing the version of SSDT (SQL Server Data Tools) or BIDS (Business Intelligence Development Studio) that aligns with the SQL Server version that you plan to use for your final deployment (the runtime).
- When targeting SSIS 2016, you would use SSDT for Visual Studio 2015 available for download at
- Note that this SSDT installation can be used to target SSIS 2014 or 2012 as well.
- When targeting SSIS 2014, you can use either one of the following:
- SSDT for Visual Studio 2015: https://msdn.microsoft.com/mt186501.aspx (Packages created using SSDT 2015 need to have their project's TargetServerVersion setting set to "SQL Server 2014" in order to work with SSIS 2014)
- SSDT-BI for Visual Studio 2013: https://www.microsoft.com/download/details.aspx?id=42313
- When targeting SSIS 2012, you can use any one of the following:
- SSDT for Visual Studio 2015: https://msdn.microsoft.com/mt186501.aspx (Packages created using SSDT 2015 need to have their project's TargetServerVersion setting set to "SQL Server 2012" in order to work with SSIS 2012)
- SSDT-BI for Visual Studio 2012: https://www.microsoft.com/download/details.aspx?id=36843
- You can install the "SQL Server Data Tools" component that is shipped with the SQL Server 2012 installation media (which is based on a Visual Studio 2010 shell)
- "SDK Service" must be enabled and started in Microsoft Dynamics Marketing.
- An Azure Service Bus namespace must have been created to facilitate the integration, along with two queues serving as the Request queue and Response queue respectively. The service bus namespace needs to be an ACS service bus, and proper privileges need to be given to the request queue and response queue in
ACS Management Portal. Instructions on how to create an ACS service bus are available on
MSDN site. As per the instructions mentioned in the link, please use the following PowerShell script to create the service bus.
Add-AzureAccount New-AzureSBNamespace –Name YOUR_NAMESPACE -Location "YOUR_LOCATION" -CreateACSNamespace $true –NamespaceType Messaging
- Windows Installer 4.5
- If you are using Windows Server 2008, Windows Server 2012 family of operating systems or later (including Windows Vista, Windows 7, Windows 8, Windows Server 2012 R2, Windows Server 2016), you do not need to do anything since the latest Windows Installer has been installed by the operating system.
- If you are using Windows Server 2003 family of operating systems (including Windows XP), you should install Windows Installer 4.5 by downloading it from Microsoft website.
When you have confirmed that your system satisfies the above prerequisites, you can navigate to the KingswaySoft website at http://www.kingswaysoft.com to download the installation package. In the download page, you will find two download links. One for x86 systems and one for x64 systems. Make sure to choose and download the right bit for your system.
After you have downloaded the package, you can install the software by following the instructions on your screen.
Setting up a Dynamics Marketing Connection using the Dynamics Marketing Connection Manager
SSIS Integration Toolkit for Dynamics Marketing includes a SSIS Connection Manager component to help you establish connections to the Microsoft Azure Service Buses that are used by Dynamics Marketing.
To add a new Dynamics Marketing connection to your SSIS package, right-click the Connection Manager area, and choose "New Connection..." from the context menu. You will be prompted the "Add SSIS Connection Manager" window, where you can select "Dynamics Marketing" and click "Add..." to create the connection.
The Dynamics Marketing Connection Manager contains the following tabs
- More Info
The Connection tab of the Dynamics Marketing Connection Manager allows you to specify connection string settings.
Note that the configuration of Dynamics Marketing connection manager does not involve the login credentials to your MDM instance. Rather you would enter the Azure Service Bus configuration that is used to facilitate integration for Microsoft Dynamics Marketing. The configuration of the connection manager should reflect how the Integration Services is configured within the Dynamics Marketing application.
- Authentication Type (since v3.0)
Two options available for Azure service bus authentication.
- SAS (Shared Access Signature) Key
- ACS (Access Control Service) Certificate
It is generally recommended that you use SAS Key for authentication purpose. ACS Certificate usually means the connection has all the administrative privileges to the service bus queues, which are usually not necessarily needed, and SAS Key authentication offers better security.
The Microsoft Azure Service Bus Namespace that is configured for integration. Check your Microsoft Dynamics Marketing Integration Settings for this value.
- Issuer Name
The Azure Service Bus Issuer Name, it should be the same as what is specified in Dynamics Marketing Integration Settings area. It is also referred as Shared Access Policy Name which can be found under the namespace's Configure menu in Azure online portal.
- Issuer Key
This can be one of the two keys under the namespace's Configure menu (Primary Key, Secondary Key). This is sometimes referred as the Default Key in Microsoft Azure.
- Request Queue Name
The Request Queue Name is the Service Bus Queue that is configured in your Dynamics Marketing's SDK Service Settings.
- Response Queue Name
The Response Queue Name is the Service Bus Queue that is configured in your Dynamics Marketing's SDK Service Settings.
- Belongs to Company Id
The Belongs to Company Id option is required in order to work with Microsoft Dynamics Marketing custom fields (Contact entity primarily). The value should be a GUID. Leave empty if there are no custom fields or you don't plan to work with custom fields within Contact entity.
- Message Response Timeout (secs)
To help counter the Queue Poll Timeout setting being set too high the Message Response Timeout (secs) setting is used by Source Components to help check the response queue for a response.
- Queue Poll Timeout (secs)
The Queue Poll Timeout (secs) is the maximum amount of time to wait for a response in the Message Response Timeout (secs), after a request has been sent. If you set this value too low, your data flow process may end prematurely because a response has not been generated yet. If you set this value too high, you may experience longer wait times because even though your response is ready for processing, you may end up waiting the maximum amount of time.
- Test Connection
After all the connection information has been provided, you may click "Test Connection" to test if the user can successfully authenticate with Microsoft Azure Service Bus.
Note that if you are having trouble with your connection, you may consider using a different authentication option. You could start by using the the ACS "owner" key, alternatively you can use the shared access key which is available in the SAS section on the service bus's Connection Information window.
The More Info tabs shows some basic information about where to find support and additional information about the toolkit. In this page, you can find the version information of the toolkit.
Using the Dynamics Marketing Source Component
The Dynamics Marketing Source Component allows you to read data from Dynamics Marketing using their SDK. The component allows you to specify a source entity to read from.
The General page is where you will configure most of the setup details.
- Connection Manager
The Dynamics Marketing source component requires an active connection to the Service Buses used by Dynamics Marketing. You can specify a Connection Manager here to facilitate that connectivity.
- Source Entity
Indicates which entity to read from the Source Entity drop down.
- Max Records to Get
This is the maximum number of records that will be retrieved.
- Origin of Change
Some Entities allow you to specify the Origin of Change parameter, which can be used to retrieve records based on the Origin of Change. E.g. you can specify USER to get records that were updated by users using Dynamics Marketing.
The Columns page lists all the available metadata fields for the entity that you wish to read from.
Using the Dynamics Marketing Destination Component
The Dynamics Marketing Destination Component is an SSIS data flow pipeline component that can be used to write data to Dynamics Marketing. You may create or update, and delete records with this component.
The General page of the Dynamics Marketing Component allows you to specify the general settings of the component.
- Connection Manager
The Dynamics Marketing destination component requires an active connection to Dynamics Marketing. You can specify a Connection Manager here to facility that connectivity.
The Action option allows you to specify how the data should be written into Dynamics Marketing. There are two (2) action types available.
- Create or Update - Create or Update record(s) in Dynamics Marketing
- Delete - Delete record(s) from Dynamics Marketing
- Destination Entity
The Destination Entity option allows you to specify which Dynamics Marketing entity to write data to. A drop down with available entities are listed here.
- Refresh Component button
By clicking this button, the component will retrieve the latest metadata from Dynamics Marketing SDK. After clicking this button, you will receive a status message indicating how many fields have been updated, added, or deleted.
- Map Unmapped Fields button
By clicking this button, the component will map any unmapped fields by matching their names with the input columns from the upstream component. This is useful when your source component has recently added more columns, in which case you can use this button to automatically establish associations between input columns and unmapped fields.
- Clear All Mappings button
By clicking this button, the component will remove all field mappings. You can use this button to reset your mappings.
The Columns page of the Dynamics Marketing Destination Component allows you to map the columns from upstream components to fields for the destination entity.
In the Columns page, you will see a grid with four columns as seen above.
- Input Column - You can select a column from your upstream component to be used as the input of the corresponding field.
- Dynamics Marketing Field - The field that you are writing data.
- Data Type - This column indicates the type of value for the current field. Typically you would need to pass in the value using the format indicated in Data Type column.
- Unmap - Clears the value mapped to the Input Column
Error Handling page
The Error Handling page allows you to specify how errors should be handled when they happen.
There are three options available.
- Fail on error
- Redirect rows to error output
- Ignore error
When the Redirect rows to error output option is selected, rows that failed to write to Dynamics Marketing will be redirected to the 'Primary Error Output' of the Destination Component. The 'ErrorMessage' output column found in the Error Output may contain the error message that was reported by Dynamics Marketing or the component itself.
If you need any further assistance with the toolkit, please don't hesitate to contact us.