Web Service Task And Document/Literal Calling Convention
May 6, 2008
The Web Service Task seems to support calling methods using parameters but not (as far as I can see) using the Document/Literal calling convention. Is this correct? Is this likely to change in the future?
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'm pretty stuck on a security issue in SSIS. The web service works by itself, but I can't call it from SSIS, it gives me a 401 unauthorized error. The web service also uses impersonation of my domain admin account.
I have tried the following things: Setting integrated windows authentication in IIS Setting the NTFS permissions of those web site directories to EVERYONE Using a credential / proxy in SSIS and running it from SQL Agent Changing the log on services of MSSQLSERVER, SQLAGENT, and SQL Integration Services to my domain admin account
I can't get anything to work. What is wrong with this thing? Microsoft's security model has gotten completley out of of hand imo
Also in the security event log it shows all authentication as successful.
I've created a report that will print on a pre-printed paper. Is there a way to embed a word document into a report or some other type on document. I'm not able to format the text like in word.
I have aspx page which uses Reportviewer object to execute/display reporting services report on the page based on the parameters passed by the aspx page.
It works fine. I have a problem when I try to use import option on that page. It throws the following error. Can anybody help me to resolve this issue?
Advanct thanks for your help and support
Thanks,
Sundar
Here is the error message when I export the report to any format (Excel, pdf, csv or text) For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Xml.XmlException: For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method.
Here is the code: ASPX Code: <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" Height="100%" ProcessingMode="Remote" Width="100%" ShowFindControls="false" ShowPageNavigationControls="true" ShowPromptAreaButton="false" AsyncRendering="false" EnableViewState="true" ShowParameterPrompts="false" Visible="false"> <ServerReport ReportServerUrl="http://miscsql1/ReportServer" ReportPath="/webreports/POS Commission Report"></ServerReport> </rsweb:ReportViewer>
ASPX.VB Code Dim RptParameters(2) As Microsoft.Reporting.WebForms.ReportParameter RptParameters(0) = New Microsoft.Reporting.WebForms.ReportParameter("Repcode", objCommon.RepSite.ToString, False) RptParameters(1) = New Microsoft.Reporting.WebForms.ReportParameter("StartDate", txtStartDate.Text.ToString) RptParameters(2) = New Microsoft.Reporting.WebForms.ReportParameter("EndDate", txtEndDate.Text.ToString) ReportViewer1.ServerReport.SetParameters(RptParameters) ReportViewer1.ServerReport.Refresh() ReportViewer1.Visible = True objCommon = Nothing
Out of Memory when working with big XML Files:when validating XSD it process small files but when size close to 1gb it throws .I have 16 xml files out of which 8-10 Â files size will be around 1gb processing one by one in FOR EACH LOOP container in SSIS[XML Task] Error: An error occurred with the following error message: "Exception of type 'System.OutOfMemoryException' was thrown.".Task XSD Validation failed.
system configuration : processor:Intel(R) Xeon (R) CPU E5-2670 v2 @2.50GhZ 2.50 Hz Installed memory (RAM): 61 GB System type: 64 -Bit operating System Visual studio:2012,32 bit virtual memory : 12499 MB
whether the size of the XML Document a limitation for the validation task ? or any system configuration i need to improve?
I'm trying to return Annotations from a BSM scorecard by calling the PMService.asmx web service. I can successfully call operations like "GetScorecards" that require no parameters...but I keep getting the same error when trying to call "GetAllAnnotationsbyScorecardID"
<?xml version="1.0" encoding="utf-8"?> <soap:Envelope xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <soap:Body> <soap:Fault> <faultcode>soaperver</faultcode> <faultstring>Server was unable to process request. --> scorecardID</faultstring> <detail /> </soap:Fault> </soap:Body> </soap:Envelope>
Am I structuring the Query the right way? Not sure if I'm a passing the guid correctly...I got the scorecard guid from the result set of calling "GetScorecards"
Is it possible to call a WCF service from a SQL CLR.
We have had success calling standard web services by including a Proxy in the Assembly but I wondered if it's possible to call out to a WCF service as the enpoint configuraion is done with an external XML file and I don't know if this will work in the same way. We may want to alter the wcf configuration depending on where the service is sitting, same box or remote.
Please do not respond with 'why do you want to do this' the reson we want to do this is because we use Service broker to recieve incoming messages and want to trigger a stored procedure to do some work on an external service when it recives some data.
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 used the URL approach to call the reporting service. In the ASP page, I use javascript to open a new window to view the report. But when I close the new window and work on the original window. It seems that the DB connection to the SQL server is not working.
I need to create a stored procedure (SQL 2005). I did it in C# (Visual Studio). The .dll file (inside) uses web services of Project Server 2007 (I just need to log on, create a project and log out). I prepared 2 files (exactly the same code): dll an exe. the .exe works fine. the problem is with .dll file (stored procedure in SQL)...
by the way: I created serialization file with sgen tool.
the problems start when I am trying to register these 2 .dlls in SQL server. I can do that in unsafe mode only. when I am trying to register them in externall access (normal dll) and safe mode (serialization .dll) I am getting errors:
CREATE ASSEMBLY failed because method "add_QueueCheckInProjectCompleted" on type "ConsoleApplication11.ProjectWebSvc.Project" in external_access assembly "ConsoleApplication11" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies
thus I did it in unsafe mode (both of them). than I created the procedure.
Now, I tried to call that procedure. I am getting error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'Main':
System.Net.WebException: The request failed with the error message:
<h2>Object moved to <a href="http://my_comp:22278/projectserver/_layouts/1033/error.aspx?ErrorText=Object%20reference%20not%20set%20to%20an%20instance%20of%20an%20object%2E">here</a>.</h2>
</body></html>
--.
System.Net.WebException:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ConsoleApplication11.ProjectWebSvc.Project.QueueCreateProject(Guid jobUid, ProjectDataSet dataset, Boolean validateOnly)
at ConsoleApplication11.Program.Main()
That means "Object reference not set to an instance of an object". the problem occurs when calling QueueCreateProject method
I dont understand that error. I wrote the same code for .dll and .exe. the .exe file is working ok (no errors) but .dll (as a stored procedure) shows this error
my code in C#:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Net;
using PSLibrary = Microsoft.Office.Project.Server.Library;
Hi, I need to call a webservice directly from a tigger or stored procedrue instead of creating a window service to read from a table then call the webservice .Is it possible ? if yes, please i need your support. Thanks,
I have a vbscript to read all files from a directory and, if the fileis valid, I would like my DTS to process it. I tried using thevbscript as an ActiveX workflow script in the DTS, but it does notexecute the data pump until it has completed looping through all thefiles, so only the last file read is sucked into the database(utilizing a global variable as the filename). Is there a way toexecute the data pump task from within the activex script? I can'tseem to find any documentation about executing a DTS task.Basically, the workflow I want is:1)Read files from directory (the number and names may change eachtime). (done with vbs)2)For each file, send it through the transformation into the database.3)When the information is in the database, append a date to the fileand move it to the archive folder. (done with vbs)If I am going about this the wrong way and you see something that isnot obvious to me, please let me know.Thanks in advance!
I am converting an old, classic asp application to ASP.NET. In the classic app, users are imported daily by importing a file from the head office and placed in the database by an SSIS package. In the new app, we are trying to use the built-in membership/authentication functionality. Does anyone know if it's possible to call the .NET membership API from, say a script task?
There is also the possibility of calling the aspnet_Membership_CreateUser directly, but that looks like I'd have to deal with password hashes and salts.
I am trying to call a Web Servie within a SSIS package. I want to pull a row at a time from a SQL Server 2005 table and pass this data values onto the web service.
Has anyone encountered this error? I get it when I run the installer (c#). I have seen a few posts on google and msdn, but still can't get past the error. I have tried uninstall/resinstalling the parser, registering from the command line, setting the property of the interop to "donotregister" and others with no luck.
Posted the same message to the Service Broker forum. But the board is neither responsive nor active. So I am trying this forum. Hope someone can help.
I would like to call an SSIS package from a Service Broker Queue.
There is one way that I am aware of -
Using xp_cmdshell from within an activation stored procedure and using DTEXEC.
Is there a more elegant way of executing an SSIS package from within SSB?
Also, I am not interested in writing a .NET external activator to process my messages in the queue. I would like this operation to be strictly database oriented. Having said this, I am also trying to avoid triggers processing the messages in the queue.
I have created a SOAP/HTTP end point which returns .NET DataSet ( ... FORMAT=ROWSETS_ONLY ... ). Then I created a proxy class using Visual Studio 2005 and wrote a cusumer .NET class in which I call that web service's method synchronously. Everything is Ok but when I call asynchronously "TargetInvocationException" is raised. I need urgent solution to this problem.
I would like to call an SSIS package from a Service Broker Queue.
There is one way that I am aware of -
Using xp_cmdshell from within an activation stored procedure and using DTEXEC.
Is there a more elegant way of executing an SSIS package from within SSB?
Also, I am not interested in writing a .NET external activator to process my messages in the queue. I would like this operation to be strictly database oriented. Having said this, I am also trying to avoid triggers processing the messages in the queue.
I am trying to call a SSIS package from a web service hosted on the same machine as the package file is sitting. All that the package does is a simple Execute SQL task with one datasource connection.
I have set impersonation as true. When I run the package from the web service on the virual port (through Visual studio IDE) it runs fine.
I have enabled logging in SSIS. The package log indicates the following:
Failed to acquire connection "APNetDS". Connection may not be configured correctly or you may not have the right permissions on this connection.
Effectively, my web service impersonated account (which is my admin account) is not being authenticated for any of the db connection (uses windows integrated authentication) and I find that odd. To impersonate, I have set the Impersonation to true in the web.config file. The authentication mode is windows.
Please tell me what bit of security I am missing? In case if it helps, the database connection is to my local database and hence should not deny access to my own account. And yes, I think my impoersonation is working because when I debug, the user.identity shows my user id.
I'm attempting to call a storedprocedure from within an ActiveX task ina DTS and am getting a "Command Text was not set for the commandobject" error. I have no problem if I replace the stored procedure callwith the actual SQL.Is it possible to directly call a SP via an ActiveX task? If yes, couldsomebody help me with the syntax please?This is what I have currently haveSet Conn = CreateObject("ADODB.Connection")SQL = "exec (MySP)"set RLoop=conn.Execute(SQL)do while (NOT RLoop.EOF)msgbox RLoop(1)RLoop.MoveNextLoopTIAKarthik
I was wondering if someone could point me in the direction of any sample code or documentation that allows you to execute SSIS packages from within a Script task?
In SQL Server 2000 DTS this was accomplished by instantiating the DTS.Package object and calling either the LoadFromSQLServer or LoadFromStorageFile method.
I have not been able to find any similiar logic in the SQL Server 2005 SSIS environment.
When running an integration services package from a windows service I get the "Object ... has been disconnected or does not exist at the server." exception after aproximately six minutes of execution.
This is *not* my windows service failing. I can loop indefinately while tracing to a log file within the service and it will run forever. While calling the mypackage.execute(...) method however, after six minutes (give or take) the exception is thrown...
my code looks something like this: <code> dim foo as Microsoft.SqlServer.Dts.Runtime.Application mypackage = foo..LoadPackage(strimportPkgFilename, pkgevents) results = myPackage.Execute(Nothing, Nothing, pkgevents, Nothing, Nothing) </code>
<error> A first chance exception of type 'System.Runtime.Remoting.RemotingException' occurred in mscorlib.dll Exception in: frmMyForm.DoImports Message: Object '/b76f98a0_5bd9_49d8_a524_eeb49d55b303/bqbhkjnaofq_ifr_cwz+srid_1.rem' has been disconnected or does not exist at the server. </error>
oddly, this same code works perfectly if I run it within a windows form application no matter how long it takes.
It also runs fine if the package can complete in under six minutes.
As part of my SSIS package, a list of sites is created that need to be created on a remote machine. let's say 1000 sites. I need to pass this list to a web service so web service sitting on that machine creates these sites for me. MY SSIS package does not run frequently so I can sacrifice time a little bit to get better functionality.
I need to move the sites that are not created (for any reasons) by web service to another table and successfully created sites to another table, so I need to get confirmation for each site from the web service.
Which option is better?
1) Calling web service for every single record (site) and get the confirmation and then based on the confirmation I move the records accordingly. I know this might be very time consuming, but as I said my SSIS package might only run every six months
OR
2) Sending records to a web service in a batch and get the result. I don€™t know how to do this though.
Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine
Now the procedure is changed to Oracle.So I have changed it to "BEGIN PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally. Executing 'execute BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.
I am searching for a solution for Calling or consume a web service in SSIS through Script task. I have gone through so many links but i am able to find the exact solution. I am getting so many references, though i am unable to crack it.
My requirement is i need to call a web service URL through script task which is having a client certificate. When we are trying to connect to the URL it will ask for the certificate authentication. After calling this URL we will get a WSDL file from the web service, We need to consume that WSDL file and we need to identify the methods inside this WSDL and need to write the data available in this WSDL to the data base tables.
How can we call that web service URL( With certificate) through script task and how can we read the WSDL file and How we can load the data into DB table.
I have a remote batch file on machine B that I need to execute using 'Execute process task' control from a package on machine A. The batch file uses pgp software and encrypts a file sitting on machine B itself. The reason why my batch file is sitting on machine B, is because the PGP software is on machine B.
If I execute the batch file by itself from machine B, the script runs fine. I refer the same batch file as a UNC path from my package on machine A. But that does not work since the 'Working directory' is still machine A. I can not set machine B's folder as the working dir because it does not accept UNC path. So I say, ok , let me map a path to that UNC location and map it as drive 'Z:'. Certainly if I do so, I will be running the process on machine A and the batch file will look for the pgp software on machine A, and hence fail.
I have tried third party remote batch execution tools (PSEXEC) but have not had success, not because of SSIS limitations, but simply because the PGP executable when run through the PSEXEC tool, does not identify the location of the public keys on machine B and hence gives an encrytion failure.
How do I get the remote batch file to execute such that it executes with its own env? Is there a better remote execution tool I can try or are there any other features of SSIS I can use to get around this issue? I need the results of the batch file and hence do not want to make it an asyncronous process.
This is the first time I've tried creating an "execute sql task" with a "full result set".
I've read in the documentation that I must set the resultname to 0, which is done, and that the variable must be of type object. Also done.
[Execute SQL Task] Error: Executing the query "select * from blah" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Has anyone else had success with a full result set?