Saturday, February 11, 2012

Integrating CRM 2011 using SQL Integration Services (SSIS)

I read this article and decided to make something similar for CRM 2011. You can see results in this post.



Datasource
As a datasource for SSIS I used table in Ms SQL 2008 server. Creation script:

CREATE TABLE [dbo].[Contact](
[FirstName] [varchar](100) NOT NULL,
[LastName] [varchar](100) NOT NULL,
[Phone] [varchar](100) NOT NULL,
[Email] [varchar](100) NOT NULL
) ON [PRIMARY]


Proxy assembly

Sql Integration Services 2008 support libraries with target versions of .Net Framework 2.0, 3.0 and 3.5 so it is impossible to use SDK assemblies (which have 4.0 version of .Net Framework). Because of this reason it would be required to use Service reference instead of usual referencing of SDK assemblies.

Open Visual Studio 2008 or 2010, choose new project, select .Net Framework 3.5 as target framework and type of project as Class Library:



Click with right of mouse on References and choose "Add Service Reference":



Input reference to endpoint of your CRM application, fill namespace and click OK:



Rename Class1 which was created by default for the project to CrmHelper. This class would be responsible for instantiation of OrganizationService:



Put the code which instantiates OrganizationServiceClient to CrmHelper class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CrmProxy.Crm;
using System.ServiceModel.Channels;
using System.ServiceModel.Security.Tokens;
using System.ServiceModel;

namespace CrmProxy
{
public class CrmHelper
{
public static IOrganizationService GetCRMService(string ServerHost, string OrgName, string UserName, string Domain, string Pwd)
{
Uri organizationUri = new Uri(string.Format("{0}/{1}/XRMServices/2011/Organization.svc", ServerHost, OrgName));

SymmetricSecurityBindingElement security = new SymmetricSecurityBindingElement();
security.ProtectionTokenParameters = new SspiSecurityTokenParameters();

HttpTransportBindingElement htbe = new HttpTransportBindingElement();
htbe.MaxReceivedMessageSize = 1000000000;

CustomBinding binding = new CustomBinding();
binding.Elements.Add(security);
TextMessageEncodingBindingElement tmebe = new TextMessageEncodingBindingElement(MessageVersion.Soap12WSAddressing10, Encoding.UTF8);
binding.Elements.Add(tmebe);
binding.Elements.Add(htbe);

EndpointAddress address = new EndpointAddress(organizationUri, EndpointIdentity.CreateUpnIdentity(string.Format("{0}@{1}", UserName, Domain)), new AddressHeader[] { });

OrganizationServiceClient osclient = new OrganizationServiceClient(binding, address);
osclient.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential(UserName, Pwd, Domain);

return (IOrganizationService)osclient;
}
}
}


Add to your project class which will contain extension methods for Entity proxy class (I called it Extensions):



Change the namespace of Extensions class to the namespace of Service Reference (like default namespace of project + name of your service reference - in my case CrmProxy.Crm):



Put instead of Extensions class declaration code which will extend methods/properties of Entity class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CrmProxy.Crm
{
partial class Entity
{
public Entity()
{
this.FormattedValuesField = new FormattedValueCollection();
this.RelatedEntitiesField = new RelatedEntityCollection();
}

public T GetAttributeValue<T>(string attributeLogicalName)
{
if (null == this.Attributes) { this.Attributes = new AttributeCollection(); };

object value;
if (this.Attributes.TryGetValue(attributeLogicalName, out value))
{
return (T)value;
}

return default(T);
}

public object this[string attributeName]
{
get
{
if (null == this.Attributes) { this.Attributes = new AttributeCollection(); };
return this.Attributes.GetItem(attributeName);
}

set
{
if (null == this.Attributes) { this.Attributes = new AttributeCollection(); };
this.Attributes.SetItem(attributeName, value);
}
}
}

public static class CollectionExtensions
{
public static TValue GetItem<TKey, TValue>(this IList<KeyValuePair<TKey, TValue>> collection, TKey key)
{
TValue value;
if (TryGetValue(collection, key, out value))
{
return value;
}

throw new System.Collections.Generic.KeyNotFoundException("Key = " + key);
}

public static void SetItem<TKey, TValue>(this IList<KeyValuePair<TKey, TValue>> collection, TKey key, TValue value)
{
int index;
if (TryGetIndex<TKey, TValue>(collection, key, out index))
{
collection.RemoveAt(index);
}

//If the value is an array, it needs to be converted into a List. This is due to how Silverlight serializes
//Arrays and IList<T> objects (they are both serialized with the same namespace). Any collection objects will
//already add the KnownType for IList<T>, which means that any parameters that are arrays cannot be added
//as a KnownType (or it will throw an exception).
Array array = value as Array;
if (null != array)
{
Type listType = typeof(List<>).GetGenericTypeDefinition().MakeGenericType(array.GetType().GetElementType());
object list = Activator.CreateInstance(listType, array);
try
{
value = (TValue)list;
}
catch (InvalidCastException)
{
//Don't do the conversion because the types are not compatible
}
}

collection.Add(new KeyValuePair<TKey, TValue>(key, value));
}

public static bool ContainsKey<TKey, TValue>(this IList<KeyValuePair<TKey, TValue>> collection, TKey key)
{
int index;
return TryGetIndex<TKey, TValue>(collection, key, out index);
}

public static bool TryGetValue<TKey, TValue>(this IList<KeyValuePair<TKey, TValue>> collection, TKey key, out TValue value)
{
int index;
if (TryGetIndex<TKey, TValue>(collection, key, out index))
{
value = collection[index].Value;
return true;
}

value = default(TValue);
return false;
}

private static bool TryGetIndex<TKey, TValue>(IList<KeyValuePair<TKey, TValue>> collection, TKey key, out int index)
{
if (null == collection || null == key)
{
index = -1;
return false;
}

index = -1;
for (int i = 0; i < collection.Count; i++)
{
if (key.Equals(collection[i].Key))
{
index = i;
return true;
}
}

return false;
}
}
}


