Recently I was asked to set up a process to automatically disable or re-enable Dynamics 365 Customer Engagement users depending on some external data. This ended up being ridiculously easy to do with SSIS and KingswaySoft's Dynamics 365 Integration Toolkit. Let me show you how it works.

In Dynamics 365 CE, you can disable or enable a user record just by setting the value of its "isdisabled" attribute to true or false, so both my disable user data flow and re-enable user data flow do roughly the same thing.

1. Get a list of Dynamics 365 user records to update.
2. Add a derived column to hold the value to use for updating isdisabled on the user records.
3. Update the user records.

The disable users package

Here's a screenshot of a sample disable users data flow.

Let's take a closer look at each step.

1. Query users using FetchXML.
2. Add a derived column named "isdisabled" and set its value to 1.
3. Update the users.

Enabling the users works exactly the same way, except the value of the "isdisabled" column should be 0 instead of 1, so I won't show the screenshots for that package.

Disable user demo

In my Dynamics 365 online instance, I have an active user named "Angus Alexander" who I want to disable.

When I run the disable users package with the query from above (<condition attribute="firstname" operator="eq" value="angus" />) in Visual Studio, I see success on every step.

I check back in Dynamics 365 to see Angus Alexander is no longer an enabled user.

Instead Angus shows up as a disabled user.

Now when Angus tries to access Dynamics 365, he sees that his account has been disabled.

Last week I posted an approach for generating complex text files from Dynamics 365 with KingswaySoft and XSLT that uses a custom script component to apply an XSL transformation to an XML document generated with the the SSIS XML merge component from the KingswaySoft SSIS productivity pack.

After I shared that post, Daniel Cai at KingswaySoft told me the premium derived column component in the SSIS productivity pack can also be used to apply the XSL transformation. In today's post I will show how I reworked my earlier example to use that approach for a no-code solution.

Assuming you have XML generated like I showed in my previous post, switching from the script component to the premium derived column for the XSLT step is easy. The premium derived column has a few different XML functions that you can see in the screenshot below:

You can pass in XSLT directly to the XslTransform functions, but I've decided to use a variable instead to make this easier to manage. Note that when you set the derived column expression, the length will automatically be set to a length that's based on the length of your XSL. This is almost certainly going to be too short, which will cause your package to fail, so make sure you manually set a more reasonable value, which is 4000 in my example.

As far as the variable, it's just a string variable, but I'd advise you not to set the string value directly. If you do that, getting the formatting right with the line breaks can be tricky. If you set the variable via an expression, getting the formatting right is much easier.

Recently I needed to build an SSIS package that would generate a delimited text file with multiple sets of related (but different entity) Dynamics 365 records combined in a single grouping. Each record would be on a separate row (with different formats for each record type), and each grouping of related records would have its own header and trailer rows. To give you a better idea of what that would look like, consider this example format for listing accounts and related contacts:

HEADER  {accountid}~
ACCOUNT {account name}|{account city}|{account state}~
CONTACT {contact id}|{contact name}|{contact phone number}~
TRAILER {accountid}|{number of contacts}~

A data export from Dynamics 365's sample data would look like this:

HEADER  2023a3fa-5d74-e611-944a-08002751dfcb~
ACCOUNT A. Datum Corporation (sample)|Redmond|WA~
CONTACT 8623a3fa-5d74-e611-944a-08002751dfcb|Rene Valdes (sample)|555-0108~
CONTACT 8c23a3fa-5d74-e611-944a-08002751dfcb|Susan Burk (sample)|555-0111~
TRAILER 2023a3fa-5d74-e611-944a-08002751dfcb|2~
HEADER  1423a3fa-5d74-e611-944a-08002751dfcb~
ACCOUNT Adventure Works (sample)|Santa Cruz|TX~
CONTACT 7a23a3fa-5d74-e611-944a-08002751dfcb|Nancy Anderson (sample)|555-0102~
TRAILER 1423a3fa-5d74-e611-944a-08002751dfcb|1~

I considered using SSIS script components to construct the text output using .Net's normal string manipulation capabilities, but that seemed like it would be a lot of work, and if the output format ever needed to change, I'd need to make changes to my code to handle it. After some pondering, I realized that if I could generate an XML document from the Dynamics 365 records, I would be able to use XSLT to transform it to any kind of text output. Additionally, if the output format ever needed to change, I would only have to update the XSLT stylesheet.

