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
Whenever i change the SA password to anything else, my SQL Agent services
like jobs, alerts starts falling, and if i attempt to stop and then restart
sql agent service, it will not start again.
My server uses sql authentication for everything...
help...
thanks
vinod
Inside of an SQL Task (below), I have a call to a stored procedure with 7 parms.
[Audit].[spBatchPackage_OnExtract] ?, ?, ?, ?, ?, ?, ?
User:: ParentExecution_Idx Long
User:: Batch_Idx Long
System:: PackageName Nvarchar
System:: PackageID GUID
User:: TargetTable Nvarchar
System:: ContainerStartTime DBTimestamp
User:: DataFilePattern Nvarchar
In my SP I have this:
ALTER PROCEDURE [Audit].[spBatchPackage_OnExtract]
@Execution_Idx int
,@Batch_Idx int
,@Source nvarchar(1024)
,@SourceId uniqueidentifier
,@Destination nvarchar(1024)
,@StartTime DATETIME
,@FileSpecification nvarchar(2)
When I execute the SQL Task, I get this error:
[Execute SQL Task] Error: Executing the query "[Audit].[spBatchPackage_OnExtract] ?, ?, ?, ?, ?, ?, ?" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any help on clearing up this error would be appreciated!
Hi,
I am having problems with an SSIS package whereby the first step of the package is failing. I am getting the following error message:
Unable to connect to FTP server using "FTPLocation".
This only happens when I use a Agent Proxy as the 'Run as' option. If I change it back to SQL Agent Service Account it works perfectly.
For the credentials of the proxy, I have used my own windows login as the identity. And I have full priveleges on the server, ie if I log on to the server I can successfully ftp to the same site and download the file without any problems. So shouldn't the Agent be able to do so too when it uses my credentials?
Any help would be much appreciated.
DrBrain
Getting this error when running a maintenance plan step. The backup steps work fine.
" Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2008-04-15 09:15:03.02 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2008-04-15 09:15:45.02 Code: 0xC0024104 Source: Reorganize Index Task ... The package execution fa... The step failed."
The step is run under the sql service agent account.
We are at SP2 on a 64 bit machine.
Thanks.
Sam
The thing is I am using a DTS package to get data from db2 residing on a s390-mainframe and put it in a sql 2000 db on windows 2000 server..
I have the odbc driver that the server guys gave me..I installed it and then run a batch pgm, which i think does the database set up...in short i set up the DSN's in my local machine..I can use the dsn when i use the sql client...
I can't create a new package and then complete the task..Problem is when i try set up a transform task, i get a memory error...it says that the instruction referenced a memory location that can't be read...
So what i do is i use the import task in sql...which inturn uses the same odbc connection and runs the query on db2..inserts the results into a table in sql..then save the package...Only problem is that i can't edit the transorm task...same memory error...
Any idea why this happens ?? I would like to see how i can get past this using Odbc...
I have a DTS package in SQL Server 2000 that has a Send Mail task to shoot me an email when it completes. When I tested this manually it ran fine however when it runs as a scheduled job in the SQL Agent the Send Mail task fails.
Prior to being able to send emails I installed Outlook so I could build a MAPI profile. With the MAPI profile there I am able to create and at least manually test a Send Mail task. From experience I believe this issue arises due to the fact that when I'm logged in Outlook is running but when I'm logged out of the Server, it is not running and so the task fails.
Using SQL 2005, SSIS, I have an SSIS package which is scheduled as a SQL Server Agent job. The package contains a Send Mail task. This task fails when SQL Agent runs the package. The task succeeds when executed interactively from the package, logged on as the SQL Agent service account. The package as a whole succeeds when executed from "Start Debugging". An Execute SQL Task in the same package containing an sp_send_dbmail statement succeeds when SQL Agent runs the job, and the same sp_send_dbmail succeeds in Mgmt Studio query window. All these events all logged onto the server as the SQL Server/SQL Agent service account.
I would appreciate ideas on what else to check for here, so that I can use the Send Mail SSIS task in packages which are run by SQL Agent jobs.
Thanks, Holly
I'm trying to set up Service Broker Services on SQL 2005 x86. I've got two services set up, and a stored procedure associated with one of them.
View 3 Replies View RelatedI'm running SQL Server 7.0 SP3 and having trouble with DTS.
I have an Execute SQL Task that runs several stored procedures. When one of the stored procedures fails, the Execute SQL Task just terminates without failing.
I found a knowledge base article (Q238523) dealing with this situation but it was supposedly fixed in SP2 - I have SP3! The other work around suggested, issuing a SET NOCOUNT ON, does not always work.
Has anyone else run into this or have any other suggestions.
I hate the thought of spending days doing another work around in order get basic DTS functionalty to work as it should!
Thanks!
Jerry Dunn
gdunnjr@yahoo.com
I'm "trying" to set up Replication. The Publishing/Distribution server is in one domain, and the subscribing server is in another. Both domains are fully trusted.
The synchronization step builds the .tmp file, but the repl_subscriber Distribution task bites the dust with an error message, "28000[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed".
The setting on the distribution options dialog box is ODBC, SQL Server. I'm not using a special login/password. I've even tried putting a user name and password there, and it doesn't work. The ODBC connections test out fine on both servers. Any suggestions where I've gone wrong?
Thanks,
Layne
I have ForEach Loop using Foreach File Enumerator. Within this loop I have SQL Task containing an Insert statement. When I run the Insert statement in query builder the transaction inserts data into a table as expected.
However, when actually running the process I am getting the error message:
Executing the query "INSERT INTO dbo.TEST_TABLE
..." failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I currently have the ResultSet to "None" and have defined the parameter I am using. Where the process seems to joke is on my file_Name variable will I am trying to insert only part of the file name.
My insert statement looks as follows:
INSERT INTO dbo.TEST_TABLE
(IID, AN8, File_Type, IB_OB, File_Name, Processed_Flag, ReceiptDateTime)
VALUES
(
'1111',
'123',
'888',
'IB',
RIGHT(LEFT(?, LEN(?) - 4), LEN(?) - 24),
'I',
GETDATE())
I have a package that fails as soon as it hits the first Data Flow that contains a Derived Column task. The task takes three date columns and looks for a date of 6/6/2079. If it is there, it is replaced with a NULL. This task worked fine until I installed the Non-CTP version of SQL 2005 SP1, earlier today. (I went from RTM 9.0.1399 to SP1 9.0.2047)Does anyone have any ideas?
Here is the error I am trapping:
An error occurred while evaluating the function.
The "component "Update Max Date Value to NULL" (346)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "WeekEndingDate" (455)" specifies failure on error. An error occurred on the specified object of the specified component.
The ProcessInput method on component "Update Max Date Value to NULL" (346) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Thread "WorkThread0" has exited with error code 0xC0209029.
I tried to configure the error output to ignore the failure. The task succeeded. It did not insert anything, however, for the three columns in question. It then failed on the next Data Flow that contained a Derived Column task. I tried to pre-populate the columns with a default date, but no go. Any help would be appreciated.
I have an SSIS package which calls a command line app.When run in BIDS, it executes normally. The command line app is passed the arguments, does what it needs to do.When called as a SQL Agent Job (by the agent, or by me) it fails when calling the app, giving an exit code of 2 (which is an exception trapped by a try-catch). The SQL Agent service is running under my user (it's a test environment). The argument passed (from the log) is valid, and I've run it against the app, it provides the appropriate output.I can't for the life of me figure out what's going wrong.The app is passed an argument of a path and a password, and applies the password to the file, using interop.
View 13 Replies View RelatedI have Windows XP Professional Version 2002 Service Pack2.
I Have:
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
Service Pack is failing to Install CLIENT COMPONENTS; message is:
Product : Client Components
Product Version (Previous): 2047
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQLTools9_Hotfix_KB921896_sqlrun_tools.msp.log
Error Number : 29549
Error Description : MSP Error: 29549 Failed to install and configure assemblies c:Program FilesMicrosoft SQL Server90NotificationServices9.0.242Binmicrosoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2147024894
Error message: The system cannot find the file specified.
Error description: Could not load file or assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
This situation, I am guessing, also preventing me to run import/export from the SQLServer Client.
Any help is appreciated. Thanks.
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'm programmatically loading a package that was created with VS 2005. The last task in the package is a Send Mail Task. It has two properties, FileAttachments and ToLine, which are set to expressions whose values come directly from package variables. The package runs in debug and non-debug (under VS) correctly.
View 3 Replies View RelatedWhen any of
my tasks or script tasks in my control flow, or data flow, have an
error, the entire package, and then the calling package fail, as far as
I've seen, through some CTP versions, beta versions, and the release
version of SQL Server 2005.
But, I've just made a change, in an
sproc called from an ExecuteSQL Task in a PreExecute event attached to
a DataFlow, and that ExecuteSQL Task is reporting an error in the
output window, but the package execution is not being stopped. Why not?
I see something like so
Error: 0xC002F309 at MyExecuteSqlTaskName, Execute SQL Task: ....
Task failed: MyExecuteSqlTaskName
Warning:
0x80019002 at OnPreExecute: The Execution method succeeded, but the
number of errors raised (1) reached the maximum allowed (1); resulting
in failure. This occurs when the number of errors reaches the number
specified in MaximumErrorCount. Change the MaximumErrorCount or fix the
errors.
repeated many times in the output stream, and the same thing for the PostExecute event attached to the same object.
I'm disappointed, because I want errors propagated upwards, as I'm used to.
I
looked, and as far as I can tell, all my ExecuteSQL tasks (in events
and in the regular control flow) have the default settings of
FailPackageOnFailure: False
FailParentOnFailure: False
MaxErrorCount: 1
Do
I have to go revise these settings on every ExecuteSQL Task in every
event handler in every SSIS package? (That will be exceedingly
tedious.) Do I set these on the ExecuteSQL task inside the event? Do I
set these on the event handler itself?
Hi,
I am using the SMTP Task to send an email to about 200 individuals with their own specific attachment. If it cannot find the attachment the SMTP Task fails.
Is there a way to log the failure and have it keep going? That way it will still process everyone, but I would get a list of those who didn't receive the email because there was a problem.
I looked at Jamie Thompson's blog and he had a great script to send out emails. But if I use that, I haven't yet figured out how to pass the filename variable to it and attach the file. Even if I use that script, how do I deal with the errors?
Thank you for the help.
-Gumbatman
When updating automaticallly, this update always fail, I have put the configuration manager /server browser : the only one that was disabled, on manual (I have not the automatic option for this one) but nothing works, what can I do ? This is a new laptop I have got (XP pro) and I am going through the process of updating many things, only this one fails, any help appreciated.
View 5 Replies View RelatedI am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.
I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.
I hope someone can help me work through this.
Thanks in advance,
SK
SSIS package "Package3.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Task failed: Bulk Insert Task 1
Task failed: Bulk Insert Task
Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package3.dtsx" finished: Failure.
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
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
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
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
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.
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.
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 View Related
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
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
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 RelatedHi 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
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.