How to Integrate Dynamics CRM with SSIS

by Carmel Schvartzman

1. In this walkthrough we will learn Step-By-Step How to integrate CRM2011 with SSIS through a Web Service in Dynamics CRM 2011 and Sql Server Integration Services.
   We'll call the CRM 2011 WCF Web Service and perform CRUD operations using Indexing on the generic Entity class. We'll consume the CRM WCF Organization (SOAP) service from a class which we will use from inside an SSIS project, but the same logic can also be used by a console application, or a Windows Service, or a Windows Form application.
   We'll not use the early binding Microsoft CRM SDK 2011; instead we'll reach directly the CRM 2011 WCF SOAP endpoint using Late Binding to perform the required CRUD operations.
2. Sql Server Integration Services as implemented through Visual Studio 2008, support only the Framework 3.5 libraries at most. Therefore, we cannot use the CRM SDK and its framework 4.0  assemblies while coding from SSIS 2008. Therefore , we'll reach Crm2011 from SSIS via its Organization Web Service.
3. First, we'll create a .dll which will send requests to the CRM2011 Organization Web Service . Then, we'll create an SSIS project to use that assembly and fetch the CRM data, and also create new entities from SSIS.
4. So let's create a new Class Project in Visual Studio 2010 or 2008, selecting the target framework to be version 3.5:
   
5. Next, add the CRM 2011 Web Service to the project:
   
6. Remember that we'll be using the Organization CRM Web Service:
   
7. After adding the Web Service, rename the Class to CrmInit:
   
8. Create an static method returning an IOrganizationService object. This function will get the host, organization and user logon data:
   
9. Now add the following security standard code for reaching the CRM Web Service :
   
   
   
   
   
   SymmetricSecurityBindingElement security = new SymmetricSecurityBindingElement();
   security.ProtectionTokenParameters = new SspiSecurityTokenParameters();
   HttpTransportBindingElement httpTransport = new HttpTransportBindingElement();
   httpTransport.MaxReceivedMessageSize = Int32.MaxValue ;
   CustomBinding binding = new CustomBinding();
   binding.Elements.Add(security);
   TextMessageEncodingBindingElement encoding =
   new TextMessageEncodingBindingElement(MessageVersion.Soap12WSAddressing10, Encoding.UTF8);
   binding.Elements.Add(encoding);
   binding.Elements.Add(httpTransport);
10. Now append the WCF Endpoint code:
    
    
    
    EndpointAddress endpoint =
    new EndpointAddress(new Uri(string.Format("{0}/{1}/XRMServices/2011/Organization.svc", host, organization)),
    EndpointIdentity.CreateUpnIdentity(string.Format("{0}@{1}", user, "")), new AddressHeader[] { });
    
    
    
    
11. And finally add the code to craete the Client:
    
    
    
    
    
    OrganizationServiceClient client = new OrganizationServiceClient(binding, endpoint);
    client.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential(user, password, "");
12. Check that you have all the "usings" you need:
    
13. Also, check the references:
    
14. Now, in order to test our class, create a new console Project , which will call our CRM assembly:
    
15. Add the following references:
    
16. First, add the Serialization assembly:
    
17. Second, add our assembly:
    
18. Now, code a call to the IOrganizationService:
    
19. Add the corresponding using:
    
20. ... and type the relevant logon data:
    
21. Next, select some columns you want to retrieve, and get the entity object:
    
22. Check in the QuickWatch window that you get an account with the required attributes:
    
    
    
23. Now we want to use the dll, so get to the BIN folder, and get it:
    
24. .. and paste it to some relevant folder where you'll keep it:
    
    
    Also, paste it to the SSIS folder (if you decide to put the assembly inside the GAC, you could skip this step):
    C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn
25. Now it's time to create the SSIS project. Open Visual Studio 2008, and create the project:
    
26. Search in the toolbox for an Script Task:
    
27. Drop the task and rename it:
    
28. Change the project properties:
    
29. .. in order to run the debbugger in 32 bits mode:
    
    
    
30. Now open the editor of the task:
    
31. Go to the Properties window:
    
32. ... and change the target Framework to 3.5:
    