This approach requires three steps:

1. Query the different data sets.
2. Merge them into a single XML document.
3. Convert XML into output format with XSLT.

Querying the data

I'm using the KingswaySoft SSIS integration toolkit to query the Dynamics 365 data. I have a query for active accounts and a separate query for active contacts. Here's the FetchXML for each query:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="name" />
    <attribute name="address1_city" />
    <attribute name="primarycontactid" />
    <attribute name="telephone1" />
    <attribute name="accountid" />
    <attribute name="address1_stateorprovince" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <link-entity name="contact" from="contactid" to="primarycontactid" visible="false" link-type="outer" alias="accountprimarycontactidcontactcontactid">
      <attribute name="emailaddress1" />
    </link-entity>
  </entity>
</fetch>

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="parentcustomerid" />
    <attribute name="telephone1" />
    <attribute name="emailaddress1" />
    <attribute name="contactid" />
    <order attribute="fullname" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="parentcustomerid" operator="not-null" />
    </filter>
  </entity>
</fetch>

Generating the XML

You could generate the XML document using a script component in your data flow, but I'm using the SSIS XML merge component from the KingswaySoft SSIS productivity pack. Note, this component is not included with the Dynamics 365 SSIS integration toolkit, so you will need an additional license (or the KingswaySoft ultimate edition license) to use it.

The XML merge component lets you specify the relationships between the different data sets.

And then you can specify the output format.

The output is a DT_NTEXT column called "MergedXml," but you can change the name.

Converting the XML

SSIS has an XML task component you can use in the package control flow to transform XML with XSLT, but I had some problems getting that to work just the way I wanted. Instead I decided to perform the transformation in a script component inside my data flow. Here's the code from the script component to do that:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
	//build the xsl
	XslCompiledTransform xslt = new XslCompiledTransform();
	string xsl = "<xsl:stylesheet version=\"1.0\" xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\">" +
		"<xsl:template match=\"/\">" +
		"<xsl:for-each select=\"accounts/account\">" +
		"\n" +
		"HEADER  <xsl:value-of select=\"accountid\"/>~\n" +
		"ACCOUNT <xsl:value-of select=\"name\"/>|<xsl:value-of select=\"address1_city\"/>|<xsl:value-of select=\"address1_stateorprovince\"/>~\n" +
		"<xsl:for-each select=\"contacts/contact\">" +
		"CONTACT <xsl:value-of select=\"contactid\"/>|<xsl:value-of select=\"fullname\"/>|<xsl:value-of select=\"telephone1\"/>~\n" +
		"</xsl:for-each>" +
		"TRAILER <xsl:value-of select=\"accountid\"/>|<xsl:value-of select=\"count(contacts/contact)\" />~" +
		"</xsl:for-each>" +
		"</xsl:template>" +
		"</xsl:stylesheet>";
	System.Text.UnicodeEncoding myEncoder = new System.Text.UnicodeEncoding();
	Byte[] bytes = myEncoder.GetBytes(xsl);
	MemoryStream xslms = new MemoryStream(bytes);

	//load it
	xslt.Load(XmlReader.Create(xslms));

	//transform the xml using the xslt
	string outputtext = "";
	string rawxml = BlobColumnToString(Row.MergedXml);
	using (StringReader sr = new StringReader(rawxml))
	{
		using (XmlReader xr = XmlReader.Create(sr))
		{
			using (StringWriter sw = new StringWriter())
			{
				xslt.Transform(xr, null, sw);
				outputtext = sw.ToString();
			}
		}
	}

	//to remove the xml document declaration in the first line, we need to split the output into an array of lines first
	string[] lines = outputtext.Split(new string[] { "\r\n", "\n" }, StringSplitOptions.None);

	//join all the lines except the first one back together
	string outputlines = string.Join("\n", lines, 1, lines.Length - 1); //leave out the first line (xml declaration)

	//add an output row
	Output0Buffer.AddRow();

	//set the value of the output row column to the rejoined output lines value
	Output0Buffer.TransformOutput.AddBlobData(System.Text.Encoding.UTF8.GetBytes(outputlines));
}

