locked
CRM 2011 - Connect to CRM using SSIS - status 401: Unauthorized RRS feed

  • Question

  • Hello!

    I am trying to connect to CRM 2011 using SQL 2008 R2 Integration Services - the goal is, to load data into CRM.

    Referring to the Blog http://blogs.msdn.com/b/crm/archive/2008/05/07/integrating-crm-using-sql-integration-services-ssis.aspx I tried to establish a connection to CRM and load data into it.

    Everything works fine, except the Script Component.

    Here is the code in my Script Component:

    public class ScriptMain : UserComponent

    {

        CrmService service = new CrmService();

        public override void PreExecute()

        {

            base.PreExecute();

            CrmService service = new CrmService();

            service.Credentials = System.Net.CredentialCache.DefaultCredentials;

            service.Url = "http://crm2011dev.myOrg.local/MSCrmServices/2007/CrmService.asmx";

            CrmAuthenticationToken token = new CrmAuthenticationToken();

            token.OrganizationName = "myOrg";

            service.CrmAuthenticationTokenValue = token;

            service.PreAuthenticate = true;         

        }

       

        public override void Input0_ProcessInputRow(Input0Buffer Row)

        {

            contact Kontakt = new contact();

            Kontakt.firstname = Row.FirstName;

            Kontakt.lastname = Row.LastName;

            Kontakt.telephone1 = Row.Phone;

            Kontakt.emailaddress1 = Row.Email;

            service.Create(Kontakt);

        }

    }

    But when I execute the package, the following error occurs:

    The request failed with HTTP status 401: Unauthorized.

       at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

       at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

       at CRM.Proxy.CrmSdk.CrmService.Create(BusinessEntity entity)

       at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

       at UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

       at UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

       at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

    That cannot be, I thought, because the user which runs the execution has full CRM rights (System Administrator role in CRM)!

    So I tried to implement the code directly in the “Input0_ProcessInputRow” section:

    public class ScriptMain : UserComponent

    {

        CrmService service = new CrmService();

        public override void PreExecute()

        {

            base.PreExecute();              

        }

       

        public override void Input0_ProcessInputRow(Input0Buffer Row)

        {

                // here the part from the PreExecute() section:

            CrmService service = new CrmService();

            service.Credentials = System.Net.CredentialCache.DefaultCredentials;

            service.Url = "http://crm2011dev.myOrg.local/MSCrmServices/2007/CrmService.asmx";

            CrmAuthenticationToken token = new CrmAuthenticationToken();

            token.OrganizationName = "myOrg";

            service.CrmAuthenticationTokenValue = token;

            service.PreAuthenticate = true;

                           

            contact Kontakt = new contact();

            Kontakt.firstname = Row.FirstName;

            Kontakt.lastname = Row.LastName;

            Kontakt.telephone1 = Row.Phone;

            Kontakt.emailaddress1 = Row.Email;

            service.Create(Kontakt);

        }

    }

    …and it works !!!

    So it cannot be a simple permission issue.

    It seems, that the Script Component does not pass the credentials from the “PreExecute” to the “Input0_ProcessInputRow”… how can I fix that?

    Has anybody an idea? Thanks in advance!

    st4ff

    Thursday, March 13, 2014 9:25 AM