Useful SSIS Expressions

Change Root Business Unit Name in Dynamics 365 CRM

It is possible to change the root Business Unit Name in Dynamics 365 CRM using SSIS.

We will get the GUID of Business Unit using Advanced Find and Export to Excel.

You’ll need to cast the GUID to a unique identifier in SSIS, with a formula as indicated in the article below, or like this:

(DT_GUID) (“{” +”6ddb52ca-f0df-e711-a95b-000d3a370f0a” + “}”)

Next, we use an SSIS job with one record input including the GUID of the root Business Unit, and a derived column for the “new” name for the Business Unit.

The SSIS job will look like this — see following screenshots.

Convert (Cast) GUID Text String to GUID formatted for import into D365

Convert a GUID text string to a GUID formatted for import to Dynamics 365. You can extract the GUID from a list of users, exported to Excel.

Use the “Derived Column” SSIS component to cast into GUID format.

(DT_GUID) (“{“+[Owner]+”}”)

where [Owner] is the GUID in text format.

T-SQL Case Statement for Status Codes

Dynamics 365 Status Codes are easy once you get used to them. You have to make sure they are aligned such that only the “Open” status reasons (statuscode) are associated with the “Open” status code (statecode), and only the “Completed” status reasons are associated with the “Completed” status code.

Use a simple case statement to get your data ready for upload to Dynamics 365 CRM through the KingswaySoft D365 Destination component.

,CASE [Activity Status]

WHEN ‘Open’ THEN 0

WHEN ‘Completed’ THEN 1

WHEN ‘Canceled’ THEN 2

WHEN ‘Scheduled’ THEN 3

ELSE ‘999’

END AS phl_statecode

,CASE [Status Reason]

WHEN ‘Completed’ THEN 5

WHEN ‘Not Started’ THEN 2

WHEN ‘In Progress’ THEN 3

WHEN ‘Waiting on someone else’ THEN 4

WHEN ‘Deferred’ THEN 7

ELSE ‘999’

END AS phl_statuscode

Note, this works in both Dyanamics 365 (on-premises) and Dynamics 365 online — this example is for online.

Useful SSIS Expressions

Change Root Business Unit Name in Dynamics 365 CRM

Convert (Cast) GUID Text String to GUID formatted for import into D365

T-SQL Case Statement for Status Codes

Bing Chat AI for those Gnarly Excel Formulas

Password Managers: An Easy-to-Implement Tool for Boosting Small Business Security

Multi-Factor Authentication is Great, but Don’t Sleep on Legacy Authentication Methods

Integration of Maps with Dynamics 365 CRM & Power Apps for Enhanced Business Goals

How to Solve the 4 Main Challenges in Landscaping and Construction Marketing and Sales

Overcome These 8 SMB Sales and Marketing Challenges to Grow Your Customer Base

4 Reasons Your B2B Sales and Marketing Strategy is Ineffective

Dynamics 365 On-Premises Notes

Coffee Shop VPN

Power Platform Per User Licenses – 50% Off!

Add a Comment Cancel reply