33. Now go to the references tab:
    
34. ... and add a reference to the Serialization assembly:
    
35. ... and to our CRM assembly:
    
36. Add the "using" directive to make visible the assembly's classes:
    
37. Now go to the CRMProxy project, and copy the code we tested inside our Console project:
    
38. Return to the SSIS project and paste the code inside the Main function:
    
39. .. and add a breakpoint:
    
40. Build the Script Task :
    
41. Close the VisualStudio Tool For Applications (VSTA) project, and save the Script Task:
    
42. Debug the SSIS project until the breakpoint is reached:
    
43. When the debugger reaches the breakpoint, check for the account data returned from the Web Service:
    
44. We got the CRM data. Let's now retrieve the Account name:
    
45. Run again the debugger, and check for the returned Account name:
    
46. It seems OK. We fetch CRM2011 data from the SSIS. Now let's create a new Entity record from the SSIS. Add the following code to create a new Competitor record:
    
47. Run the SSIS, and open your CRM2011 to see the new added Competitor:
    
    
    
    
    
    
    This late Entity creation using the "Attributes" ( [""] ) feature, is the result of extending the Entity class to allow for string indexing using the field names. We extended the Entity class in this walkthrough.
    
    That's all...Enjoy Dynamics CRM
    
    
    
    כתב: כרמל שוורצמן
    
    

Step-By-Step How to open a Dialog from Client Side using Javascript

by Carmel Schvartzman

1. In this walkthrough we will learn how to open a Dialog automatically from Client Side, using Javascript and a Web Resource, in Dynamics CRM 2011. A Web Resource is just a virtual file containing html, jscript, css, a picture or Silverlight , stored in the CRM database, and identified by a unique URL. After its creation, a Web Resource can be used in several CRM Forms, enhancing its functionality and appeareance. In our example, we will use a javascript Web Resource to launch a Dialog when an  CRM Form opens:
   
   
2. First, we'll need a javascript code inside a Web Resource. Open the Web Resources:
   
3. ... and create a New Web Resource:
   
4. Give it a Display Name with a "_" to make the Web Resource appear at the top of the Web Resources list, and set the Type to script:
   
5. Open the Text Editor and type the javascript function that will open the Dialog:
   
   The first parameter represents the CRM context, and the second will be the ID of the CRM Dialog we want to launch from the form.
   
   
6. Let's stop the creation of the Web Resource for now, and go to the form of the entity you want to open the dialog from:
   
7. To relate our Web Resource to the form, open the Form Properties:
   
8. Add the  Web Resource to the Form Libraries:
   
   
9. Find and open the Web Resource we just created:
   
10. Next, add it as the Event Handler script that will run on a "OnLoad" event:
    
11. The next step is to set what will be the function that will be called. Be careful to type exactly the same name you saved when you created the Web Resource:
    
    Also, pass the execution context as the function's first parameter.
12. Now we have a little problem: the second parameter must be the ID of the Dialog we want to open. Where we can obtain the GUID of the Dialog? Well, that's a bit tricky. Go to the Dialog you want to launch:
    
13. Open it, and click "Copy a Link" from the Actions menu:
    
    That will copy to the clipboard a URL containing the GUID of the Dialog.
14. Go back to the Event Handler , and paste it to the parameters text box:
    
15. You can see a URL and an "ID" in it: this is the GUID of the Dialog:
    
16. So erase everything else , and close the GUID with quotes:
    
17. Save the form and Publish it. Now we have the parameters we need . Get back to the Web Resource, and type the following code to get the Organization's server URL:
    
18. Next, we need to determine the Entity Name and the ID of the Record represented by the CRM Form, so type the following code:
    
19. Now we build the URL of the window that will open the dialog. Start with the server:
    
20. ...  and link the Entity Name and ID to the URL:
    
21. Finally , type the javascript code that opens the window dialog:
    
22. And we're done...  Let's see how it works: open some Contact:
    
23. ...  And you'll see that the selected CRM 2011 Dialog launchs automatically when the Form opens:
    
    
    
    
    
    
    That's all...Enjoy Dynamics CRM!!!
    
    
    
    כתב: כרמל שוורצמן