microsoft.public.crm :: Using CRM web service within SSIS


	Using CRM web service within SSIS Hi, I am trying to create an integration package with SSIS that will import data into MS CRM on a daily basis. I would have liked to create a .NET integration application, but the client has restricted us to the use of SSIS. From reading posts on this site I have come to the following conclusions: 1) It is possible 2) It should not be too difficult if I am an experienced programmer If I am correct, could someone please point me in the right direction with the following: 1) Which component do I need to use in SSIS to access the webservice? (Is it in the toolbox?) 2) Are there any examples anywhere that may explain the process? Your help is greatly appreciated. Regards Ok, I found the webservice task in SSIS but.... Now I need a wsdl for our customized version of CRM Does anybody know how I can generate the wsdl file? - Hide quoted text - "dutoit" wrote: > Hi, > I am trying to create an integration package with SSIS that will import data > into MS CRM on a daily basis. > I would have liked to create a .NET integration application, but the client > has restricted us to the use of SSIS. > From reading posts on this site I have come to the following conclusions: > 1) It is possible > 2) It should not be too difficult if I am an experienced programmer > If I am correct, could someone please point me in the right direction with > the following: > 1) Which component do I need to use in SSIS to access the webservice? (Is it > in the toolbox?) > 2) Are there any examples anywhere that may explain the process? > Your help is greatly appreciated. > Regards Just browse to http://yourcrmserver/mscrmservices/2006/crmservice.asmx?wsdl Doesn't the webservice task give you the option to browse to your webservice and then generate the wsdl for you? -- Jeffry van de Vuurst CWR Mobility BV www.cwrmobility.com/weblog -- On 15 mei, 12:15, dutoit <dut@discussions.microsoft.com> wrote: - Hide quoted text - > Ok, I found the webservice task in SSIS but.... > Now I need a wsdl for our customized version of CRM > Does anybody know how I can generate the wsdl file?"dutoit" wrote: > > Hi, > > I am trying to create an integration package with SSIS that will import data > > into MS CRM on a daily basis. > > I would have liked to create a .NET integration application, but the client > > has restricted us to the use of SSIS. > > From reading posts on this site I have come to the following conclusions: > > 1) It is possible > > 2) It should not be too difficult if I am an experienced programmer > > If I am correct, could someone please point me in the right direction with > > the following: > > 1) Which component do I need to use in SSIS to access the webservice? (Is it > > in the toolbox?) > > 2) Are there any examples anywhere that may explain the process? > > Your help is greatly appreciated. > > Regards Hi Jeffry, Thanks for your reply. I am able to see the wsdl file by using the method as suggested. If I take this file (copy text -> paste into notepad -> save as something.wsdl) it does not work with the SSIS web services task editor. I get the following error: "Could not read the Web Services Description Language (WSDL) file. The input WSDL file is not valid. The following error occurred while reading the file. There is an error in XML document (1, 5).." Is there any other way of generating the wsdl file from the server? Thanks again, dutoit "Jeffry van de Vuurst" wrote: - Hide quoted text - > Just browse to http://yourcrmserver/mscrmservices/2006/crmservice.asmx?wsdl > Doesn't the webservice task give you the option to browse to your > webservice and then generate the wsdl for you? > -- > Jeffry van de Vuurst > CWR Mobility BV > www.cwrmobility.com/weblog > -- > On 15 mei, 12:15, dutoit <dut@discussions.microsoft.com> wrote: > > Ok, I found the webservice task in SSIS but.... > > Now I need a wsdl for our customized version of CRM > > Does anybody know how I can generate the wsdl file?"dutoit" wrote: > > > Hi, > > > I am trying to create an integration package with SSIS that will import data > > > into MS CRM on a daily basis. > > > I would have liked to create a .NET integration application, but the client > > > has restricted us to the use of SSIS. > > > From reading posts on this site I have come to the following conclusions: > > > 1) It is possible > > > 2) It should not be too difficult if I am an experienced programmer > > > If I am correct, could someone please point me in the right direction with > > > the following: > > > 1) Which component do I need to use in SSIS to access the webservice? (Is it > > > in the toolbox?) > > > 2) Are there any examples anywhere that may explain the process? > > > Your help is greatly appreciated. > > > Regards I don't have SSIS available at the moment, so I can't check. What options does the webservice task have? Can't you browse to your webservice url? -- Jeffry van de Vuurst CWR Mobility BV www.cwrmobility.com/weblog -- On 15 mei, 14:42, dutoit <dut@discussions.microsoft.com> wrote: - Hide quoted text - > Hi Jeffry, > Thanks for your reply. > I am able to see the wsdl file by using the method as suggested. > If I take this file (copy text -> paste into notepad -> save as > something.wsdl) > it does not work with the SSIS web services task editor. I get the following > error: > "Could not read the Web Services Description Language (WSDL) file. The input > WSDL file is not valid. The following error occurred while reading the file. > There is an error in XML document (1, 5).." > Is there any other way of generating the wsdl file from the server? > Thanks again, > dutoit > "Jeffry van de Vuurst" wrote:> Just browse tohttp://yourcrmserver/mscrmservices/2006/crmservice.asmx?wsdl > > Doesn't the webservice task give you the option to browse to your > > webservice and then generate the wsdl for you? > > -- > > Jeffry van de Vuurst > > CWR Mobility BV > >www.cwrmobility.com/weblog > > -- > > On 15 mei, 12:15, dutoit <dut@discussions.microsoft.com> wrote: > > > Ok, I found the webservice task in SSIS but.... > > > Now I need a wsdl for our customized version of CRM > > > Does anybody know how I can generate the wsdl file?"dutoit" wrote: > > > > Hi, > > > > I am trying to create an integration package with SSIS that will import data > > > > into MS CRM on a daily basis. > > > > I would have liked to create a .NET integration application, but the client > > > > has restricted us to the use of SSIS. > > > > From reading posts on this site I have come to the following conclusions: > > > > 1) It is possible > > > > 2) It should not be too difficult if I am an experienced programmer > > > > If I am correct, could someone please point me in the right direction with > > > > the following: > > > > 1) Which component do I need to use in SSIS to access the webservice? (Is it > > > > in the toolbox?) > > > > 2) Are there any examples anywhere that may explain the process? > > > > Your help is greatly appreciated. > > > > Regards Hi Jeffry, Thanks again. I managed to download the WSDL file successfully from the webservice url. When I open the tab called "Input" in the web services task editor the following happens: 1) The service dropdown contains "CRMService" 2) If I select this it gives the following error : TITLE: Web Service Task ------------------------------ Item has already been added. Key in dictionary: 'ArrayOfString' Key being added: 'ArrayOfString' After clicking ok in the error window, there is nothing to select in the Method dropdown box. (I would assume that the method dropdown should now contain a list of available methods for the service...) Any ideas? "Jeffry van de Vuurst" wrote: - Hide quoted text - > I don't have SSIS available at the moment, so I can't check. What > options does the webservice task have? Can't you browse to your > webservice url? > -- > Jeffry van de Vuurst > CWR Mobility BV > www.cwrmobility.com/weblog > -- > On 15 mei, 14:42, dutoit <dut@discussions.microsoft.com> wrote: > > Hi Jeffry, > > Thanks for your reply. > > I am able to see the wsdl file by using the method as suggested. > > If I take this file (copy text -> paste into notepad -> save as > > something.wsdl) > > it does not work with the SSIS web services task editor. I get the following > > error: > > "Could not read the Web Services Description Language (WSDL) file. The input > > WSDL file is not valid. The following error occurred while reading the file. > > There is an error in XML document (1, 5).." > > Is there any other way of generating the wsdl file from the server? > > Thanks again, > > dutoit > > "Jeffry van de Vuurst" wrote:> Just browse tohttp://yourcrmserver/mscrmservices/2006/crmservice.asmx?wsdl > > > Doesn't the webservice task give you the option to browse to your > > > webservice and then generate the wsdl for you? > > > -- > > > Jeffry van de Vuurst > > > CWR Mobility BV > > >www.cwrmobility.com/weblog > > > -- > > > On 15 mei, 12:15, dutoit <dut@discussions.microsoft.com> wrote: > > > > Ok, I found the webservice task in SSIS but.... > > > > Now I need a wsdl for our customized version of CRM > > > > Does anybody know how I can generate the wsdl file?"dutoit" wrote: > > > > > Hi, > > > > > I am trying to create an integration package with SSIS that will import data > > > > > into MS CRM on a daily basis. > > > > > I would have liked to create a .NET integration application, but the client > > > > > has restricted us to the use of SSIS. > > > > > From reading posts on this site I have come to the following conclusions: > > > > > 1) It is possible > > > > > 2) It should not be too difficult if I am an experienced programmer > > > > > If I am correct, could someone please point me in the right direction with > > > > > the following: > > > > > 1) Which component do I need to use in SSIS to access the webservice? (Is it > > > > > in the toolbox?) > > > > > 2) Are there any examples anywhere that may explain the process? > > > > > Your help is greatly appreciated. > > > > > Regards Then you probably have a problem. If I remember correct, this is the same problem BizTalk has (before the BizTalk adapter). It doesn't support webservices with collection types in it. And e.g. the RetrieveMultiple returns a BusinessEntityCollection with an array of BusinessEntities. So, this type of webservice is just not supported by SSIS (and BizTalk). You can probably use a custom .NET dll from SSIS to do the webservice work for you. That way you still have your .NET integration application... -- Jeffry van de Vuurst CWR Mobility BV www.cwrmobility.com/weblog -- On 15 mei, 16:47, dutoit <dut@discussions.microsoft.com> wrote: - Hide quoted text - > Hi Jeffry, > Thanks again. > I managed to download the WSDL file successfully from the webservice url. > When I open the tab called "Input" in the web services task editor the > following happens: > 1) The service dropdown contains "CRMService" > 2) If I select this it gives the following error : > TITLE: Web Service Task > ------------------------------ > Item has already been added. Key in dictionary: 'ArrayOfString' Key being > added: 'ArrayOfString' > After clicking ok in the error window, there is nothing to select in the > Method dropdown box. > (I would assume that the method dropdown should now contain a list of > available methods for the service...) > Any ideas? > "Jeffry van de Vuurst" wrote:> I don't have SSIS available at the moment, so I can't check. What > > options does the webservice task have? Can't you browse to your > > webservice url? > > -- > > Jeffry van de Vuurst > > CWR Mobility BV > >www.cwrmobility.com/weblog > > -- > > On 15 mei, 14:42, dutoit <dut@discussions.microsoft.com> wrote: > > > Hi Jeffry, > > > Thanks for your reply. > > > I am able to see the wsdl file by using the method as suggested. > > > If I take this file (copy text -> paste into notepad -> save as > > > something.wsdl) > > > it does not work with the SSIS web services task editor. I get the following > > > error: > > > "Could not read the Web Services Description Language (WSDL) file. The input > > > WSDL file is not valid. The following error occurred while reading the file. > > > There is an error in XML document (1, 5).." > > > Is there any other way of generating the wsdl file from the server? > > > Thanks again, > > > dutoit > > > "Jeffry van de Vuurst" wrote:> Just browse tohttp://yourcrmserver/mscrmservices/2006/crmservice.asmx?wsdl > > > > Doesn't the webservice task give you the option to browse to your > > > > webservice and then generate the wsdl for you? > > > > -- > > > > Jeffry van de Vuurst > > > > CWR Mobility BV > > > >www.cwrmobility.com/weblog > > > > -- > > > > On 15 mei, 12:15, dutoit <dut@discussions.microsoft.com> wrote: > > > > > Ok, I found the webservice task in SSIS but.... > > > > > Now I need a wsdl for our customized version of CRM > > > > > Does anybody know how I can generate the wsdl file?"dutoit" wrote: > > > > > > Hi, > > > > > > I am trying to create an integration package with SSIS that will import data > > > > > > into MS CRM on a daily basis. > > > > > > I would have liked to create a .NET integration application, but the client > > > > > > has restricted us to the use of SSIS. > > > > > > From reading posts on this site I have come to the following conclusions: > > > > > > 1) It is possible > > > > > > 2) It should not be too difficult if I am an experienced programmer > > > > > > If I am correct, could someone please point me in the right direction with > > > > > > the following: > > > > > > 1) Which component do I need to use in SSIS to access the webservice? (Is it > > > > > > in the toolbox?) > > > > > > 2) Are there any examples anywhere that may explain the process? > > > > > > Your help is greatly appreciated. > > > > > > Regards Ok, thanks again Jeffry. I am able to modify the wsdl file to a point where the methods do appear in the dropdown. I did however remove all reference to things that were being reported as being already added. (ArrayofString, ArrayofGui) This seems to have solved the problem, but I am pretty sure that I may have broken the collection types? Do you think this may work, or do I need to find a completely new approach? Regards, dutoit "Jeffry van de Vuurst" wrote: - Hide quoted text - > Then you probably have a problem. If I remember correct, this is the > same problem BizTalk has (before the BizTalk adapter). It doesn't > support webservices with collection types in it. And e.g. the > RetrieveMultiple returns a BusinessEntityCollection with an array of > BusinessEntities. So, this type of webservice is just not supported by > SSIS (and BizTalk). > You can probably use a custom .NET dll from SSIS to do the webservice > work for you. That way you still have your .NET integration > application... > -- > Jeffry van de Vuurst > CWR Mobility BV > www.cwrmobility.com/weblog > -- > On 15 mei, 16:47, dutoit <dut@discussions.microsoft.com> wrote: > > Hi Jeffry, > > Thanks again. > > I managed to download the WSDL file successfully from the webservice url. > > When I open the tab called "Input" in the web services task editor the > > following happens: > > 1) The service dropdown contains "CRMService" > > 2) If I select this it gives the following error : > > TITLE: Web Service Task > > ------------------------------ > > Item has already been added. Key in dictionary: 'ArrayOfString' Key being > > added: 'ArrayOfString' > > After clicking ok in the error window, there is nothing to select in the > > Method dropdown box. > > (I would assume that the method dropdown should now contain a list of > > available methods for the service...) > > Any ideas? > > "Jeffry van de Vuurst" wrote:> I don't have SSIS available at the moment, so I can't check. What > > > options does the webservice task have? Can't you browse to your > > > webservice url? > > > -- > > > Jeffry van de Vuurst > > > CWR Mobility BV > > >www.cwrmobility.com/weblog > > > -- > > > On 15 mei, 14:42, dutoit <dut@discussions.microsoft.com> wrote: > > > > Hi Jeffry, > > > > Thanks for your reply. > > > > I am able to see the wsdl file by using the method as suggested. > > > > If I take this file (copy text -> paste into notepad -> save as > > > > something.wsdl) > > > > it does not work with the SSIS web services task editor. I get the following > > > > error: > > > > "Could not read the Web Services Description Language (WSDL) file. The input > > > > WSDL file is not valid. The following error occurred while reading the file. > > > > There is an error in XML document (1, 5).." > > > > Is there any other way of generating the wsdl file from the server? > > > > Thanks again, > > > > dutoit > > > > "Jeffry van de Vuurst" wrote:> Just browse tohttp://yourcrmserver/mscrmservices/2006/crmservice.asmx?wsdl > > > > > Doesn't the webservice task give you the option to browse to your > > > > > webservice and then generate the wsdl for you? > > > > > -- > > > > > Jeffry van de Vuurst > > > > > CWR Mobility BV > > > > >www.cwrmobility.com/weblog > > > > > -- > > > > > On 15 mei, 12:15, dutoit <dut@discussions.microsoft.com> wrote: > > > > > > Ok, I found the webservice task in SSIS but.... > > > > > > Now I need a wsdl for our customized version of CRM > > > > > > Does anybody know how I can generate the wsdl file?"dutoit" wrote: > > > > > > > Hi, > > > > > > > I am trying to create an integration package with SSIS that will import data > > > > > > > into MS CRM on a daily basis. > > > > > > > I would have liked to create a .NET integration application, but the client > > > > > > > has restricted us to the use of SSIS. > > > > > > > From reading posts on this site I have come to the following conclusions: > > > > > > > 1) It is possible > > > > > > > 2) It should not be too difficult if I am an experienced programmer > > > > > > > If I am correct, could someone please point me in the right direction with > > > > > > > the following: > > > > > > > 1) Which component do I need to use in SSIS to access the webservice? (Is it > > > > > > > in the toolbox?) > > > > > > > 2) Are there any examples anywhere that may explain the process? > > > > > > > Your help is greatly appreciated. > > > > > > > Regards I'm pretty sure you'll need a different approach We looked at this a while back and I think the fundamental stumbling block is that SSIS will only allow you to pass simple data types (e.g. string, int) to a WebService call, whereas all the CRM WebService calls take complex data types (e.g. all dervied classes from BusinessEntity) You have 2 options: 1. Write your own web service that accepts simple types, and then calls the CRM WebService. Call your own web service from SSIS. This would work OK when only a few attributes need to be passed, but isn't particularly scalable 2. Create an SSIS PipelineComponent that does all the work, and add it to your SSIS package. This is more powerful, but involves a lot more work 3. Sorry for the blatant plug, but we went for the 2nd option, and have an SSIS PipelineComponent that I believe will be released next month -- David Jennaway - Microsoft Dynamics CRM MVP Web: http://www.excitation.co.uk - Hide quoted text - "dutoit" wrote: > Ok, thanks again Jeffry. > I am able to modify the wsdl file to a point where the methods do appear in > the dropdown. > I did however remove all reference to things that were being reported as > being already added. (ArrayofString, ArrayofGui) > This seems to have solved the problem, but I am pretty sure that I may have > broken the collection types? > Do you think this may work, or do I need to find a completely new approach? > Regards, > dutoit > "Jeffry van de Vuurst" wrote: > > Then you probably have a problem. If I remember correct, this is the > > same problem BizTalk has (before the BizTalk adapter). It doesn't > > support webservices with collection types in it. And e.g. the > > RetrieveMultiple returns a BusinessEntityCollection with an array of > > BusinessEntities. So, this type of webservice is just not supported by > > SSIS (and BizTalk). > > You can probably use a custom .NET dll from SSIS to do the webservice > > work for you. That way you still have your .NET integration > > application... > > -- > > Jeffry van de Vuurst > > CWR Mobility BV > > www.cwrmobility.com/weblog > > -- > > On 15 mei, 16:47, dutoit <dut@discussions.microsoft.com> wrote: > > > Hi Jeffry, > > > Thanks again. > > > I managed to download the WSDL file successfully from the webservice url. > > > When I open the tab called "Input" in the web services task editor the > > > following happens: > > > 1) The service dropdown contains "CRMService" > > > 2) If I select this it gives the following error : > > > TITLE: Web Service Task > > > ------------------------------ > > > Item has already been added. Key in dictionary: 'ArrayOfString' Key being > > > added: 'ArrayOfString' > > > After clicking ok in the error window, there is nothing to select in the > > > Method dropdown box. > > > (I would assume that the method dropdown should now contain a list of > > > available methods for the service...) > > > Any ideas? > > > "Jeffry van de Vuurst" wrote:> I don't have SSIS available at the moment, so I can't check. What > > > > options does the webservice task have? Can't you browse to your > > > > webservice url? > > > > -- > > > > Jeffry van de Vuurst > > > > CWR Mobility BV > > > >www.cwrmobility.com/weblog > > > > -- > > > > On 15 mei, 14:42, dutoit <dut@discussions.microsoft.com> wrote: > > > > > Hi Jeffry, > > > > > Thanks for your reply. > > > > > I am able to see the wsdl file by using the method as suggested. > > > > > If I take this file (copy text -> paste into notepad -> save as > > > > > something.wsdl) > > > > > it does not work with the SSIS web services task editor. I get the following > > > > > error: > > > > > "Could not read the Web Services Description Language (WSDL) file. The input > > > > > WSDL file is not valid. The following error occurred while reading the file. > > > > > There is an error in XML document (1, 5).." > > > > > Is there any other way of generating the wsdl file from the server? > > > > > Thanks again, > > > > > dutoit > > > > > "Jeffry van de Vuurst" wrote:> Just browse tohttp://yourcrmserver/mscrmservices/2006/crmservice.asmx?wsdl > > > > > > Doesn't the webservice task give you the option to browse to your > > > > > > webservice and then generate the wsdl for you? > > > > > > -- > > > > > > Jeffry van de Vuurst > > > > > > CWR Mobility BV > > > > > >www.cwrmobility.com/weblog > > > > > > -- > > > > > > On 15 mei, 12:15, dutoit <dut@discussions.microsoft.com> wrote: > > > > > > > Ok, I found the webservice task in SSIS but.... > > > > > > > Now I need a wsdl for our customized version of CRM > > > > > > > Does anybody know how I can generate the wsdl file?"dutoit" wrote: > > > > > > > > Hi, > > > > > > > > I am trying to create an integration package with SSIS that will import data > > > > > > > > into MS CRM on a daily basis. > > > > > > > > I would have liked to create a .NET integration application, but the client > > > > > > > > has restricted us to the use of SSIS. > > > > > > > > From reading posts on this site I have come to the following conclusions: > > > > > > > > 1) It is possible > > > > > > > > 2) It should not be too difficult if I am an experienced programmer > > > > > > > > If I am correct, could someone please point me in the right direction with > > > > > > > > the following: > > > > > > > > 1) Which component do I need to use in SSIS to access the webservice? (Is it > > > > > > > > in the toolbox?) > > > > > > > > 2) Are there any examples anywhere that may explain the process? > > > > > > > > Your help is greatly appreciated. > > > > > > > > Regards