I have created a Web service and I want to call a single method from it to extract data. For this reason I use the SSIS "Web Service Task" . The problem is that the xml output file from the web service task does not look in the proper way. When I invoke my web service method in the browser the result looks like the following:
<?xml version="1.0" encoding="utf-8" ?>
- <ArrayOfBooking xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/">
- <Booking>
<BookingDate>2007-04-12T15:19:01.6736072+03:00</BookingDate>
<FlightDate>2007-04-12T15:19:01.6736072+03:00</FlightDate>
</Booking>
- <Booking>
<BookingDate>2007-04-12T15:19:01.6736072+03:00</BookingDate>
<FlightDate>2007-04-12T15:19:01.6736072+03:00</FlightDate>
</Booking>
</ArrayOfBooking>
but when I use the web service task , the xml file produced, looks like the following:
My problem is that I cannot create a xsd schema for the xml file output from the "web service task", and because of this I cannot insert the data in the xml file into the Database, because the xsd schema is reqired by the XML Source which I use in my DataFlow task for inserting the data.
Do you have any ideas how to solve this strange problem?
I have a VM with SP2013 and SQL Server 2012 SP1. I have installed the powerpivot plugin SP1.But when I run the PowerPivot for SharePoint 2013 configuration tool.
With the new improvments to the web service task, we can now use variables as arguments in web service calls. I am trying to setup a call to the amazon web service ECS. I am trying to do a simple sellerlookup. I have played with the settings and gotten nowhere. I get one of two error when I try to execute. I can always use a scripting task or write my own task, but I would like to use the built in task if it is possible. Has anyone used AWS with SSIS?
[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
or
[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Method 'ProxyNamespace.AWSECommerceService.SellerLookup' not found.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
I am an ETL Developer.Very new to web service task.I created a package where I am using For loop Container.Within the container I used Execute SQL Task (retrieve a single row using store proc).Output of the Execute SQL Task is passed to webservice task.The output of Web service task is a XML data which is used in the data flow task and transfer the xml output to flat file destination.The package works fine for a batch of hundred rows. But for a batch of 10,000 rows at a stretch the performance is very poor. The webservice can take only one input at a time. Is there any other method other than web service task to solve this problem?
I am a using a web service task inside a package. I configured the url and also got the wsdl file into my local envirioment and mapped the path of the WSDL file into the package. The Test connection for the site also succeeded. The problem I am facing is as follows.
When I go to the input tab there the service name automatically comes up allright but in the Method when I use the drop down I do not see any of the functions that are listed. Am I doing something wrong? Please do advice. It is very very urgent.
I tried to use an Web Service from SSIS (Web Service Task), this Web Service returns arrays of int and an array of a data contract. It always returns the following error msg while selection the method after downloading the WSDL-file:
Item has already been added. Key in dictionary: 'Arrayofint' Key being added: 'Arrayofint'.
When I remove the arrays and just use an data contract as return parameter. SSIS-project also returns the same error message, but not with Arrayofint, instead it has the name of data contract. If I implement the same webservice with ASP.Net everything works fine.
Is there a solution to this problem besides of using ASP.Net or a stored procedure in SQL Server, which implements the web service access? Or has anyone has integrated a WCF web service with data contract?
Comment 06/12/16 (UH): Stored Procedures doesn't work either, since the assemblies from WCF can't be added to SQL Server.
Executing the simplest of Web Service tasks consistently fail with the error: "Object reference not set to an instance of an object".
This really isn't a very helpful message for this task, and there's nothing from the task editor that can be adjusted. I am using SP1, the HTTP connection to the service tests OK, the WSDL file downloads OK, and the methods and parameters appear OK.
A package contains a web service task which makes a call to a web service. This call may take some time to complete (maybe up to 5-10 minutes). In it's simplest form, my package is reporting a failure. Error message is
"Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: The operation has timed out.
I can find no way to set the timeout period of a SSIS web service task. Is this possible, or is it something which Microsoft is addressing?
I am trying to configure a web service task. I have created the HTTP Connection, and specified the WSDL. When I select the service name on the Input page, I get the error ...
Item has already been added. Key in dictionary: 'BaseTO' Key being added: 'BaseTO'
I have previously been able to successfully configure the task in a separate project.
The Webservice methods take and return complex parameters. e.g. BaseTO above is custom transfer object type which the other transfer objects inherit. It is a List<> of some type of this object which is being passed/returned from the service.
I suspect these complex types will not be supported anyway but am puzzled by the original problem.
I have a web service running from multipule locations. Its the same service at all the locations. I am trying to use a for loop to execute a web service task dynamically setting the url of the http connection manager that the webservice task uses. I have succesfuly done this, but the task is using a url from the wsdl file that is stored locally to access the service regardless of what the url is in the http connection manager is. Is there any way to force the web service task to download the wsdl file every time it's executed or is there an easier way for accomplishing this?
I am creating a simple ssis package in business inteligence studio 2005. In that i am going to use an webservice task. I have assigned the wsdl and the configured the HTTP connection too. I have selected the service name and the method to be called. I have to give 2 input values for the web method. I need to make it dynamic. so i have planned to assign the variables for those input parameters. But In the webservice editor window there is only the provision to assign the vales for the input parameters. how can i assign valriables instead of setting the values directly.
I have also find in blogs that there will be a variable checkbox available. But i cant find the one..
I have been struggling with a problem with the Web Service Task. I have a package that uses this and has to authenticate to the web service. This works fine on my development server but if I export the package to file and run from another computer / user, the package fails as it doesn't log in to the web service. I have found the problem to be the package was set to default of EncryptSensitiveWithUserKey and I understand why this is. If I change to use EncryptSensitiveWithPassword, it prompts for creds and works fine. My question is how do I use a package configuration file to use different creds (there is not password option for the http connection) so I can standardize on use of config files for this operation. Thanks.
I am retrieving data from Web Services by using Web Service Task. In the General pane, I successfuly download the wsdl file. There are 3 methods included in this file. However, I cannot see these methods in the list box in Input pane. I have installed SP2. Could anyone tell me why?
I'm attempting to use the Web Service task to call a method provided by a vendor. The inputs are all simple types so that helps a lot, but for one input it's a string array. If I enter a single string value for a given id using the array dialog, the method works fine.
However if I try to change the input to use a variable to provide the value it fails. I'm assuming that you can't create a variable to return a string array. Please let me know if this is the case or if I'm missing something obvious ;-)
In addition, is there a way to specify a null value for an input? Currently there doesn't appear to be a way to do this in SP2. I tried leaving the string empty which didn't work and I tried using a variable with expression "NULL(DT_WSTR, 1252)" but that failed too.
I have a web service contained in a apache tomcat server and I try to execute a webservice and store the result into a flat file.But I have a message saying me that there is an error in the xml document. I suppose that it's my wsdl file. But my wsdl file is fine!!! Can the error come from the provider of the web service (Apache Tomcat). I suppose that because in every example on the internet I didn't see an example an tomcat or other web server use.
When trying to set up a Web Service Task in SSIS I get this error message when I click on the "Test Connection" button:
Invalid URI: The format of the URI could not be determined.
The web service is http://www.webservicex.net/CurrencyConvertor.asmx. When I enter the same URL into Internet Explorer it connects to the web service with no errors. We do have a proxy server. What is causing my problem?
I am trying to call a web service using the web service task and passing it variables, but even when I set variables to be Strings/any other data type, they always get passed out as objects. The call is then rejected based on the fact that there is no web service that accepts those parameter types. If there is a workaround to this problem, that would be great, otherwise I think I'm going to have to call the web services in a script task. Would someone please give me an example of the steps necessary to call a web service from the script task?
I am trying to sync a database with data from a web service.
In my control flow I have the Web Service Task setup correctly (I can run the project and the expected data is output to a file if I set the output of the web service task to type "File Connection").
What I would like to do is set the OutputType of my Web Service Task to "Variable" and have the Xml Source component in my Data Flow read the variable and process it.
I have already created a variable of type string to link the two parts together, but I have not figured out how.
Any suggestions? I have been searching the web, but have not found an answer to my question.
I m trying to use the web services task, but when i try to run it i got the following error
[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
This is probably because i never worked with this task, even so, does anyone knows what might be wrong?
I got a connection a wsdl, i can configure the service, method and variables(i use fixed values), and i configure the output to a variable with the type object(i have tryed string and int)
I even can download the wsdl file, but the error seems to be in the connection
I am quite new using SSIS and I have a problem with catching an (SOAP) exception from a Web service task. Some times my web service task can fail and when the web service is failing, it is throwing an exception. When the task succeeds the result is being put into a variable, That part is not a problem.
But catching an exception is. I have tried to use a script task and tried to get exception from the dts object model. I have not yet succeeded on that. But it might be a possible way to go. A different approach might be creating an OnError event on my web service task which I can create a task when triggered. But I have not found any solution yet and I hope some people out there have done this before or have a solution on this.
I create a native web service (Soap) on a third party vendor product that is integrated with Net Framework 2.0 I create a client for this in VS 2000. It works fine [Client can access the web service]
Now I go and create a SSIS Package with a web service task and try to hook up to the same web service. The package fails. Why?
I have created a SSIS package with Web Service task in it,which execute remote server web service
I got following error during Web service execution
Note:I am not using proxy settings,i also checked HTTP connection and it suceeded
Error:[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
For the life of me I can't figure this one out. I'm trying to call a web service using the SSIS "Web Service Task". I give the Connection Manager a valid URL, download the WSDL, and go to the input pane of the "Web Service Task" options. No problems. I select my web service and pick a web method from the methods drop-down box. No problems. But where it should pop up with an input dialog box that allows you set your inputs for the web service call, nothing shows up. No errors are reported; the input pane just remains completely blank. I can inspect the WSDL with other tools (like Altova's XMLSpy) and plainly see the inputs it takes. I haven't seen a similar issue posted on this forum so I don't know if I'm going nuts or my version of SSIS is evil or what. I applied SQL Server SP1 and SP2 but it didn't change anything. I then tried to connect to one of Amazon's public web services to see if it was a problem related to our in-house WSDL formatting, and I discovered the same problem. Take for instance http://webservices.amazon.com/AWSECommerceService/AWSECommerceService.wsdl? I create a Web Service Task for this service and when I go to select the "ListLookup" method from the drop-down box on the input page no input parameters appear. The WSDL clearly defines this method to take a bunch of inputs. If anybody has any ideas about what's going on here I would greatly appreciate the help. Thanks.
Hi: I a SSIS beginner and am hoping this is an easy one. I am using a Foreach loop container with an in-memory ADO recordset as the enumerator. The recordset has 3 columns and one of them I wish to fixup data in by calling a web service task that returns an integer. I see how to call the web service and return the integer into a variable but am unsure how to approach putting that variable back into the in-memory ADO recordset? Should I even be trying to do it this way? thanks! Henry
I have been trying to setup a web service task to use my http connection manager that connects to a server url. What I want to do is to upload a file to a web server... can this be done using web service task? and what is the WSDL? and what happens when my target server usis SSL? so instead of http, it should use https? I need help with this one... can't quite imagine how to get things rolling..
I've tried creating several Web Service Tasks and had no problems until I came across one that requires authentication. I have a standalone program to handle this Web Service, but I'm experimenting with SSIS to see if I can replace it.
Hi I have created a web service task that sits inside of a foreach loop. The loop is a ado enumerator. This loop is going to loop through about 35,000 records and make 35,000 web service calls.
So far through my testing, I have gotten it to read and write everything correctly, but each call to the web service takes about 1-2 seconds and maxes out the CPU. This would take almost all day for this web service to run at 100% CPU power all day. We want to be able to run this a few times a day or at least once a day.
I have disabled all of the other tasks in the foreach loop so it just calls the web service task and found that it's the web service task that takes up 100% of the CPU and takes 1-2 seconds to run. I have also tried creating a mock web service on the server that just sends back the same mock data, so it's extremely fast, but the Web Service Task still maxes out the CPU and takes 1-2 seconds per call.
I have tried this out on 2 different servers, all with a few gigs of ram they're not using. It seems more like it's a CPU thing, or that the Web Service Task has a high overhead that each time in the loop it takes 1-2 seconds to create the task.
Microsoft says it is possible but I just do not see how. Here is the link to the help file where it said that variables could be pass as input to web methods...I do not see the check box they mention on my IDE.
There is not a way to pass parameters to input of Web Service tasks. I heard this problem is fixed with SQL2K5 SP1 and even the online doc says that one can choose either "value" or "variable" when specifying input for web service tasks, but after I installed what-I-think-is SP1, there is still no way to do this.
If one can only specify values (hard-coded) as input to web service tasks, then this would be a very severe limitation. I hope I'm wrong, so could someone please give a pointer. Thanks
Hi every body... I will thank u the help that you could bring me...
This is my problem:
I am using a Web Service Task in Integration Services for access a Web Method, this method is not mine, it is a method provided for another company. This Web Method is published in a VPN (Virtual Private Network) and it is in a secured channel (https).
The properties of the Connection Manager that the Web Service Task uses are ok. The Server URL is the address of the asmx and the client certificate is the correct. In fact when I press Test Connection, the message Test Connection Succeed is shown. If I change the certificate, a connection error occurs (403 Forbidden), this means that the client certificate and the address is ok. I addition, I use this certificate to access another methods published by the same company in a Visual basic application (with WSE) and I donīt have problems.
When I execute the DTSX the error "403 Forbidden" occurs and I can't execute the method. I executed the DTS in the VS2005, SQL IDE and in a batch file (.bat) too, and the same error happened.
If I use the File Monitor tool (from SysInternal, that now belongs to Microsoft) I can see that when I test the connection (in the Connection Manager used by the Web Service Task), the process accesses the certificate's private key located in the RSA folder of my certificates. But when I run the DTSX (no matters which way), it never reads the private key, it only accesses the certificate repository.
I saw that the code of the DTSX includes the certificate in Base64, but I am thinking that this code doesn't include the private key (I don't know if it would have to do it)...
Well, the fact is that I canīt use this Web Service task. I am suspecting that is a bug of the Integration Service
