Integration

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:

 

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:

 

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:

 

Sign this assembly because it will be put to GAC:

Build and install this assembly to GAC using drag-drop to C:WindowsAssembly 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.cs and make following changes:

 

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 on “Integrating CRM 2011 using SQL Integration Services (SSIS)

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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.

  6. 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

  7. 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.

  8. 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.

  9. 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

  10. 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.

  11. 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.

  12. 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

  13. 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.

  14. 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!

  15. 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.

  16. 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.

  17. 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.

  18. 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.

  19. 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

  20. 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);

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

  22. 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.

  23. 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?

  24. 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.

  25. 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?

  26. 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

  27. Hi Andrii

    I just got this error.

    Instance argument: cannot convert from
    CrmProxy.Crmnew.AttributeCollection' to 'System.Collections.Generic.IList>'
    c:usersysraelfdocumentsvisual studio 2010ProjectsCrmProxyCrmProxyExtensions.cs

    is there any body who encouter this kind of problem.

  28. 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:usersysraelfdocumentsvisual studio 2010ProjectsCrmProxyCrmProxyExtensions.cs 26

    Thanks.

  29. 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?

  30. 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.

  31. It should look like following:

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

  32. 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);

  33. 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

  34. 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.

  35. 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

  36. 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!

  37. 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.IssuanceTokenProviderBase1.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

  38. 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", );

  39. 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

  40. 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

  41. 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

  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?

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

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

    Server stack trace:
    at System.ServiceModel.Security.IssuanceTokenProviderBase1.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)

  44. 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

  45. 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.

  46. 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.

  47. 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.

  48. 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&#39; contains data of the 'http://schemas.microsoft.com/xrm/2011/Contracts:OptionSetValue&#39; 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.

  49. 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!

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

  51. 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

  52. 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?

  53. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *