2

I'm trying to consume data from one of our SaaS providers (Ultimate Software) via SSIS 2012 Enterprise Edition.

I'm well aware that the Web Service task is useless for anything but the most simple web services and have pretty well abandoned trying to make use of it for this project.

To get access to the data I need, I actually have to call two separate web services, one to login and obtain an Authentication token, the other to actually request the data I'm looking for.

I've put together a script component source, using the vendor's example code. As soon as I attempt to instantiate a new loginClient via the following code:

LoginService.LoginServiceClient loginClient = new LoginService.LoginServiceClient("WSHttpBinding_ILoginService");

I get the following error:

Could not find endpoint element with name 'WSHttpBinding_ILoginService' and contract 'LoginService.ILoginService' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this name could be found in the client element.

I've been able to get the vendor's code to work pretty much as is as a console application in visual studio, but as soon as I try to make it work as a script component it falls apart. Thank-you!

crosan
  • 486
  • 4
  • 13
  • 1
    So when it runs via a console app, you it works fine. Same code in an Script (component {df} or task {cf}) fails with the above? Could it be something like your console app has a reference to a wsdl file that the package won't (since it's all databasey)? – billinkc Aug 12 '14 at 15:01
  • http://stackoverflow.com/questions/17583220/could-not-find-default-endpoint-element-that-references-contract | http://stackoverflow.com/questions/17768611/could-not-find-endpoint-element-with-name-xxxxx-and-contract-yyy-in-the-serv | http://stackoverflow.com/questions/9377875/could-not-find-endpoint-element-with-name-and-contract-i-in-the-servicemodel Similar sounding issues, none marked as resolved though – billinkc Aug 12 '14 at 19:06
  • I can't find any documentation on that website. Is this a SOAP or a REST API? You will find it very hard to use a SOAP API via SSIS but REST is quite easy in a script component. If all else fails you could of course shell out and run a console app as a separate process. Nasty, but it would work. – Ciarán Aug 12 '14 at 21:22
  • Thanks Ciaran and Bill! Was finally able to speak to one of the developers there yesterday that mentioned they have a restful service easily accessible via Informatica. The soap service had a myriad of issues through SSIS. I think I found the primary issue was the fact that SSIS doesn't appear to make use of the app.config file in VSTA, which is where all the endpoints are defined from the WSDL file. It even appears you commented on this one billinkc :-) http://stackoverflow.com/questions/15226757/ssis-with-script-component-and-service-references – crosan Aug 19 '14 at 13:16

1 Answers1

0

How to consume Web Services in SSIS

please go through these steps : A Web service can simply be defined as a piece of functionality hosted over the web. It is a platform independent technology that can be leveraged using xml messages.

Let’s go through the steps one by one:

Step-1: Create a WCF service using the “WCF Service Application” template in visual studio and add a method GetCustomerName to the WCF service.

Step-2: You can test the service by pressing F5.

Step-3: So, our WCF service is up and running. Now let’s create the SSIS package to consume the service. Open SSDT or BIDS and create a new SSIS Project.

Step-4: Drag and drop the Web Service Task onto the Design pane. Step-5: Double click on the Web Service Task to open the “Web Service Task Editor”. In the General tab set the HTTPConnection option by creating a new connection. Step-6: In the Server URL textbox, specify the WCF service address that we created earlier and press the “Test Connection” button. Step-7: Create an empty wsdl file in notepad and specify the path to this file in the WSDLFile property. Make sure that the OverwriteWSDLFile option is set to true and press the “Download WSDL” button.

Step-8: For the purpose of this exercise, I have taken the web service output in a text file however we can use the response received from the WCF service in a variety of ways for example: a. Saving in the database. b. Creating/Passing a JSON object. c. Sending it to another application. For now, just go to the Output tab, in the File properties, select "New Connection".

Step-9: Select the "Create file" option in the Usage type.

Step-10: In the File textbox, specify the path of the file to store the results of the Web Service and press OK. This is the file where the XML data will be written to when the package is run and the web service is called.

Step-11: Run the SSIS project.

Step-12: Open the file created in the path specified in step 10 and you can see the XML output that was created from the web service.

Rahul Sharma
  • 453
  • 3
  • 10