Problem With A Web Service Task That Uses A Digital Certificate
Nov 9, 2006
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
hi all, i want to create a service broker application between two different instances. can i achieve the dialog security and transport security with out using certificate? like we can achieve the transport security using windows authentication (both the instances are in same domain). so how to achieve the dialog security with out using certificates.
i m trying to send message between different server instance using service broker.
and for security purpose i am trying to create certificate. for that i have used makecert.exe and get a certificate and a private key. but when i am creating certificate using that file it is showing error
the code is --
CREATE CERTIFICATE ctfSourceServerMaster
FROM FILE = 'C:SourceServer.cer'
WITH PRIVATE KEY ( FILE = 'C:SourceServer.pvk', DECRYPTION BY PASSWORD = 'PrivateKeyPassword' )
ACTIVE FOR BEGIN_DIALOG = ON
GO
i have created the file SourceServer.cer' and SourceServer.pvk' by using makecert.exe tool.
the idea behind creating the certificate ctfSourceServerMaster is to give transport security.
I am running the particular script in the master database.
but still i am getting error
ERROR:----
The certificate, asymmetric key, or private key file does not exist or has invalid format.
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()".
Hi I have created a Client/Server application. The Client connects remotely to the SQL 2005 server using thier unique user name and password.
The client application allows the users to update a form.
I need to add to the database a digital signature for that user when they update that form. This is intended to be a replacement for a physical signature that would appear on a paper form.
I€™m not sure if this is the right topic for this particular forum but here goes anyway€¦
Okay, admittedly, I am not really familiar with MS SQL Server (I€™m Oracle, actually) but I am investigating a possible implementation on a relatively small scale. I am sure that MS SQL Server will handle all the database functions I need and will leave plenty of room for future growth. That really isn€™t the issue. What I am trying to figure out is how the Digital Dashboard I€™ve read about works. Of particular concern is the apparent connection between the Dashboard and Share Point. My first question is this: Is Share Point required to use the Digital Dashboard?
My next question is actually a bit easier€¦ Is there a sample site I can access that shows some of the capabilities of a Digital Dashboard implementation? I€™d like to make sure that the kinds of displays I am trying to build are actually available using this feature€¦
Hi, We are trying to implement Service Broker between SQL Server Express and SQL Server on the Same machine and we are having problems with certificates. We are creating a certificate on SQL Server, backing up the certificate on a file system and then loading certificate on the SQL Server Express from the file and we are keep getting the following error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format.
Following script runs fine on SQL Server.
Code Snippet
use master
Create Master Key Encryption BY Password = '45Gme*3^&fwu';
BACKUP MASTER KEY TO FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk'
ENCRYPTION BY PASSWORD = '45Gme*3^&fwu'
Create Certificate EndPointCertificateC
WITH Subject = 'C.Server.Local',
START_DATE = '06/01/2006',
EXPIRY_DATE = '01/01/2008'
ACTIVE FOR BEGIN_DIALOG = ON;
BACKUP CERTIFICATE EndPointCertificateC
TO FILE = 'C:ServiceBrokerEndPointCertificateC.cer'
Following script runs on SQL Server Express:
Code Snippet
Create Certificate EndPointCertificateC
From FILE = 'C:ServiceBrokerEndPointCertificateC.cer'
WITH PRIVATE KEY (
FILE = 'C:ServiceBrokerPrivateKeyMasterB.pvk',
DECRYPTION BY PASSWORD = '45Gme*3^&fwu'
);
If we run the script other way around, it works fine. If we use the SQL Server on some other machine, the script works fine. But only on the same machine, it throws this error. We made sure the permissions and everything. Let us know if there is any work around or what are we doing wrong.
Would like to use the digital signature (i.e. PKI) from a smart card to authorize a PDF document. Is it possible to retrieve the PKI from Windows once its read in via the smart card using some sort of C++ or Java API call to apply it to the PDF form?
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 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'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