/// <summary>
/// converts blob column data to a string - from https://stackoverflow.com/a/14253887
/// </summary>
string BlobColumnToString(Microsoft.SqlServer.Dts.Pipeline.BlobColumn blobColumn)
{
	if (blobColumn.IsNull)
		return string.Empty;

	var blobLength = Convert.ToInt32(blobColumn.Length);
	var blobData = blobColumn.GetBlobData(0, blobLength);
	var stringData = System.Text.Encoding.Unicode.GetString(blobData);

	return stringData;
}

A few notes:

1. You will need to add using directives for these namespaces to your script: System.Xml, System.Xml.Xsl, System.IO.
2. The BlobColumnToString helper method is necessary to read the contents of the MergedXml column into a string variable.
3. Ideally you would store the XSL outside the script code so that it's easier to manage.
4. When you transform the XML, the first line of the output will be an XML document declaration. I am removing that first line of the output with the code starting at line 44 in my sample above.

Last year Ben Hosking said there was no way to move access teams between Dynamics CRM organizations, so I created a tool to do that. Last month Tanguy Touzard said I should turn my console application into an XrmToolBox plugin, so I did. Soon after that Daniel Cai said I should make something with the KingswaySoft SSIS Integration Toolkit, so I've now written a version of my access team template mover as an SSIS package.

The logic

KingswaySoft's Dynamics CRM SSIS adapter makes basic CRM retrieves and inserts easy, so I could have written a simple package to do a full copy of access team templates from a source to a target in about 10 minutes, but I wanted to include the same functionality as my XrmToolBox plugin to enable access teams on the relevant entities in the target organization if desired.

Unfortunately the KingswaySoft adapter doesn't make working with Dynamics CRM metadata easy, so I had to use an SSIS transformation script task to check whether access teams are enabled for a particular entity and enable them.

After the transformation script task checks whether access teams are enabled for the entities in the target organization and (optionally) enables them, a conditional split transformation passes teamtemplate records for entities that are enabled for access teams to a Dynamics CRM destination task to create or update the teamtemplate records in the target organization.

Here's what the data flow task looks like with all the components on the canvas:

Let's take a closer look at all of them.

The retrieve task

The data flow starts with a Dynamics CRM source task to retrieve teamtemplate records from the source organization. Although you could use an entity source type, I use FetchXML to offer greater flexibility. For example it would be possible to only sync access teams that have names starting with a particular string value.

The transformation script task

KingswaySoft's Dynamics CRM adapter supports the use of its connection managers in SSIS script tasks, but there are a few extra steps you need to take that are described in this blog post.

Because SSIS processes the data in a row-by-row fashion, I needed to use slightly different logic in the SSIS package to check for whether entities have access teams enabled and then enable them. In the PreExecute method, the package establishes source/target connections and then retrieves metadata for all the entities in the target system. Next it stores the objecttypecode values for each enabled entity in a list of strings. This actually took me a while to get right, because KingswaySoft returns an entity's logical name as the objecttypecode attribute instead of the actual numeric CRM objecttypecode value.

As the transformation script task loops through each row, it does the following:

1. Check whether the entity is enabled for access teams in the target. If so, set the value of an "enabled" output column to true.
2. If the entity isn't enabled for access teams in the target organization:
   1. If a package parameter to automatically enable access teams is set to true, update the entity in the target organization to enable access teams and set the "enabled" output column to true. Also add the entity's logical name to the list of enabled entities so the script won't unnecessarily update the target system if it encounters another teamtemplate record for the same entity later.
   2. Otherwise, set the "enabled" output column to false.

The conditional split

The conditional split transformation has two outputs - one for rows with the enabled column set to true and one for rows with the enabled column set to false. My version of the package doesn't actually do anything with the "disabled" output, but you could do something with those rows if you wanted.

The upsert

The package creates or updates teamtemplate records in the target organization with a Dynamics CRM destination upsert task.

All the columns are mapped except for the createdby and modifiedby attributes.

Package parameters

As I mentioned earlier, the package supports optionally enabling entities for access teams in the target system. That's done via a package parameter called "enableTargetAccessTeams." The package also has additional parameters for setting the source and target connection strings and passwords.

Wrapping up

You can get the package from my GitHub repository here. I hope to add more packages to this solution for synchronizing other reference data records like business units, teams and queues in the near future. Stay tuned!