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.