SSIS Web Service Task - SOAP Header Input
Feb 7, 2008
Hi,
In SSIS web service task - when you specify the Service and Method in the input tab for a WSDL file being used,
it seems to prompt for the parameters to be supplied only in the body of the WSDL file, and not the header.
I need to be able to provide security information (present in the SOAP header of the WSDL) like username, password etc..which is necessary to post any response to the web server, and I cannot see where I can give this in the input tab of the web service task.
Any help highly appreciated.
Thanks
View 3 Replies
ADVERTISEMENT
Nov 7, 2006
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
Kevin Le
View 7 Replies
View Related
Jan 17, 2006
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.
Anyone know how to handle this?
Thanks
-- Scott
View 2 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
Jun 11, 2007
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.
View 15 Replies
View Related
May 14, 2007
I created my SOAP endpoint in SQL 2005 sp 2, I worte an ASP.NET 2.0 app to consume the exposed webmethod but how do you add the soap headers in the asp.net app?
Basically I want to expose the application name property to set it to determine the actual caller in SQL Profiler. Can it be done without modifying the generated proxy code in ASP.net or is there a workaround ?
Thanks
J
View 4 Replies
View Related
Nov 24, 2006
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.
Any help would be greatly appreciated.
Thanks,
Catherine
View 4 Replies
View Related
Apr 3, 2006
Hi,
I have created a simple webservice with a soap header and am trying to
access it using an xml datasource in reporting services. The query
works but I have no idea how to pass in a value for the UID on the host
header. Any help? what XML would I add for the header?
This is what I have no that allows me to pass in parameters through the editor.
<Query>
<Method Namespace="http://tempuri.org/" Name="HelloPerson2"/>
<SoapAction>http://tempuri.org/HelloPerson2</SoapAction>
</Query>
Here is the code for the webservice:
/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class WebService : System.Web.Services.WebService {
public WebService () {
//Uncomment the following line if using designed components
//InitializeComponent();
}
[Serializable]
public class PersonHeader : SoapHeader
{
private int _uid;
public int UID
{
get { return _uid; }
set { _uid = value; }
}
}
public PersonHeader userCredentials;
[WebMethod]
[SoapHeader("userCredentials")]
public Person HelloPerson2(string firsName, string lastName)
{
Person person = new Person();
//person.FirstName = "John";
//person.LastName = "Smith";
person.FirstName = firsName;
person.LastName = lastName;
person.UID = userCredentials.UID;
return person;
}
[Serializable]
public class Person
{
public Person()
{
}
private string _firstName;
public string FirstName
{
get { return _firstName; }
set { _firstName = value; }
}
private string _lastName;
public string LastName
{
get { return _lastName; }
set { _lastName = value; }
}
private int _uid=1000;
public int UID
{
get { return _uid; }
set { _uid = value; }
}
}
}
View 7 Replies
View Related
Jun 29, 2015
I have just upgraded from Report server 2005 to 2008 r2. I have a program that calls the report server to run some reports but I have getting the error Server did not recognize the value of http header SOAPAction:
[URL] .....
I can log in to the report server and run any report that I want without any issues.
View 3 Replies
View Related
Mar 3, 2007
hi
I would like to create a SSIS package that is going to be called by store procedures.
What i have done so far.
1) I created a Execute SQL task that come with this statement e.g. Seleect * from tblA where BD >= ? and BD =< ?
2) I save this package as a DTSX file and will called it from a proc.
My intention is to pass 2 values when i call the proc. What should do next? any guided tutorial or steps i would be happy. thanks
View 1 Replies
View Related
Feb 3, 2007
Hi,I have a client that needs to issue soap calls to a third-party webservice from within a DTS package. Is this even possible? I'velooked everywhere and found the sqlxml, but that looks like itprovides a soap web service, not consumes one.Right now, we are consuming the same web service in an ASP.NETapplication and would love to use the same code, but am really justlooking for the best solution. Basically, the only firm requirementsare that the client needs to provide a text file of items that need tobe posted to the third-party via their web service. Right now, we usedts for the client to upload other text files, that's why I was hopingto use the same procedure, but if we need to go another route, I'msure I can convince them.Any suggestions?Trey
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
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
May 23, 2007
Hello,
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.
View 2 Replies
View Related
Apr 5, 2007
Is it possible to do this under SSIS 2005? How? I see I can add a reference to system.web.services.dll.. but then what?
The web service was developed in vb.net/vs.net 2005 and I have no problem adding and consuming it from a web page developed using vs.net 2005 - asp.net/vb.net.
Thanks for any help or information.
View 3 Replies
View Related
May 26, 2006
Hi
Does any body knows how to convert this VBScript Code to VBDotNet (SQL SERVER 2005 INTEGERATION). Below Codes Returns a list of all the services installed on a computer, and indicates their current status (typically, running or not running).Its in VBScript Format.
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\" & strComputer & "
ootcimv2")
Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service")
For Each objService in colRunningServices
Wscript.Echo objService.DisplayName & VbTab & objService.State
Next
Regards
Deepu M.I
View 4 Replies
View Related
May 30, 2008
Hi everybody,
I'm fairly new to the SSAS/SSIS world (though not new on databases, etc.) and I'm having some problems with the SSIS packages in our Cube environment.
Currently in our SSAS/SSIS project, we have two major connection managers, one to the database we use for loading the Cube, and the other connector for the cube itself. To load the data from the database to the cube, we wrote some SSIS packages and used the Analysis Service Processing tasks to process all the dimensions and measures. This works pretty good, so no problems here.
The real problem starts, when I try to change the connection parameters, e.g. because the server changed, or the database has been renamed.
As soon as the connection managers points to another (existing) cube, regardless if the structure is exactly the same as the one of the old cube, the tasks lose all the assigned objects from their lists. It is really annoying to add all these exactly same objects to the task again. I tried experimenting with the DelayValidation attribute so the Development Studio doesn't destroy my work every time, but when I deploy the package the Cube breaks. Obviously some kind of deeper connection is destroyed when I change the connection string.
Is there a way to prevent the package from breaking/losing objects, without me having to sacrifice 15 minutes every time I change the connection parameters?
Regards,
Tris
View 4 Replies
View Related
Dec 4, 2007
I'm getting the following error from a few hosts that are querying a db in SQL Server 2005. The error has occurred while executing various queries that we would expect to return various sized result sets (from a couple rows to a couple million rows). Many hosts have never experienced the error but it is occurring fairly frequently on a couple.
using Windows Server 2003 on both the jdbc client and the DB host.
using jdbc driver from sqljdbc_1.1
netstat doesnt increment the TCP connection reset count after this error occurs.
using standard sql server authentication.
-----------------------------------------
com.microsoft.sqlserver.jdbc.SQLServerException: A DBComms.error occurred while reading input. Context:Read packet header, Unexpected end of stream, readBytes:-1. Negative read result PktNumber:0. ReadThisPacket:0. PktDataSize:4,096.
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
com.microsoft.sqlserver.jdbc.DBComms.readError(Unknown Source)
com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerStatement$StatementExecutionRequest.executeStatement(Unknown Source)
com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
com.nmwco.server.LoggingReporting.UiQueryPerformer.generateStringStringPair(UiQueryPerformer.java:211)
com.nmwco.server.LoggingReporting.SharedVisualizer.createClientsUi(SharedVisualizer.java:767)
com.nmwco.server.LoggingReporting.SharedVisualizer.createClientsUi(SharedVisualizer.java:737)
com.nmwco.server.report.Reports.ApplicationNetworkUtilization.configure(ApplicationNetworkUtilization.java:44)
com.nmwco.server.report.ReportVisualizerFactory.execute(ReportVisualizerFactory.java:459)
com.nmwco.server.report.report.processRequest(report.java:207)
com.nmwco.server.report.report.doGet(report.java:217)
javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:672)
org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:574)
org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:499)
org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:966)
com.nmwco.server.jsp.reporting_config._jspService(reporting_config.java:132)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
com.nmwco.server.misc.EncodingFilter.doFilter(EncodingFilter.java:33)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
java.lang.Thread.run(Thread.java:619)
View 3 Replies
View Related
Aug 8, 2005
I want to dynamically pass values to the input value of a Web method to return values to a webservice.
View 7 Replies
View Related
May 15, 2008
I'm using a Row Count Task to count the number of records passing thru a particular path in my data flow. I created a package variable and referenced this variable in the Components Properties tab in the Row Count Task. I believe this is the minimum I need to do to get the row count.
However, as I explore the other tabs in the editor, I see there is something called Input Columns tab. What is this for? I didn't select anything in there and things are working fine. At first I thought that I had to choose the columns that I want to have available to me for further processing after the Row Count Task, but this isn't the case. I am able to see all my columns coming out of the Row Count Task even tho I didn't do anything in the Input Columns tab.
So, what is this setting for?
View 4 Replies
View Related
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
Jul 24, 2007
Hi,
I have a SSIS package with a Sequence which Contains a Webservice Task, in the input section of this task i want to pass a User Variable as Parameter for my webmethod. but it doesn´t work, it allways sends the variable definition as string "@[User::Filename]". so i searched Microsoft Technet how to pass User Variables in Webservice Tasks and found this site: http://technet.microsoft.com/en-us/library/ms187617.aspx
which says :
"
Variable
Select the check boxes to use variables to provide inputs. "
but there is no such checkbox on the input page of my Webservice Task... there is just the Value column which i can edit... but as mentioned before when i try to set the value to a variable it doesn work
i tried the following strings in the value column:
@[User::Filename]
@Filename
User::Filename
@User::Filename
any ideas?
thanks for your help
bye
as
View 5 Replies
View Related
Dec 18, 2007
Hi Everyone,
I'm trying to do something that should be fairly straightforward, but SSIS seems to be getting confused. I have a stored procedure which takes a timestamp as an input parameter. (NOTE: It's not a DateTime that's being stored as a DBTIMESTAMP, it really is a timestamp in the SQL sense.)
The command should be something like this:
Code Block
EXEC dbo.UpdateSynchTimestamp ?
I tried to use my variable to pass the value through Parameter Mapping, but I got an unusual error:
[Execute SQL Task] Error: Executing the query "EXEC dbo.UpdateSynchTimestamp ?" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_DBTIMESTAMP)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This is strange for a number of reasons:
1) The DBTIMESTAMP parameter has the Direction set to "Input", so it should not be interpreted as an Output or ReturnValue.
2) The Execute SQL Task has Result Set = "None", so it should not be trying to return anything.
If I change the code to include a value hard-coded it works:
Code Block
EXEC dbo.UpdateSynchTimestamp 0x00000000000013BD
It is only when a variable is involved that it breaks.
Finally, here's the Stored Procedure itself:
Code Block
CREATE PROCEDURE [dbo].[UpdateSynchTimestamp]
@NewValue TIMESTAMP
AS
BEGIN
SET NOCOUNT ON;
UPDATE ServerSettings
SET [Value] = @NewValue
WHERE [Key] = 'SynchTimestamp'
END
Doe anyone have any suggestions as to why this isn't working for me? For the time being, I have a Script Task which constructs the command text and stores it in a variable. I can't even use an Expression because the DBTIMESTAMP is not supported.
Thanks for reading this!
View 12 Replies
View Related
Mar 29, 2007
I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:
1) In the First Execute SQL Task, I want to store a single row result of @@identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )
2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).
Please give me solutions for the above two..
View 6 Replies
View Related
Jul 30, 2007
I need to loop the recordset returned from a ExecuteSQL task and transform each row using a Data Conversion task (or a Script Task).
I know how to loop the recordset returned by an ExecuteSQL task:
http://www.sqlis.com/59.aspx
I loop the returned recordset (which is mapped to a User variable of type System.Object) and assign the Variable Mappings in the ForEach Loop to different user variables which map to the Exec proc resultset (with names and data types).
I assume to now use these as the Available Input columns for the Data Conversion task, I drag a Data Flow task inside the For Each Loop container and double-click it, then add a Data Conversion task.
But the Input columns (which I entered in the Variable Mappings in the ForEach Loop containers) dont show up in the Available Input columns of the Data Conversion task.
How do I link the Variable Mappings in the ForEach Loop containers from the recordset returned by the Execute SQL Task to the Available Input columns of the Data Conversion task?
.......................
If this is not possible, and the advice is to use the OLEDB data flow as the input for the Data Conversion task (which is something I tried too), then the results from an OLEDB Command (using EXEC sp_myproc) are not mapped to the Available Input columns of the Data Conversion task either (as its not an explicit SQL Statement and the runtime results from a stored proc exection)
I would like to use the ExecuteSQL task to do this as the Package is clean and comprehensible. Which is the easiest best way to map the returned results from a Stored proc execution to the Available Input columns of any Data Flow transformation task for the transform operations I need to execute on each row of data?
[ Could not find any useful advice on this anywhere ]
thanks in advance!
View 4 Replies
View Related
Jan 2, 2007
Hi Everyone,
I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.
I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!
Thanks
View 11 Replies
View Related
Feb 19, 2008
Hi Anyone
How to export a header and details data from two different table and export it in the below format ?
RecordCount = 129 ------------> Header
001|Manager|2399.00|12 ------------> Detail Lines
002|Technican|1800.00|15
003|Mechanic|1500.00|18
.......
Total Amount = 180000.00 ------------> Footer Line
I want to use the SSIS to do this job can anyone explain step by step.
Thanks,
Madhu
View 3 Replies
View Related
Feb 21, 2008
I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.
The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.
I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)
Any option anyone knows will help.
Thanks.
View 17 Replies
View Related
Feb 1, 2007
In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?
In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.
I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.
Here€™s the error info€¦
SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).
View 9 Replies
View Related
Mar 5, 2015
I need to be able to see if the incoming csv file had a head row different than the previous files header row. That will tell me that I have new columns.
View 3 Replies
View Related
Nov 7, 2007
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
http://www.pragmaticworks.com/filepropertiestask.htm
View 5 Replies
View Related
Sep 28, 2006
Hi *,
I'm trying to import a flat file with ~3500 rows into a SQL-DB. SSIS extracts only around half the rows. It leaves out every 2nd row. Anyone had this problem before?
Thanks!
View 1 Replies
View Related
Feb 21, 2007
Hi All,
I want to know is it possible to have source as Flat File and destination as XML
Thanks in advance,
Shagun
View 1 Replies
View Related
