Using SSIS/KingswaySoft Adapter to Obtain Audit Information
A common request we see is the ability to extract and report on audit data gathered by CRM. If you have done any work or research around this topic, you may have found that it can be tricky. Lucky for you, today's blog will help you solve this issue by teaching you how to use KingswaySoft Adapter to obtain audit information!
If you have worked with SSIS/KingswaySoft Adapter before, you may be tempted to use CRM as a source and then select the Audit entity:
Although you can pull data from the Audit entity, you will not be able to get the column that stores the actual changes made since that column is not exposed and the data that is accessible is not fully usable.
The good news is that obtaining readable data from a CRM Audit is now a lot easier thanks to a feature in KingswaySoft CRM adapter. The new feature is a source type called AuditLogs.
Let's take a look at how it works!
1. Create a new Dynamics CRM Source and select the Source Type as AuditLogs.
2. Now you can use FetchXML to specify the entity that you want to pull data for by adding with the entity and the primary key of that entity:
<fetch mapping="logical" version="1.0">
Keep in mind that you can use FetchXML conditions to pull the desired data set.
3. Refresh your CRM Metadata and click OK.
Now it is time to map your output. You will notice that the CRM source has two outputs:
Once you map the two outputs to their respective destinations, your dts will look like this:
NOTE: We let SSIS create the table for us based on the metadata provided by each outputs.
Finally, you can query the data in the two tables by joining on the auditid column as shown below:
from [dbo].[INT_Audit_PrimaryV2] a
inner join [dbo].[INT_AUDIT_DETAILV2] d
on a.auditid = d.auditid
The results are as follows:
The last four columns give you the value and label or display value for the old and new values in the transaction. These are especially useful when the change involves a lookup or an option set as the value columns will display the GUID or integer respectively while the value_label columns will display the friendly name.
We hope you found today's blog helpful. Keep checking our PowerObjects blog for more tips and tricks, and if you are looking for more information on SSIS, check out other blogs on the topic!
Populating Parent Customer ID with KingswaySoft
When using Microsoft Dynamics CRM, you will come across cases where you might need to populate the Parent Customer ID field using KingswaySoft.
A common mistake that occurs is when people try to use this to populate the parent customer ID field only. If so, they will receive this error message:
"Error: 0xC0047062 at Data Flow Task, Dynamics CRM Destination : System.Exception: An input for 'parentcustomeridtype' field is required in order to process lookup field 'parentcustomerid' correctly. The input value should be one of the following entity names, or its corresponding entity type code in integer format: 'account', 'contact'. (Integer type code is the only option when SOAP 2007 or 2006 is used).
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)"
When you use SSIS the error will be under the insert/update data section as shown below:
In order to solve it, we will need to make sure that the Parent Customer ID type is also being populated. If the parent customer is a contact, the value in the field will be a Contact record; if the parent customer is an account, the value in the field will be an Account record.
We hope this blog was helpful! If you interested in learning more, please check out our other related Dynamics CRM blog topics such as:
Maximizing Data Integration and Migration Performance in Dynamics 365
Data Integration and Data Migration has always been one of the most challenging tasks in any Dynamics 365 implementation. The most common and popular method used within the community is through SQL Server Integration Services (SSIS) along with a third party connector/toolkit from KingswaySoft.
While this is the most popular method, you can sometimes encounter performance issues during the implementation. In this blog, we will show you how to tackle performance issues by optimizing the configuration.
Optimizing Multiple Connection Settings
It's no secret that utilizing ExecuteMultipleRequest (which was introduced when Dynamics CRM 2011 UR12 was released) can drastically improve the performance for bulk data load. Instead of using a single connection to create or update a single record, ExecuteMultipleRequest allows you to create or update more than one record per connection.
By default, the platform only accepts up to two concurrent connections and each connection only accepts up to 1000 records. In some cases, dependent on the type of contract or license you have with Microsoft, you may be able to request for additional connections. If you exceed the number of concurrent connection available, the platform will reject the call and return an error message indicating that the server is busy. Fortunately, ExecuteMultipleRequest is built-in to KingwaysSoft and we just have to set the optimal settings. So what is the best settings?
In KingswaySoft, ExecuteMultipleRequest is indicated by following settings:
1. Number of concurrent connection: Use up to X thread in total
2. Number of records per connection (batch): Batch Size
In the example above, you will use five concurrent ExecuteMultipleRequest connections with a batch size of 30 records. While in theory the maximum batch size is 1000, in real world scenario, you will want to keep the number between 50-100 to maintain performance and stability and avoid timeout. Ideally, you will want the number of connections to be lower than the maximum connections your environment has, since other integrations or migration jobs may be running at the same time and sharing the number of connections with your SSIS job.
Optimizing .NET Client Settings
Although, the SSIS Job has its own connection settings, it is running on top of .NET framework and therefore will be limited by the .NET maximum number of connections settings. This particular setting determines the number of open connections the SSIS Job is going to establish and it may throttle the overall SSIS job performance. You can remove this limitation by adding the following configuration section:
<add address = "*"
maxconnection = "100" />
Add this to your machine.config file located in: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config and C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config
This method should help optimize your configuration. For more Dynamics 365 tips – check out our blog!
Happy Dynamics 365'ing!
How to Migrate Salesforce.com Attachments to Dynamics 365
In order to migrate attachments from Salesforce to Dynamics 365, we could use Kingswaysoft without writing any code through the Developer version.
Kingswaysoft has a premium Derived column tool available that decodes the Attachment encoded string from Salesforce.com and migrates it to Dynamics 365. The license needs to be obtained only to use the SSIS package in production as a scheduled job or through the command line tool.
The process is explained as below:
- Download the Kingswaysoft Productivity tools here.
- Once it is installed, reopen the Visual studio and the Premium tools should appear in the SSIS toolbox as below.
- Drag and drop the Premium Derived Column to the DataFlow.
- Use the DecodeBase64 function and use the field where the attachment is stored. In the below case, it is the field Body from Salesforce.com.
- Make sure the DataType is DT_Image, which will be automatically populated.
- Run the SSIS package after mapping the column with the documentbody field in Annotation entity in Dynamics 365.
- Verify by opening the document/pdf from the associated entity (example case or opportunity or quote). The PDF will open in CRM with the same contents as in Salesforce.com.
Voila! You've migrated your Salesforce.com attachments Dynamics 365!
Happy Dynamics 365'ing!