Sign this assembly because it will be put to GAC:





Build and install this assembly to GAC using drag-drop to C:\Windows\Assembly folder ot gacutil:



SSIS Package

Start new SSIS Project:



Drag-drop Data Flow Task to Control Flow Pane:



Create new Connection to SQL DB which will be used for datasource:








Open Data Flow tab of project and drag and drop Ole DB Source:



Open and configure it:





Drag and Drop Script component to Data Flow tab, choose Transformation type, connect output of Ole DB Source and Script component:




Open script component, choose columns that should be transferred inside it and click Edit Script button to edit script:





In newly opened Visual Studio click with right of mouse on project and choose Properties to change Target Framework of project:




Add reference to assembly that was created step before and System.Runtime.Serialization:



Open main.sc and make following changes:

/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using CrmProxy;
using CrmProxy.Crm;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
IOrganizationService _organizationservice;

public override void PreExecute()
{
_organizationservice = CrmHelper.GetCRMService("http://crm2011", "Contoso", "administrator", "Contoso", "pass@word1");

base.PreExecute();
}

public override void PostExecute()
{
base.PostExecute();
/*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Entity newcontact = new Entity();
newcontact.LogicalName = "contact";
newcontact["firstname"] = Row.FirstName;
newcontact["lastname"] = Row.LastName;
newcontact["telephone1"] = Row.Phone;
newcontact["emailaddress1"] = Row.Email;

_organizationservice.Create(newcontact);
}

}


Save, rebuild and close Visual Studio with script.

That's it and everything is ready to run package.

Source table:


Package processing:


Imported data in CRM:

112 comments:

  1. Great blog post! Really powerful integration. However, I wonder: is this supported?

    ReplyDelete
  2. Thanks, This approach is 100% supported because data is imported using CRM Endpoint.

    ReplyDelete
  3. I couldn't build the Extension Class. Got the following error.
    Extension method must be defined in a top level static class; CollectionExtensions is a nested class

    ReplyDelete
  4. Hello, thanks for the blog. It was really helpful.
    I was able to do insert/updates as well as retrieve of data.

    I still have a problem regarding inserting a lookup value like primarycontactid (or transactioncurrencyid).
    When I try to populate the field with the GUID of the contact (or currency) I receive a type cast error.

    Here is suggested to do the following:
    http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/dcc4f2d6-20e7-4a5c-abad-370083eb7f6c/

    EntityReference primaryContactId = new EntityReference("contact", contactId);
    account["primarycontactid"] = primaryContactId;

    Anyway when I try thiswith the proxy code it is throwing a syntax error.

    I aslo tried this:
    const string strGuid1 = "8EABFCB9-9C66-E111-A571-000C29D6058B";
    Guid gd1 = new Guid(strGuid1);
    EntityReference Contatto = new EntityReference

    {
    LogicalName = "contact",
    Id = gd1
    };

    newaccount["primarycontactid"] = Contatto;

    but unfortunately it is not working as well.
    Can you tell me how you handle with lookup values?

    Thanks a lot
    Marco

    ReplyDelete
    Replies
    1. Hi Marco,
      I too have the same problem. Please let me know if you have cracked it

      Delete
  5. Hello Judith,
    Can you please describe what are you trying to achieve because I was not able to understand what you've meant from your message?

    ReplyDelete
  6. Hi!, This example is very good working with an on-premise environment, but how will you create the client object for an Online environment?

    Regards

    ReplyDelete
  7. Hello,
    I have never tried to make it work with CRM Online but I believe that only authentication part will be changed.

    ReplyDelete
  8. hello,
    This tutorial is great for creating entities.
    However I remain blocked at this stage. For I would make in order to retrieve updates to existing features. A solution starting from this code?

    thank you

    ReplyDelete
  9. For Retrieving of records you can use Retrieve or RetrieveMultiple. For Update - you can use Update message.

    ReplyDelete
  10. Yes I know but the problem is that we already get the GUID of the entity to update. (Retrieve (Entity Name, GUID, attributes)) But what code to use to go find the GUID of an item of my CRM from this script. For there a conflict between CrmProxy.Crm and Microsoft.Xrm.Sdk.

    Do you have a sample code for this from this code?

    Thank

    ReplyDelete
  11. It seems that it is impossible to use .Net 4.0 assemblies (Microsoft.Xrm.Sdk) inside Script Component because it can use .Net 2.0, 3.0 or 3.5. In case you have already developed some code to retrieve id of record you can convert it to be used in scripting component.

    ReplyDelete
  12. I do not have this code without Microsoft.Crm.sdk ..
    Do you own a sample code for this action?

    ReplyDelete
  13. In case you have Visual Studio you will be able to develop this code.

    ReplyDelete
  14. hi there,
    I cant deploy dll to GAC. Can i combine the crm proxy class in script component?
    I got error that the VS2008 dont reconginze the following object:

    OrganizationServiceClient
    IOrganizationService

    ReplyDelete
  15. Hello,
    Why are you unable to deploy assembly to GAC?
    Regarding usage of reference inside Script Component - I tried but I was not able to make it work. You can try.

    ReplyDelete
  16. HI,
    What crm online Authentification code will look like

    ReplyDelete
  17. Hello,
    I haven't tried but you can.

    ReplyDelete
  18. Thank-You,

    could You give me an online authentification sample code.

    ReplyDelete
  19. Welcome, I don't have it so you can do it with yourself.

    ReplyDelete
  20. Hi, I am trying to follow your example. However I am running into an error "The Security Support Interface provider(SSPI) negotiation failed." I tried many different things I found by searching on Google like changing the UPN to SPN and vice versa but in vain. It does not work. Can you please tell me why I am runnning into this error?
    Thanks.

    ReplyDelete
    Replies
    1. Hi, I'm having the same error.

      Did you solve it? if yes, how did you get it working?

      Regards,

      Alex

      Delete
    2. Let me know about resolution! :)

      Delete
    3. change your endpoitn address to
      EndpointAddress address = new EndpointAddress(organizationUri, System.ServiceModel.EndpointIdentity.CreateDnsIdentity(""));

      Delete
    4. The only decision, that worked for me I've posted here:
      http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/6a4b72ee-d7ed-46f8-acd7-b048e881bb9e

      Delete
  21. Do you have an example for OptionSet? I have been trying to connect CRM 2011 via SSIS 2008. But it seems that the XRM metadata is not working properly in the SSIS 2008 environment.

    ReplyDelete
  22. Hi ,

    I followed your method. but I have currency field in crm 2011.
    so In Script Component how to map to currency field from Execl sheet.

    Please help me out.

    ReplyDelete
  23. Hello Siwinjo,
    Can you please show code you are trying to use?

    ReplyDelete
  24. Hello Gangs,
    I can suggest to use RetrieveMultiple to get currencyid based on name or ISO code of your currency column.

    ReplyDelete
    Replies
    1. Hello Andrii,

      Do you have any idea how to RetrieveMultiple to get currencyid.

      Even i am struck in Optionset field also.
      how to map optionset field(crm 2011) from excel sheet in script component.
      please help me.

      Delete
  25. Hi Andrii,

    I followed your example and I was able to create activities. However I am stuck when I wanted to create a reference. Here is my requirement. I want to create an opportunity. So you instantiate the entity using the below statement
    Entity newOpty = new Entity();
    newOpty.LogicalName="opportunity";
    newOpty["name"] = "some title for opty";
    newOpty["customerid"] = new EntityReference(){LogicalName = "contact", Id = new Guid ("xxxxx-xxxx"));
    _organizationservice.Create(newOpty);

    I am getting an error. Pleas let me know how to solve this error

    ReplyDelete
    Replies
    1. Hello Ganesh,
      What kind of error do you get?

      Delete
    2. There was an error while trying to serialize parameter http://schemas.microsoft.com/xrm/2011/Contracts/Services:entity. The InnerException message was 'Type 'CRMProxy.CRM.EntityReference' with data contract name 'EntityReference:http://schemas.microsoft.com/xrm/2011/Contracts' is not expected. Add any types not known statically to the list of known types - for example, by using the KnownTypeAttribute attribute or by adding them to the list of known types passed to DataContractSerializer.'.

      I have no clue about this error. In the same example which you have posted, can you try add a reference to an account with a GUID and see if that works!. Let me know the outcome.

      Delete
    3. Hi Ganesh,

      Did you solve the error? I'm getting the same error as yours nowadays.

      Regards,

      Alex

      Delete
    4. Hi Alex,

      I am still stuck with the same error and not able to find a way out. Keep me posted if you get any way around this.

      Regards
      Ganesh

      Delete
    5. Hi Ganesh,

      In our case we solve it in this way:


      [KnownType(typeof(Money))]
      [KnownType(typeof(Decimal))]
      [KnownType(typeof(EntityReference))]
      [KnownType(typeof(OptionSetValue))]
      partial class Entity
      {
      public Entity()



      so just adding KnownTypes to the Entity of the Extensions.

      Hope it helps,

      Regards,

      Alex

      Delete
    6. This comment has been removed by the author.

      Delete
    7. Hi Alex,

      I got it working now. Thanks for the tip. Have a nice day.

      Entity newopty = new Entity();
      OptionSetValue sales_category = new OptionSetValue();
      sales_category.Value = 867700472;
      newopty.LogicalName = "opportunity";
      newopty["new_sales_category"] = sales_category;
      newopty["description"] = Row.Description;
      newopty["customeridtype"] = 2;
      newopty["customerid"] = new EntityReference() { LogicalName = "contact", Id = new Guid("72DD319A-65B1-E111-90C6-00155D01750A") };
      _organizationservice.Create(newopty);

      Delete
    8. To solve serialization issues, you should add to your Extensions.cs or another proxyclasses file such a lines:

      [System.Runtime.Serialization.KnownTypeAttribute(typeof(OptionSetValue))]
      [System.Runtime.Serialization.KnownTypeAttribute(typeof(EntityReference))]


      public partial class Entity { }


      [System.Runtime.Serialization.KnownTypeAttribute(typeof(OptionSetValue))]
      [System.Runtime.Serialization.KnownTypeAttribute(typeof(EntityReference))]
      public partial class EntityCollection { }


      [System.Runtime.Serialization.KnownTypeAttribute(typeof(EntityReference))]
      [System.Runtime.Serialization.KnownTypeAttribute(typeof(PrincipalAccess))]
      [System.Runtime.Serialization.KnownTypeAttribute(typeof(OptionSetValue))]

      public partial class OrganizationRequest { }


      [System.Runtime.Serialization.KnownTypeAttribute(typeof(EntityReferenceCollection))]//added
      public partial class OrganizationResponse { }


      Note, that it is not the complete variant of proxyclasses, but it is minimum to operate with the OrganizationServiceClient messages.

      Delete
  26. Andrii,

    The service I'm trying to consume is https instead of http and this seems to be causing problems.

    If I leave the URL at https, I get:
    "The provided URI scheme 'https' is invalid; expected 'http'.
    Parameter name: via"

    If I put it at http, I get:
    "There was no endpoint listening at http://URL/XRMServices/2011/Organization.svc that could accept the message. This is often caused by an incorrect address or SOAP action."

    Also, no service was found when I went to XRMServices/2011/Organization.svc. I had to go to XRMServices/2011/Organization.svc?wsdl instead.

    I feel like I'm very close to getting your solution to work, but can't quite make it. I do appreciate all the work you've done converting the 4.0 solution to 2011 though!

    ReplyDelete
    Replies
    1. Hello Phrozt,
      You can get Service Description in other way - you can open CRM - Settings - Customizations - Developer Resources, click Download wsdl Under Organization Service header.

      Delete
    2. Yes, I was able to get the Service Description. I wrote code against it and it was all valid. The problem happened when I tried to run the SSIS package. If the URI was https, it said it expected http. If I put http, it could not process the request.

      Delete
    3. I believe that you issue invoked with following lines of code:

      HttpTransportBindingElement htbe = new HttpTransportBindingElement();
      htbe.MaxReceivedMessageSize = 1000000000;
      binding.Elements.Add(htbe);

      Unfortunately I don't have a lot of free time for investigation but I believe that you should do something with this lines.

      Delete
  27. Hi - I have been successful with the create new contacts, but the crm update command generates an error -

    _organizationservice.Create(newcontact); - SUCCEEDS
    _organizationservice.Update(newcontact); - FAILS

    Script component has encountered an exception in user code:

    Entity Id must be specified for Update

    How/where is the Entity Id added?

    ReplyDelete
    Replies
    1. I found a solution - do a left outer join at the OLE DB Connection source editor, including the GUID.

      Then use this GUID in the script:

      newcontact["contactid"] = Row.GUID;

      _organizationservice.Update(newcontact);

      The Update is then successful - you will need to also add error control as this will fail if you push in NULL GUID values.

      Delete
    2. do a left outer join at the OLE DB Connection source editor, including the GUID. Can u explain more pls

      Delete
  28. We want to insert 5 millions of Records using this approach what are the performance considerations?
    what are the changes that we need to do at both the ends?

    ReplyDelete
    Replies
    1. Do we need to do any changes at SSIS and MS CRM ends?

      Delete
    2. Feel free to use this approach to import data to CRM. I have not clue regarding performance. You don't need to change anything on both sides before import.

      Delete
  29. anybody has code to connect crm2011 online i am getting the error called "Error The provided URI scheme 'https' is invalid; expected 'http'. Parameter name: via"
    Please help its urgent

    ReplyDelete
  30. Hi, could you please explain why did you need extension class and how did you know what to extend?
    Tnx

    ReplyDelete
    Replies
    1. Hello,
      Extension class is added to improve the speed of development.
      In case you would not include extension class work with attributes will become nightmare.

      Delete
  31. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Hello,
      Not sure what you've meant. tryGetValue is method and not object or attribute.

      Delete
    2. Thanks for the response.

      What im only understand is the tryGetValue is not in the collection of the Entity Attributes.

      Delete
  32. Hi Andrii

    I just got this error.

    Instance argument: cannot convert from
    CrmProxy.Crmnew.AttributeCollection' to 'System.Collections.Generic.IList>'
    c:\users\ysraelf\documents\visual studio 2010\Projects\CrmProxy\CrmProxy\Extensions.cs

    is there any body who encouter this kind of problem.

    ReplyDelete
  33. And also this error..

    'CrmProxy.Crmnew.AttributeCollection' does not contain a definition for 'TryGetValue' and the best extension method overload 'CrmProxy.Crmnew.CollectionExtensions.TryGetValue(System.Collections.Generic.IList>, TKey, out TValue)' has some invalid arguments c:\users\ysraelf\documents\visual studio 2010\Projects\CrmProxy\CrmProxy\Extensions.cs 26

    Thanks.

    ReplyDelete
    Replies
    1. You should recheck namespaces.
      Namespace of your Extensions class should be the same as a namespace of your CRM Endpoint.

      Delete
  34. Just starting to set up CRM. I'm trying to add the Service Reference, I needed to use Organization.svc?wsdl, and I'm getting the VS2010 error: "The server was unable to process the request due to an internal error."

    I turned on tracing, and it looks like the CRM error is: "MessageSecurityException: Security processor was unable to find a security header in the message..."

    Do I assume I need to find a way to get my VS2010 to authenticate to CRM? If so, how?

    ReplyDelete
    Replies
    1. Hello,
      You can go other way:
      Open CRM - Settings - Customizations - Developer Resources - Download Organization Service.
      Save this file and add reference in VS to this file.

      Delete
    2. What is this file supposed to look like? Mine starts with the header "Microsoft CRM Error Report", which I assume is not right ...

      Delete
    3. It should look like following:

      <?xml version="1.0" encoding="utf-8"?>
      <wsdl:definitions name="OrganizationService" targetNamespace="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:wsx="http://schemas.xmlsoap.org/ws/2004/09/mex" xmlns:wsa10="http://www.w3.org/2005/08/addressing" xmlns:tns="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlns:wsp="http://schemas.xmlsoap.org/ws/2004/09/policy" xmlns:wsap="http://schemas.xmlsoap.org/ws/2004/08/addressing/policy" xmlns:msc="http://schemas.microsoft.com/ws/2005/12/wsdl/contract" xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:wsam="http://www.w3.org/2007/05/addressing/metadata" xmlns:wsaw="http://www.w3.org/2006/05/addressing/wsdl" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:i0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
      <wsdl:import namespace="http://schemas.microsoft.com/xrm/2011/Contracts/Services" location="http://toughmudder.api.crm.dynamics.com/XRMServices/2011/Organization.svc?wsdl=wsdl0"/>
      <wsdl:types/>
      <wsdl:service name="OrganizationService">
      <wsdl:port name="CustomBinding_IOrganizationService" binding="i0:CustomBinding_IOrganizationService">
      <soap12:address location="http://Organization.api.crm.dynamics.com/XRMServices/2011/Organization.svc"/>
      <wsa10:EndpointReference>
      <wsa10:Address>http://Organization.api.crm.dynamics.com/XRMServices/2011/Organization.svc</wsa10:Address>
      </wsa10:EndpointReference>
      </wsdl:port>
      </wsdl:service>
      </wsdl:definitions>

      Delete
    4. Mine is an HTML file with error information (Internal Error 500 ...)

      Delete
    5. I believe you will have to contact support engineers to solve your issues.

      Delete
    6. iisreset cleared the problem. I can use the GUI to add the service reference.

      Delete
  35. If CRM is running as 'Network Service' how do I authenticate with the SSIS package? Do I need to run CRM as a domain user?

    ReplyDelete
  36. Hello Andrii,
    I am using this approach to import data from a legacy system and it is working great.
    But when I am trying to create Products I am getting this error:
    Required member 'LogicalName' missing for field 'Target'

    I don't see any field 'Target' in the Product entity.

    I cannot find help elsewhere on the Internet, so if you can help me - Thank you in advance.

    Here is my code:
    Entity product = new Entity();
    product["productnumber"] = Row.productid;
    product["name"] = Row.productdesc;

    if (Row.producttype == 1)
    {
    OptionSetValue osv = new OptionSetValue();
    osv.Value = 1; // Sales Inventory
    product["producttypecode"] = osv;

    }

    if (Row.producttype == 2)
    {
    OptionSetValue osv = new OptionSetValue();
    osv.Value = 3; // Services
    product["producttypecode"] = osv;
    }

    Guid uomScheduleId = new Guid("29942CA3-0F03-4F21-BF24-2B9B21D4FA65");
    EntityReference uomSchedule = new EntityReference();
    uomSchedule.LogicalName = "uomschedule";
    uomSchedule.Id = uomScheduleId;
    product["defaultuomscheduleid"] = uomSchedule;

    Guid uomId = new Guid("D5C337EA-58B7-4A20-823C-3DDA117BD4CE");
    EntityReference unit = new EntityReference();
    unit.LogicalName = "uom";
    unit.Id = uomId;
    product["defaultunit"] = unit;

    _organizationService.Create(product);

    ReplyDelete
  37. Hi Andrii

    Now I am developing DynamicsCRM 2011 with SSIS and crmproxy in reference to your information.
    I want to create an Activity(Phonecall).
    So I wrote code as follows :

    ---
    public override void input0_ProcessInputRow(Input0Buffer Row)
    {
    Entity newPhonecall = new Entity();
    newPhonecall.LogicalName = "phonecall";

    EntityReferenceCollection cols = new EntityReferenceCollection();
    cols.Add( new EntityReference { LogicalName = "lead", Id = new Guid(Row.mikomiId) });
    newPhonecall["to"] = cols;
    newPhonecall["directioncode"] = true;
    newPhonecall["subject"] = Row.subjectName;
    newPhonecall["regardingobjectid"] = new EntityReference() { LogicalName = "lead", Id = new Guid(Row.mikomiId) };
    newPhonecall["from"] = new EntityReference() { LogicalName = "systemuser", Id = new Guid("B43BD689-F21B-E211-BF3E-005056913A8A") };
    newPhonecall["ownerid"] = new EntityReference() { LogicalName = "systemuser", Id = new Guid("B43BD689-F21B-E211-BF3E-005056913A8A") };
    _organizationservice.Create(newPhonecall);
    }
    ---

    I executed this SSIS program, so the record was created in ActivityPartyBase table.
    Then ParticipationTypeMask field =8(Regarding) and =9(Owner) were created, but
    =1(Sender) and =2(ToRecipient) record weren't created.

    So, Sender and ToRecipient weren't displayed in the PhoneCall screen.
    Maybe, this program does not set "to" and "from" definitely.

    Please help me !

    Best Regares,
    Todaka from Japan

    ReplyDelete
    Replies
    1. Hello Todaka,
      To understand required format the easiest way is to retrieve already created phonecall record using C# and investigate output. I believe you will find EntityCollection inside from and to fields.

      Delete
    2. Hello Andrii,

      Thank you for your reply.
      I will try EntityCollection.
      But there is little infomation about it with SSIS and crmproxy.
      So if you give further information to me, I thank you very much.

      Best regards,
      Todaka from Japan

      Delete
    3. I could resolve this problem.

      1. I added the next line to crmproxy.
      [System.Runtime.Serialization.KnownTypeAttribute(typeof(EntityCollection))] // added for EntityCollection

      2. I added the next lines to SSIS program.
      Entity ToParty = new Entity();
      ToParty.LogicalName = "activityparty";
      ToParty["partyid"] = new EntityReference() { LogicalName = "lead", Id = new Guid(Row.mikomiId) };
      newPhonecall["to"] = new EntityCollection() { Entities = new Entity[] { ToParty }, EntityName = "activityparty" };

      Entity FromParty = new Entity();
      FromParty.LogicalName = "activityparty";
      FromParty["partyid"] = new EntityReference() { LogicalName = "systemuser", Id = new Guid("B43BD689-F21B-E211-BF3E-005056913A8A") };
      newPhonecall["from"] = new EntityCollection() { Entities = new Entity[] { FromParty }, EntityName = "activityparty" };

      thanks!

      Delete
  38. Any chance you can do this over with VS2012/SQL2012 using .net 4.0 and xrm?

    ReplyDelete
    Replies
    1. Hello,
      It could work but I haven't tried it yet.

      Delete
  39. Hey Andrii,

    Great blog, i wish if i can get it working like rest of the folks around :(
    Thanks in advance, you have always been very helpful at social.crm development forum.

    I am following your steps and i am trying to connect to our live server, so basically moving data to our Live CRM server. (Production environment).

    I am getting following error, can you please point in right direction, this is my first time in the world of DTS and SSIS :).

    Our CRM server is using HTTPS but this code doesn't like HTTPS so first i got following error:
    "The provided URI scheme 'https' is invalid; expected 'http'.

    On changing it to HTTP, i get following error now:

    An error occurred while receiving the HTTP response to http://URL/XRMServices/2011/Organization.svc. This could be due to the service endpoint binding not using the HTTP protocol. This could also be due to an HTTP request context being aborted by the server (possibly due to the service shutting down). See server logs for more details.

    Server stack trace:
    at System.ServiceModel.Security.IssuanceTokenProviderBase`1.DoNegotiation(TimeSpan timeout)
    at System.ServiceModel.Security.SspiNegotiationTokenProvider.OnOpen(TimeSpan timeout)
    at System.ServiceModel.Security.WrapperSecurityCommunicationObject.OnOpen(TimeSpan timeout)
    at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
    at System.ServiceModel.Security.SecurityUtils.OpenCommunicationObject(ICommunicationObject obj, TimeSpan timeout)
    at System.ServiceModel.Security.SymmetricSecurityProtocol.OnOpen(TimeSpan timeout)
    at System.ServiceModel.Security.WrapperSecurityCommunicationObject.OnOpen(TimeSpan timeout)
    at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
    at System.ServiceModel.Channels.SecurityChannelFactory`1.ClientSecurityChannel`1.OnOpen(TimeSpan timeout)
    at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
    at System.ServiceModel.Channels.ServiceChannel.OnOpen(TimeSpan timeout)
    at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
    at System.ServiceModel.Channels.ServiceChannel.CallOnceManager.CallOnce(TimeSpan timeout, CallOnceManager cascade)
    at System.ServiceModel.Channels.ServiceChannel.EnsureOpened(TimeSpan timeout)
    at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
    at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
    at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

    Exception rethrown at [0]:
    at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
    at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
    at CrmProxy.Crm.IOrganizationService.Create(Entity entity)
    at CrmProxy.Crm.OrganizationServiceClient.Create(Entity entity)
    at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
    at UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
    at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)


    It looks like our server only accepts HTTPS, how do i get to work?
    Please help!!!!

    Thanks
    Sam



    ReplyDelete
  40. _organizationService.Create is done works great .Can u give a sample code for
    _organizationService.Delete and _organizationService.update

    ReplyDelete
    Replies
    1. Hello,

      For Update following code should work:

      Entity newcontact = new Entity();
      newcontact.LogicalName = "contact";
      newcontact["firstname"] = Row.FirstName;

      newcontact.Id = ;


      _organizationservice.Update(newcontact);

      and for Delete:

      _organizationservice.Delete("contact", );

      Delete
    2. for update Should I add newcontact.Id = Guid("xxxxxx"); right??

      Delete
    3. When I do like that I m getting " The method or operation is not implemented " error

      I guess my guid Id is not right .

      I also want u one more thing Can u pls share or explain guid id's screenshot

      Delete
    4. Hello,
      You can use syntax similar to:
      record.Id = new Guid("99964D46-F698-E111-B802-00155D160B09");

      Delete
    5. anybody is capable of connect to CRM 2011 online using SSIS 2008 i am able to connect to CRM on premise by this post but not able to connect to CRM 2011 Online from SSIS using CRMProxy if some one have done please give step by step process
      Thanks in Advance

      Delete
    6. I haven't tried. What issues do you have?

      Delete
    7. when i tried to connect i get the error as "There was no endpoint listening at https://home89.api.crm.dynamics.com/home/XRMServices/2011/Organization.svc that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details."

      and inner exception i get as "The remote server returned an error: (404) Not Found."
      Please help

      Delete
    8. Hello, Your URL is wrong. I believe it should be like https://home89.api.crm.dynamics.com/XRMServices/2011/Organization.svc instead of https://home89.api.crm.dynamics.com/home/XRMServices/2011/Organization.svc.

      You will have to rewrite your assembly.

      Delete
    9. hey Andrii thanks for reply but can you please let me know that what needs to be change from this post code?
      i have tried changing my code and now i am getting this error "Secure channel cannot be opened because security negotiation with the remote endpoint has failed. This may be due to absent or incorrectly specified EndpointIdentity in the EndpointAddress used to create the channel. Please verify the EndpointIdentity specified or implied by the EndpointAddress correctly identifies the remote endpoint."

      and innerexception says "An error occurred when verifying security for the message."
      Please help

      Delete
  41. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Hello krunal,

      I have the same issue, Did you solve your dynamics online authentication ?

      please help me !

      Thanks,
      BRMD.

      Delete
    2. Hello,

      I don't use self-developed SSIS packages anymore because I discovered for myself following solution - http://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-crm

      Kind regards,
      Andrii.

      Delete
    3. Hello,

      I don't use self-developed SSIS packages anymore because I discovered for myself following solution - http://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-crm

      Kind regards,
      Andrii.

      Delete
  42. Impressing! Followed the tutorial and finally the KingwaySoft stuff. Andrii, thank you.
    I have the following scenario: once in a day some service runs on the side of our main system, extracts
    updates into an XML file which is put on a file system.

    SSIS picks up this file, extracts identificators, fetches the matching entities, updates this entities and saves those back to CRM.

    Since I am new to SSIS(two days of experience) I need some general advice - what pattern to use. Should I use XML Task or XML file as a source?

    Заранее благодарю


    ReplyDelete
    Replies
    1. Hello Greg,

      As far as I understood Xml DataSource should work.

      Kind regards,
      Andrii.

      Delete
    2. I am trying to "read" data from CRM, of SSIS 2008. Obviously I can use the 3.5 dialect only. I found your example of how to script source from CRM in SSIS 2012, but this didn't work for me. May be there is some tiny stupid detail I am missing, but I cannot create query. I mean I can, but when the matter stops once I am coming to creation of Conditions.

      The query.Criteria.Conditions doesn't have the Add method. What's wrong here?

      TY

      Delete
    3. Hello,

      Have you tried to use Kingway SSIS Integration Adapter - http://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-crm

      Kind regards,
      Andrii.

      Delete
    4. Sure I did. It's an amazing plugin, but the point is that I am not a freelancer and my management was advised by a consultant that there is no need to buy it. I know on my skin that it is extremally cost effective plugin, I tried it in developers mode. But the reality is that in our company this is the first attempt to adopt MS CRM and none of us is really experienced with it. This is why the consultants credibility is higher than ours. And we realize the fact that the consultant not using the Kingsway charges us higher. Life is hard. Anyhow, I am just curious, why the Conditions dropped Add method? Sure I worked around but didn't like the idea.

      Delete
    5. Ok, got your problem.

      Not sure why there is no such method. What I can suggest is instead of using Ad method try to create collection of conditions and assign it to a property Conditions directly.

      PS try to buy this adaptor because it will solve a lot of issues now and in future.

      Kind regards.
      Andrii.

      Delete
    6. This is exactly what I did :-) Actually I don't see any other way. Management doesn't see a reason to buy as it is just one time task, to integrate the existing system with the MS CRM. Dunno - may be they're right.

      Delete
    7. Adrii, sorry for bugging you.
      I am using your Proxy class to extract entity from CRM. When I build the query among the columns I request one which is of OptionSet type. Script fails with the following:

      System.ServiceModel.Dispatcher.NetDispatcherFaultException: The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://schemas.microsoft.com/xrm/2011/Contracts/Services:RetrieveMultipleResult. The InnerException message was 'Error in line 1 position 1058. Element 'http://schemas.datacontract.org/2004/07/System.Collections.Generic:value' contains data of the 'http://schemas.microsoft.com/xrm/2011/Contracts:OptionSetValue' data contract. The deserializer has no knowledge of any type that maps to this contract. Add the type corresponding to 'OptionSetValue' to the list of known types - for example, by using the KnownTypeAttribute attribute or by adding it to the list of known types passed to DataContractSerializer.'. Please see InnerException for more details.

      I added :
      [KnownType(typeof(OptionSetValue))]
      just above your partial class Entity

      This didn't work out. Not sure where and how this attribute should be added.

      Delete
    8. Ok, just a matter finding the right code :-)
      Added this:
      [System.Runtime.Serialization.KnownTypeAttribute(typeof(EntityReference))]
      [System.Runtime.Serialization.KnownTypeAttribute(typeof(OptionSetValue))]

      Works!

      TY anyways!

      Delete
    9. Now my challenge is to get metadata for the specific OptionSet. Do you guys have any ideas or leads?

      Delete
    10. Hello,
      You should try to use OrganizationRequest and fill RequestName with RetrieveAttribute and fill collection items EntityLogicalName, LogicalName and RetrieveAsIfPublished.

      Delete
    11. It worked, thanks. Just wanted to mention that I had to add

      [System.Runtime.Serialization.KnownTypeAttribute(typeof(OptionSetMetadata))]
      public partial class OrganizationResponse { }

      It was done in the Extensions.cs

      Delete
    12. Guys, I have another question. I have a task which extract optionsetmetadata. I don't want to hit the CRM again and again for getting it. I'd like to use caching here. Is there any good practices?

      Delete
    13. Hello,

      Not sure how to organize cashing but I believe it would not harm productivity of your CRM Instance.

      Kind regards,
      Andrii.

      Delete
    14. Looks like this is my last question: I am going to keep credentials for an CRM account in the .dtsConfig file. What is the proper way to protect this data? Can I encrypt .dtsConfig file like the web.config?

      Delete
    15. Hello,

      Regarding this point I'm not sure. You should check search engines. I believe you will find the best approach there.

      Kind regards,
      Andrii.

      Delete
  43. hello andrii i strucked here and am getting an error in script component that is

    Server stack trace:
    at System.ServiceModel.Security.IssuanceTokenProviderBase`1.DoNegotiation(TimeSpan timeout)
    at System.ServiceModel.Security.SspiNegotiationTokenProvider.OnOpen(TimeSpan timeout)
    at System.ServiceModel.Security.WrapperSecurityCommunicationObject.OnOpen(TimeSpan timeout)
    at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
    at System.ServiceModel.Security.SecurityUtils.OpenCommunicationObject(ICommunicationObject obj, TimeSpan timeout)
    at System.ServiceModel.Security.SymmetricSecurityProtocol.OnOpen(TimeSpan timeout)
    at System.ServiceModel.Security.WrapperSecurityCommunicationObject.OnOpen(TimeSpan timeout)
    at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
    at System.ServiceModel.Channels.SecurityChannelFactory`1.ClientSecurityChannel`1.OnOpen(TimeSpan timeout)
    at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
    at System.ServiceModel.Channels.ServiceChannel.OnOpen(TimeSpan timeout)
    at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
    at System.ServiceModel.Channels.ServiceChannel.CallOnceManager.CallOnce(TimeSpan timeout, CallOnceManager cascade)
    at System.ServiceModel.Channels.ServiceChannel.EnsureOpened(TimeSpan timeout)
    at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
    at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
    at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

    Exception rethrown at [0]:
    at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
    at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
    at CrmProxy.Crm.IOrganizationService.Create(Entity entity)
    at CrmProxy.Crm.OrganizationServiceClient.Create(Entity entity)
    at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
    at UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
    at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

    ReplyDelete