Web Service Task With Dynamic Url
Jan 4, 2008
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?
View 3 Replies
ADVERTISEMENT
May 4, 2006
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()".
View 6 Replies
View Related
Jan 10, 2005
Hi,
I recently came across a DTS made by an experienced DBA and was impressed by the use of Dynamic Properties DTS Task used. As I understood the DTS was generic and if I'm not mistaken, can be easily transferred to another server/machine on an AS IS basis and without having to change any of the properties (server name, login, password etc.) for the source or destination server.
This seems to be a really neat feature.
I tried to put this to use but am having problems regarding how to proceed... Unfortunately I have not been able to find any article either which addresses this particular request and takes a novice step by step so that this feature can be used.
Will appreciate any help.
Thanks
View 3 Replies
View Related
May 21, 2008
What is equivalent to dynamic property task of dts2000 in SSIS 2005;
Thanks
View 1 Replies
View Related
Feb 26, 2001
Hi,
DTS now has a dynamic properties task in sql 2000 which at a first glance is pretty cool. However i do not know if it would sort out a particular issue I have.
I need to have a generic DTS package that pulls data from different locations based on the project selected by a user. The source is a btrieve database
The name of the tables change with the project, so for example the F24 project would have its source table named F24TACT and the IFO source would have its source table named IFOTACT.
I need to be able to dynamically retrieve the table name from a local SQL table and assign this name to the data pump for the data extraction..
I don' t know if this is achievable in DTS. I can retrieve the value into a look up variable but how can i set it at runtime ?
thanks
Tony
View 2 Replies
View Related
Apr 4, 2007
Hello,I am building a packge where an FTP task needs to pull down a singlefileevery day from a specific location. The location will only have theone file.The file name will be different every day. A sample of one of the filenameslooks like this:CDNSC.CDNSC.SC00015.04012007The file names will be different every day, as the last eight digitsrepresent the date of the data in the file. The source files will belocatedin a subdirectory called 'outgoing'.My first approach to this has been to use a Dyamic Properties Task tosetthe SourceFileName of the FTP Task. I tried using a sting GlobalVariableexpressed as *.*.*.* thinking a wildcard would work, but it didn't.What approaches can I take so that the Source Filename in the FTP taskwilldymanically update each day to get the one file?Thank you for your help!cdun2
View 1 Replies
View Related
Oct 11, 2006
Hi ,
What is the equivalent of DTS Dynamic Properties task into SSIS ?
How do I convert this task to SSIS ?
Thanks,
Vikas
View 1 Replies
View Related
Nov 7, 2007
Hi,
Is there anyway where i can pass the package name to an "execute package task" and run it through loop. My loop task will pass the package name which is assigned on a variable and the execute package task should pickup the name and run, is this possible, if yes how?
View 3 Replies
View Related
Sep 17, 2007
Hi!
I have a HttpConnection with an url to an existing webservices f.ex. http://myweb/ws/ssis.asmx?wsdl
My Web Service Task is using this HttpConnection and I have specified a path for the WSDL-file, f.ex: c:SSISMyProj.wsdl
This leads to that I have to deploy the WSDL-file to exactly the same location on the Sql-server that hosts/executes the Package.
Is there a way to point out a URL or anything else? I don't want to have this dependency.
//Daniel
View 1 Replies
View Related
Nov 28, 2007
Hi,
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?
Thank You
VBJ
View 9 Replies
View Related
Nov 5, 2007
Hello All,
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.
Thanks in advance
View 3 Replies
View Related
Dec 15, 2006
Hello,
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.
Thanks,
Uwe
View 7 Replies
View Related
Oct 3, 2007
I was using the code in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1371094&SiteID=1) to create a console application which can build the SSIS package dynamically and run the package.
If the source column and destination column names are of different cases then the application was failing during the mapping. So I modified the for each loop like below. Still this is not a fool proof method, this will work as long as all characters in the column names are upper or lower.
for eg., Source column = empl_id, Destination column = EMPL_ID, in this case the below code will work. if the source column or destination column is Empl_Id, then the below mapping will fail.
Code Block
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
IDTSInputColumn90 vCol = destnDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
try
{
destnDesignTime.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name.ToLower()].ID);
}
catch
{
destnDesignTime.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name.ToUpper()].ID);
}
}
So how can I map the columns irrespective of the cases?
Thanks
View 9 Replies
View Related
Oct 1, 2007
I am facing a reporting service issue.
System information:
1. Our Reporting Server installed on a DB server.
2. We have one windows application which executes on the same server, which generates the reports snapshots.
3. One set of reports with single Shared data source
Scenario:
We have many countries for which report history snapshots needs to be generated. The report information is stored in different databases named as database_CountryCode on the same DB server.
Questions:
1. We need to dynamically change the shared data source connection string to point to the respective country database, when generating the report for that particular country. --> We found out this can be accomplised by using parameterized connection string in report specific data sources in SQL 2005. Can we achive something similar in SQL 2003 Reports as well?
2. We also need to instantiate the reporting service web service in multiple threads, one for each country, where in each thread picks up the corresponding country code, changes the connection string and generates reports snapshots. Can this be accomplished? I know this goes against the entire idea of licensing, but my question is just about feasibility of this idea.
Addnl Info
The CreateReportHistorySnapshot method of reportingservice.asmx returns a snapshotID, which is the timestamp of report snapshot creation. We tried creating the same report snapshot for the same country in 2 threads. Whenever the timestamp for both snapshots was same, only one report snapshot actually got created. Can this be overcome?
View 1 Replies
View Related
Jun 23, 2006
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.
Suggestions?
Richard
View 14 Replies
View Related
Oct 20, 2006
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?
Greg.
View 2 Replies
View Related
Dec 21, 2005
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.
Any help much appreciated.
Greg.
View 1 Replies
View Related
Dec 22, 2007
hi all,
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..
Any one plz let me know the solution for this
Thankz
View 1 Replies
View Related
Apr 6, 2007
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.
Cale
View 5 Replies
View Related
Jun 28, 2007
I have a Dynamic propeties task in dts 2000 that process/executes a global variable.
The global variable basically executes a bat file.
How do i set this up in ssis. The migration failed to properly convert this task.
Please help.
Thank you.
View 11 Replies
View Related
Dec 31, 2013
I can set almost all properties of FTP task and FTP connection manager using expressions option. In that option, I don't see that I can set FTP PASSWORD using a variable. How do I set the password dynamically?
View 2 Replies
View Related
Mar 17, 2008
Hi All,
I have the following issue, i want to make dynamic the Argument property of the Execute Process Task but i couldn't able to do so, i have posted several times , hopefully i will get a solution today. I am using the following Argument to execute (winzip) program.
Argument: /e C:FTPFile1.zip C:FTPUnzipped
This Argument works but since File1.zip gets change every time, i mean it will be File2.zip, thus i have to change it manually on the Execute Process Task every time, however i want it to change by itself.
To make it clear, the /e mean winzip usage for extract file, the C:FTPFile1.zip is where the zipped file is, and the C:FTPUnzipped is where i dumped the unzipped files. as you see the file name will change every time like File1.zip, File2.zip, File3.zip, etc... Thus i want to make it daynamic, for example if i execute today File1.zip, then i have to execute tomorrow File2.zip automatically, and then File3.zip etc, that means the source file is changing every time, how do i make it then to be Dynamic.
Please help, i need your input badly.
Thank you in advance,
View 14 Replies
View Related
Oct 24, 2007
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?
View 1 Replies
View Related
Apr 12, 2007
Hi all,
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:
<?xml version="1.0" encoding="utf-16"?>
<ArrayOfBooking xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
<Booking>
<BookingDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</BookingDate>
<FlightDate xmlns="http://tempuri.org/">2007-04-12T16:14:33.9210023+03:00</FlightDate>
</Booking>
</ArrayOfBooking>
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?
Regards,
pc
View 3 Replies
View Related
Jun 11, 2007
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.
View 3 Replies
View Related
Dec 5, 2007
Hello,
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.
View 1 Replies
View Related
Sep 12, 2007
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?
Fred
View 5 Replies
View Related
Jul 2, 2007
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?
Thank you,
James Mac William
View 10 Replies
View Related
Mar 21, 2007
Hello,
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.
Greg.
View 3 Replies
View Related
Jan 11, 2007
Hi,
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
Thanks
View 7 Replies
View Related
Aug 30, 2006
Hi!
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.
Regards
Geir F
View 1 Replies
View Related
Feb 8, 2008
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?
Any help appreciated.
View 2 Replies
View Related
May 31, 2007
Hi,
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()".
Please help..
Thanks in advance
View 4 Replies
View Related
