Tuesday, 1 April 2014

Case
A while a go I did a post on how to insert, update or delete records in CRM (2013), but what if you want to (de)activate records instead of deleting them to preserve some history in CRM?

Solution
This is a continuation of a previous blog post. Please read it to get more inside details.

1) Start
To deactivate or activate records in CRM, you need the identity id (GUID) of that record. In SSIS you could full outer join your source with a CRM identity view and if the records exists in CRM, but not in your source then you can deactivate the record.

2a) Download CRM SDK
For this example I used CRM 2013 and I downloaded the free Microsoft Dynamics CRM 2013 Software Development Kit (SDK). Execute the downloaded file to extract all the files. We only need the Microsoft.Xrm.Sdk.dll and Microsoft.Crm.Sdk.Proxy.dll assemblies which can be found in the SDK\Bin folder.

SDK download

2b) Windows Identity Foundation
The CRM SDK also requires an installation of Windows Identity Foundation. I used Windows6.1-KB974405-x64 for this example.

3) DLL to SSIS machine
To use the assemblies (DLL) from step 2a in SSIS, you need to add the DLL files to the Global Assembly Cache (GAC) on your SSIS machine. Here is an example for adding to the GAC on Win Server 2008 R2. You also need to copy them to the Binn folder of SSIS: D:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\

4) Parameters
To avoid hardcoded usernames, domainnames, passwords and webservices, I created four project parameters (SSIS 2012). These parameters will be used in the Script Components.

4 parameters, password is marked sensitive.

﻿﻿﻿

5) Script Component - Parameters
Add a Script Component (type destination) for the deactivate. When you edit the Script Component make sure to add the four parameters from the previous step as read only variables.
﻿

Add parameters as read only variables

﻿

6) Script Component - Input Columns
Add the columns that you need as Input Columns. For the deactivation you only need the entity ID from CRM. This is the Technical Id (a guid) from the CRM entity that you want to update.
﻿

Input Columns for deativate

7) The Script - Add assembly
Hit the Edit Script button to start the VSTA editor. In the solution explorer you need to add four references (microsoft.crm.sdk.proxy.dll is for inactivation):

Case
I want to upload files to CRM Annotations with SSIS. I already uploaded contacts, but now I also want to upload their documents. How do I do that?

Solution
You can use the same webservice in a Script Component that you used to insert/update records in CRM. For this example I have a source with a businessKey from my contact, a filename and a filepath. If your source doesn't contain the actual file in a blob, but only a filepath then you can use the Import Column Transformation.
I used a Lookup Transformation to get the identity id from a CRM database view because I need it to reference the document to my contacts.

1b) Windows Identity Foundation
The CRM SDK also requires an installation of Windows Identity Foundation. I used Windows6.1-KB974405-x64 for this example.2) DLL to SSIS machine
To use the assembly (DLL) from step 1a in SSIS, you need to add the DLL to the Global Assembly Cache (GAC) on your SSIS machine. Here is an example for adding to the gac on Win Server 2008 R2. You also need to copy it to the Binn folder of SSIS: D:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\

3) Parameters
To avoid hardcoded usernames, domainnames, passwords and webservices, I created four project parameters (SSIS 2012). These parameters will be used in the Script Components.

4 parameters, password is marked sensitive.

﻿﻿﻿

4) Script Component - Parameters
Add a Script Component (type destination) for adding the files. When you edit the Script Component make sure to add the four parameters from the previous step as read only variables.
﻿

Add parameters as read only variables

﻿

5) Script Component - Input Columns
Add the columns that you need as Input Columns. For inserting the files as annotations you need the blob column containing the actual file, a filename, an entity ID to reference the file to a CRM contact and optionally a subject and/or description.

Input Columns for insert

﻿

6) The Script - Add assembly
Hit the Edit Script button to start the VSTA editor. In the solution explorer you need to add three references: