Thank you for watching this video from
Kingswaysoft. Today I will be introducing
the SSIS integration toolkit from
Microsoft Dynamics 365/CRM. The SSIS
integration toolkit from Microsoft
Dynamics 365/CRM is a cost-effective,
easy-to-use and high-performance data
integration solution. It utilizes
Microsoft SQL Server Integration
Services (SSIS) capabilities.
In today's demo, we will show you our
software running in a SQL server
2012 environment. If you are using a
different SQL server version, the
interface will be slightly different.
Also we will be showing you the
integration between two CRM
organizations, which does not have to be
the case for your integration scenario.
In your case, most likely you will use
CRM as a source, and use your other
application or database system as a target.
Or it may be the other way around, with your other application or database system as
a source and have CRM as a target.
With that said, SSIS is capable to integrate
Microsoft Dynamics 365/CRM with
virtually any other applications or
database systems. To get started, let's
create a simple SSIS project.
This tutorial will already assume that
you have the SSIS bits installed,
otherwise you will not see the SSIS
project type here.
In the Business Intelligence template,
select the Integration Services Project
and give your project a name.
Press 'OK' to create the solution. For this
demonstration, we will migrate account
records from the CRM Adventure Works
organization to Contoso. SSIS is a platform that can be used to
implement data migration and application
integration using simple drag-and-drop
features. To begin the SSIS
implementation using our toolkit, we
will create a connection to the
Microsoft Dynamics 365/CRM server. Let's
start by right-clicking the connection
manager area and select the 'New Connection' option.
Select DynamicsCRM and press Add. If you
do not see this option, go back and make
sure that our software is properly
installed on your machine. We are now
prompted to enter the connection
information to the CRM server. The first
option you will notice in the interface is
the authentication type. There are
several different options to choose from
depending on the deployment type of
your CRM server. For instance, if you are
using Partner hosted or an IFD CRM
server, you would choose the 'Federation'
option. If you are using Office 365 CRM Online,
you would select the 'Online Federation'.
In our example, we will use a local
CRM server, so we select 'Active
Directory'.
The following option allows you to
specify the CRM discovery server URL
which you can use to discover your CRM
organizations. The URL will depend on
your CRM server's setup. The 'Service
Endpoint' option specifies how you would
like to connect to the CRM server and
this will depend on the CRM server
version that you are currently using.
The Timeout is set to 120 seconds but
this can be adjusted. In the case where
you would use CRM bulk data load API,
you may need to increase the setting
since the service call would take longer.
In the next area of the form, we must
specify the authentication information
in order to connect to the CRM server. If
you are using a local CRM instance,
you may use the integrated
authentication option. By clicking on the
drop down button in organization, the
toolkit will show all the available
organizations that the user has access
to on the CRM server.
Let's choose Adventure Works.
Let's navigate to the proxy server page
when you can configure proxy server
settings if a proxy may be required.
There are three proxy mode options: No
proxy, Auto-detect and Manual. When the
manual option is selected, the following
fields will be enabled to specify the
proxy server port and
authentication details.
The advance settings page allow you to
specify some advanced connection
settings. In the Home Realm Uri,
you can specify here Uri of the
Cross realm STS metadata endpoint.
This option may be required for certain
deployment such as a situation where you
have multiple identity providers. The CRM
server URL is the actual URL that is
used by the connection manager to mix
service calls to CRM. In the case that
the connection manager is parameterized
using the connection strength property,
CRM server URL can be left empty in
which case our software will
automatically find out the
organization's service URL using CRM
discovery service based on the provided
CRM organization name. If it is
intended to target a different CRM
organization from the design-time setup,
the CRM server URL should be changed in
the connection string. It is not
sufficient enough to only change the
CRM organization name in the
connection string as the CRM server URL
takes precedence. Also in a multi node cluster, you would change the CRM server URL to
specifically target a particular node.
There is also an option for Retry on
Intermittent Errors. This option is
intended to help recover from possible
intermittent outages or disruption of
service so that the integration does not
have to be stopped due to temporary
networking issues. We have designed this
option so that it should only be tried
when it's deemed to be safe to do so, but
there may be exceptions. Lastly, we have a
new feature that allows you to ignore
certificate errors.
Before we hit okay, we should test the
connection to make sure our information
is correct and we can connect
successfully. Since we are connecting to
two CRM organizations, we should rename
this first connection manager for clarity.
Now we'll create another CRM connection
for the destination. This process will be
similar however this time we will choose
a different CRM organization.
We'll rename this to Contoso. The next
step is to use the data flow task to
demonstrate the integration between two
CRM organizations.
If you are using SSIS 2012 or later, you
should automatically see these data flow
components in the toolbox of the data
flow view.
If you do not see any components you can
click the SSIS toolbox button. Note that
you are using SSIS 2008 r2 or earlier
you and need to manually add these
components to the SSIS data flow toolbox.
In the SSIS toolbox, you will see the
components from our SSIS toolkit. The
CRM source component can be used to
read data from the dynamics 365 CRM
server, while the CRM destination
component can be used to write data to
the CRM server. The CRM OptionSet Mapping component
can be used to manage the translation
and mapping of CRM pick-lists OptionSet
values. For the purpose of this
demo, we will look at the source and
destination components first.
Let's start with the CRM source. First
we'll drag the CRM source component
to the surface of the data flow.
Double-click the CRM source component
to bring up its editor form. Here we'll
specify the connection manager, which
will be Adventure Works. The batch size option allows us to
specify how many records we want to
retrieve each time. The default value is
2000 the maximum allowed value is 5000.
The CRM source component supports four
source types. Entity, FetchXML, EntityChanges
and AuditLogs. Your source type
will depend on your business
requirements. Entity allows you to
visually pick which fields you want to
read from CRM entity. FetchXML is an
advanced option that gives you a lot
more flexibility when reading data from
the CRM server. EntityChanges require
CRM 2015 update 1 or later with a change
tracking option enabled for the entity
in Microsoft Dynamics 365 CRM. AuditLogs allow you to retrieve audit data from
the CRM server according to the query
specified.
For the time being we will use Entity as
a source type. We will now have to
specify the source Entity. To do so,
click the drop down button. We will be
prompted to select one of the entities
that's available on the CRM server. For
this demo project, let's choose Account.
The next option allows us to specify a
CRM user account to impersonate. This is
only necessary when you want to read
from special entities which require
impersonation such as the user query
entity. There is also an Output Timezone setting, which
allows you to specify the time zone of
all the datetime fields output.
The default is UTC time, however, you
have two other options that you can use
including adjust the time zone of the
connection user or the impersonation
user. We will use the default. Next you'll
need to specify what columns or CRM
fields you want to read data from. By
default, all CRM fields are selected. This
may not be the best practice you should
only select the fields that you need to
use in a downstream pipeline components.
If we now switch to FetchXML mode, the
component will automatically populate
the fetch XML query based on the
fields that have been selected. You can
also insert SSIS variables to
parameterize this query.
We also support opening files or CRM
views using the toolbar commands. When the
open view option is selected, a select-a-view dialog will appear which can be
used to get saved FetchXML queries in
CRM. The view drop-down list contains
both system views and personal views of
the connection user.
Notice that we have a refreshed CRM
metadata button to refresh the
components to the latest CRM metadata.
This is especially useful for cases
where we have created or modified any
fields in the CRM system for the
specified entity, which were otherwise
required deleting and recreating the
component to reflect the mapping on the
columns page. We're going to click the 'OK'
button to finish configuration of the
source component.
The next step is to create a CRM
destination component. Our goal is to
pull data from one CRM organization
and write to another. You may not
necessarily need to use the CRM
destination components in your scenario
as mentioned before. You may be writing
data into another application or
database system. To illustrate the
purpose of the destination component,
this would be our target component.
Let's drag the CRM destination
component to the data flow design
surface and connect the CRM source to
the CRM destination.
We'll double-click the CRM destination
component to bring up its editor form
and select Contoso as the destination
connection. The next thing you'll need to specify is
the action type. The CRM destination
component offers seven different actions
when writing data to the CRM server. From
this list, you can see some obvious
actions including Create, Update and
Delete. We also have some unique actions
including Upsert, Merge, Convert and
Execute workflow, which are designed for
specific integration needs or scenarios.
The Upsert action performs an update or
insert and offers a robust solution that
helps manage ongoing changes. This action
will depend on whether a match can be
found based on the matching criteria
that you have defined. These four
matching criteria options will also
apply to the update action. The matching
criteria options will be explained later.
Merge will take two CRM records as its
input and perform a CRM merge
action. The convert action can be used
to drive the CRM sales lifecycle by
converting lead records all the way to
invoices. It is also possible to convert
an owner team to an access team. The
execute workflow action can be used to
execute CRM workflow against a large
number of CRM records. It also enables
the capability to schedule the execution
of CRM workflow utilizing SSIS
scheduling capabilities.
In this demonstration, we will select the
Upsert action. Next, we'll need to specify the
destination entity which is the entity
you want to write data into. In our case,
it will be Account.
For the Upsert action, we need to define
an Upsert matching criteria. As mentioned
before, these four matching criteria
options also apply to the update action.
The first matching criteria is primary
key, where we use the CRM record's GUI
ID to identify whether the record is an
existing record in the target system. If
it does not exist, the Upsert action will
create the record. The second option is
CRM duplicate detection.
This requires you to set up the CRM
duplicate detection properly in the
system. This option matches CRM records
based on the CRM duplicate detection
rules. We do not normally recommend using
this option. The third option is manually
specify. When this option is selected it
will require us to go to the columns
page to choose which fields or field we
want to use for matching purposes. Notice
the check box for each field name. If all
the fields chosen match any records in
the system, the components will perform
an update otherwise it will create a new
record. The last option is CRM
alternate key. This option allows you to
uniquely identify a record aside from
the primary key. This relies on the
alternative key being setup in CRM.
Note that this alternate key is a
feature introduced in CRM 2015 update 1
which is not available to a previous
CRM version. For the purpose of this
demonstration, we will choose manually
specify. Now we have to specify how we
would like to handle multiple matches.
The first option is Update All. The
second option is Update One, in which
the component will only update the first
matching record. The third option is
Ignore in which the source row will be
ignored when multiple matches are found.
Lastly, Raise an Error will report an
exception when multiple matches are
found.
We offer a few options that will help
you define your integration features
within our optional settings. These
options are designed to address some
specific integration requirements.
The Remove Unresolvable References
option will perform a check to see if a
lookup reference exists for writing
the CRM server. This option is typically
not recommended due to performance
reasons.
The Send Datetime Values in UTC format
option will indicate that the incoming
datetime values from your source are
already in UTC format.
When this option is not selected, the
input values will be treated as the
current users local time.
The enable CRM Duplicate Detection
option uses CRM duplicate
detection rules to detect if an incoming
record is a duplicate in the CRM
system.
Ignore Null Valued Fields does not post
any null values to the CRM server.
Remove invalid characters will remove
any invalid characters from the input
which can avoid an XML exception when
the component tries to construct the
SOAP request to be sent to the CRM
server.
The Ignore Unchanged Fields is a useful
option when using the Update or Upsert
action. If the incoming value remains
unchanged for a particular field, its
value will not get posted to the CRM
server, which in turn will avoid firing
any unnecessary workflows, plugins or
audits. This option may improve your
performance significantly.
By using a batch size greater than one will specify how many records you
want to submit to the CRM server in a
batch using the Bulk Data Load API. The
limit of the batch size is 1000, but it is
recommend to use a value of 250 or lower.
Using Bulk API will significantly
improve your performance, in particular
for CRM online. In the case that Createdby or Modifiedby fields are mapped in
the destination component and you have
specified a Batch Size it is recommended
that you sort the input rows by these two
fields, as the destination component will
produce the best performance when they
are sorted. This can possibly be done in
your source component if applicable,
alternatively you can use an SSIS
Sort Component to perform the sorting
before sending to the destination
component. Also, when using bulk API, you
may want to consider increasing the timeout
parameter in the Connection Manager as
extra time may be needed to process the
number of requests at CRM server side.
Change Flag Field(s) option can be
enabled when the 'Ignore Unchanged Field'
option is selected. It is used in
special cases to help track or tag for
the last change of the record was
initiated. For instance, if the source of
your SSIS data flow is coming from
your ERP system, you can have it input
value for this field. We will only write
to this field if there are any changes to
other fields when the 'Ignore Unchanged
Fields' option is selected.
Now, let's take a look at the columns
page. As we have selected to perform the
Upsert action on Manually Specified
fields, we need to specify the key
columns to be used for matching. As you
can see, the mapping has already been done
for us based on the name match. Most of the time,
the Upsert matching criteria is based
on one key column but there might be
cases that you use more than one key
column, which is also referred to as the
composite key.
When working with CRM lookup field, often
you know the lookup record's name, but
you don't necessarily know the record's
ID in GUID format. If that's the case, you
can use the Text Lookup feature, and you
will be passing in a text value and
letting the component do the look up
for you by finding the GUID value of
the record. Let's create a test lookup
setting for 'ownerid' field. The first
thing we should do is change the input
from 'ownerid' to 'owneridname' so we are
passing in the owner's name instead of
its ID.
Then we click the button to bring up the
text lookup configuration window.
Notice that we have two entities here, because the CRM 'ownerid' is a lookup
fields that targets the 'systemuser' and
the team entities. Within the lookup
method, we have four options: to use the
Primary Field, Manually Specify, Alternate
Key, or to Opt Out for that specific
target entity. You can choose to perform
lookup using CRM alternate key for the
specific entity. This option can utilize
CRM's Alternate Key for lookup purposes.
Additionally, this option provides the
capabilities to look up on more than one
field as defined by the Alternate
Key selected. In the case that the
Alternate Key is defined by two CRM
fields in the lookup entity, the input
value passed to the lookup field will be
used for the first field of Alternate Key
setup, and you will need to specify an
input for the secondary field.
Let's configure the lookup on the 'fullname'
field for 'systemuser'.
For team entity, we are going to choose the "Use primary field" option. There's also the
option to exclude inactive records from
being used in the lookup process. We can
also choose an optional default value
when there is no match found. When a
default value is entered, it will default
to this particular user when the lookup
of the input value fails. The Text lookup
feature comes with a number of
configurable options which are designed
for specific integration scenarios. You
can configure the lookup's case
sensitivity, by default it is a
case-sensitive lookup. You can also report
errors when there are duplicates. There is
also an option that allows to skip the
record if the lookup fails.
The cache strategy option is important
to note. The text lookup feature involves
populating an internal cache. In the case
that you choose full cache, we will
populate a full cache when the lookup
is first hit. This is recommended when
the number of records in the lookup
entity is relatively small and/or the
primary entity has a large number of
records.
The partial cache mode is recommended in
the case where you are processing a
small number of records, but your lookup
entity contains a large number of
records. The last page is the error handling page,
where there are three error-handling
mechanisms to choose from. The default
option is 'fail on error', where the entire
data flow will fail as soon as an error
occurs. There is also the redirect rows to error
output, where the error output will contain
the failed records with extra columns
such as ErrorCode, ErrorColumn and
ErrorMessage. These three error columns
can usually help you identify why the
error happened. The ignore option is
generally not recommended.
There is also the 'Enable Columns for
Default Output' section which you can
use to enable or disable the additional
columns in the destination component's
Default Output. The 'CrmRecordID' gives
you the newly created CRM record's ID and
'IsNew' tells you if the record is a newly
created one or an existing record that
have been updated. 
Since our version 8.0 release, there is an option to enable
the "Skipped Rows" output, that will contain
any records that have been skipped
during the process. Let's click 'OK' to finish configuring
the component. Lastly, I will add some
dummy "DataReader" Destination Components
to illustrate the error handling page we've
just discussed.
The Default Output contains what was
successful upon executing the task.
Depending on additional columns selected,
it can include 'CrmRecordID' and/or 'IsNew'.
As mentioned before in our recent release,
we have a Skipped Rows output. The Error Output contains those records
that have failed upon executing the task. This option is enabled only when
'Redirect rows to error outputs' is selected. The error output will contain the failed records
with extra columns, like ErrorCode, ErrorColumn and ErrorMessage.
We have now established our data flow. We
can retrieve data from the CRM
Adventure works for organization and
write data to Contoso. Now we can execute
this data flow task.
Going back to Visual Studio, I will
briefly talk about the OptionSet
Mapping Component. You would place this
transformation component in between the
Source and Destination component. Imagine you have an ERP system that has a picklist
or Optionset field, where you need
to migrate or integrate with your CRM
application on an ongoing basis. Your ERP
system has a pre-defined set of option
values for the picklist or Optionset
field, which may be different from what
you have in your CRM system, and you
don't want to change the option values
on either side. In this case, you would
find the Optionset mapping component
useful, as it can help translate any
input value into an option that
Microsoft Dynamics 365 CRM accepts. Let's try to set up an Optionset mapping
component. In this example I am going to
show you a CRM to CRM Optionset
mapping, which does not have to be the
case for your integration scenario.
We're going to break the connection between the source and destination components, so
that we can add an Optionset mapping
component in between. This is a typical
setup for an Optionset mapping component, since it is a transformation component.
Double-click to open the component's
editor window. The input column is the
column from the source system where you
will be receiving data from. It is the
input column where you would want to
translate its text values to CRM Optionset
values. In the target CRM Optionset or Picklist
field area of the form, you will specify
the Destination CRM Connection Manager.
Next, you will select the entity with the
Optionset field that you are working
with. After this, you will need to specify
the Optionset or Picklist Field
which allows you to specify which CRM
Optionset or Picklist field you would
need for the mapping. In the Mapping Page, the top part of the
window has a grid where you can create
mapping values. You can add mapping
entries or delete it.
The import button allows you to import
from a mapping that you have previously
saved or exported, while the export
button allows you to export all mapping
values in the grid to an XML file so you
can edit the file by using a text editor
of your preference. The second part of the window is
'Handling of non-matched values', where you would specify how you would like to
handle values that do not have any
matches in your destination component.
There are five options available. The Create option will create a new
option using the input values for the
specified CRM Optionset or Picklist
field when there is no match found. You
can specify the input column which
contains the internal integer value when
creating the option.
The Replace as an existing option
allows you to specify the option value
to be used if no match has been found.
We have a couple more options that you
can choose from, depending on your
business requirements.
It is worth mentioning that the output
value of the input column from the
Optionset mapping component can be
directly mapped to a CRM Optionset or
Picklist field in the CRM Destination
Component as the field accepts either an
integer value or a label value.
This sums up the basic overview of our
all Optionset mapping Component.
This concludes the demonstration of our
SSIS Integration toolkit for Microsoft
Dynamics 365 CRM components. Thank you
for watching this video. For any further
assistance, please feel free to
contact us.
