Microsoft Dynamics 365 – Page 121 – Nishant Rana's Weblog

How to – Apply XSLT transformation using the XML Task in SSIS

Using the XML Task component in SSIS we can apply XSLT to the input XML file.

Recently we had a requirement to read data from XML file and create/update corresponding records in Dynamics 365 CE. For the child entity tags in the XML, we wanted to insert some of the tags (key) from the parent to the child so that it can be easily used for mapping and setting the parent lookup.

For that we had used Foreach Loop Container to read the XML file, then had applied XSLT transformation using the XML Task before processing the file using XML Source Component and CRM Destination Component of KingswaySoft.

The XML task editor here is using 3 File Connection.

One for the input file, another for output and third for the XLST file.

Here need to select

Operation Type as XLST
Source Type as file connection as we are specifying XML file as input.
For output, we are creating an output XML file and overwriting the destination file if existing.
In the Second Operand, we are specifying the XSLT file.

For InputXMLTask file connection, we need to specify the user-defined variable that reads the file in its connection string property for it to work properly.

Same for OutputXMLTask

And for the XSLT File Connection

Let us take a simple example to understand it better à

Suppose below is our source XML i.e. Contact.xml

We want to apply following XSLT transformation to it, which adds the fullname tag to it which is a combination of firstname and lastname tag. i.e. XlsTransform.xslt

Drag the XML task to the control flow of the package.

Specify the following properties for it as shown below

Run the package we will get the below output file, a fullname tag added which is combination of firstname and lastname tag as below i.e. ContactOutput.xml

Hope it helps..

Order of attributes in Alternate Key in Dynamics CRM Destination Component in SSIS (KingwaySoft)

Recently we got the below error in one of our SSIS Packages

error

The packages were working fine in one of the environments and were failing in another.

It was throwing an error in one of the lookup fields that was referring to the alternate key. Here the alternate was defined with 2 fields.

As it turned out in another environment the order of the fields was changed for the alternate key field.

We recreated the alternate key field in the proper order of the attributes to fix this.

Hope it helps..

Using CDS/CRM Connection Manager of KingswaySoft’s SSIS Dynamics 365 Integration toolkit in Script Component (SSIS)

Recently for one of our requirements we had to use the CDS/CRM Connection of KingswaySoft’s SSIS Integration Toolkit for Dynamics 365

Below are the steps to be followed –

Drag the Script Transformation component, specify any input columns and inputs / outputs as required.

In the Connection Managers add the connection to Dynamics 365

Click on Edit Script to open the Visual Studio.

Add references to below 3 assemblies. We need to search for the 2 KingswaySoft assemblies in the machine and then add them.

The sample code –


public override void Input0_ProcessInputRow(Input0Buffer Row)
{
var connMgr = this.Connections.CRMConnectionManager;
var connectionString = (string)connMgr.AcquireConnection(null);
var conn = new CrmConnection(connectionString);

var orgService = (IOrganizationService)conn.GetCrmService();

RetrieveRequest retRequest = new RetrieveRequest();
retRequest.ColumnSet = new ColumnSet();
retRequest.ColumnSet.AddColumn("statuscode");
retRequest.Target = new EntityReference("dxc_contract", "dxc_contractnumberinternal", Row.dxccontractnumberinternal);
var response = (RetrieveResponse)orgService.Execute(retRequest);

if (response.Entity != null)
{
// status is closed
if (((OptionSetValue)response.Entity.Attributes["statuscode"]).Value == 282210002)
{
Row.IsApprovedContract = "N";
}
else
{
Row.IsApprovedContract = "Y";
}
}
}

Get all the details here

http://www.kingswaysoft.com/blog/2013/06/24/Writing-Script-Component-or-Script-Task-using-CRM-Connection-Manager

Hope it helps..

Fixed – Assembly must be registered in isolation error while trying to register plugin \ custom workflow activity in Isolation Mode None in Dynamics 365 On Premise

While trying to register our custom workflow activity from Isolation mode – Sandboxed to None for Dynamics 365 On Premise we got the below error

This was because the user through which we were trying to register the plugin in None Isolation Mode using Plugin Registration Tool was not having Deployment Administrator role.

Adding the user to that Deployment Administrator role through Deployment Manager fixed the issue.

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/on-premises/deployment-administrators

Scribe Insight Limitation – XML Source doesn’t support repeating sibling nodes

Recently we were evaluating Scribe Insight as our Integration tool for one of our clients. The requirement was to read an xml and create records in Dynamics 365 CE.

The basic structure was something like below, with Account as the parent tag and Payment, Assets, Consumer as repeating child nodes of Account i.e. 1 – n.

However, to our surprise when we started defining the same structure in the XML Component within Scribe Workbench we got the below error message à

—————————

XML Component

—————————

Repeating nodes are not supported for cousins, siblings, etc. Multiple repeating nodes must be related linearly to repeating nodes that already exist. Thus, a new repeating node must be a grandparent, parent, child, grandchild, etc., to an existing repeating node.

—————————

Interestingly enough the same limitation doesn’t exist in Scribe Online. Had posted something similar few days’ back on that

https://nishantrana.me/2019/01/14/scribe-online-working-with-hierarchical-data-in-dynamics-365-ce/

As we wanted to go with something that works in On Premise setup we went ahead with SSIS Productivity Pack of KingswaySoft https://www.kingswaysoft.com/products/ssis-productivity-pack which had XML components that works perfectly well with this kind of schema or structure.

Hope it helps..