November 16, 2010 at 9:54 am
Hello,
I have a package which pull rows from Oracle into Sql Server.
It works fine in BIDS
And it works fine if I launch it manually, from SSIS console.
If I start it with Sql Server Agent I get the following error:
Executed as user: ADA\Administrator. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 17:41:29 Error: 2010-11-16 17:41:30.53 Code: 0xC0047062 Source: Data Flow Task ADO NET Source [1] Description: System.OverflowException: Arithmetic operation resulted in an overflow. at System.Data.Odbc.OdbcStatementHandle.RowCount(SQLLEN& rowCount) at System.Data.Odbc.OdbcDataReader.GetRowCount() at System.Data.Odbc.OdbcDataReader.FirstResult() at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper) End Error Error: 2010-11-16 17:41:30.53 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: component "ADO NET Source" (1) failed the pre-execute phase and returned error code 0x80131516. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 17:41:29 Finished: 17:41:30 Elapsed: 0.797 seconds. The package execution failed. The step failed.
Please note that I have no "sensitive" in the package, because the package use package configuration and connection strings are passed throw an XML file and a SQl Server Configuration Table
The Agent service is started with the administrator account.
Very appreciated any idea
Thankyou
carlo
November 16, 2010 at 10:41 am
I'm certainly not an Oracle expert, but I have found the following topic:
http://forums.oracle.com/forums/thread.jspa?threadID=327112
Maybe the last reply can be useful?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 16, 2010 at 10:52 am
Thank you, but it doesn't help.
My package is a very simple one, only for architecture test purpose.
So there's no functional problem, and it actually works if I launch it manually (in BIDS and in SSIS).
The problem arises when I schedule it in the Sql Server Agent.
I have tried changing the Agent service account, but it doesn't help.
Desesperating...
Thank you in advance for any new idea
carlo
November 16, 2010 at 11:05 am
Are you 100% sure that all the configurations come through correctly when executed from SQL Server Agent?
Maybe you can quickly add a task in the package that writes all the configuration settings to a file, so that you can manually check if everything is configured OK.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 16, 2010 at 11:15 am
I agree, that could be the point. Could you explain me how to write to a file the configuration details?
Thank you very much
carlo
November 16, 2010 at 11:42 am
Well, you could store the used configurations in string variables and then write the values of those variables to a text file using a script task.
Or, you can store the values of those variables in a temporary table using an Execute SQL Task.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 16, 2010 at 11:29 pm
I'll go throw it today an tell you...
November 17, 2010 at 5:31 am
I wrote the username variable (executing the package) into a table, and it shows: ada\administrator
Which other information would I need to know?
No idea at the moment
thank you
carlo
November 17, 2010 at 5:32 am
I'm realizing this section is on sql server 2005, but I'm actually using sql server 2008 R2 64 bits
November 18, 2010 at 4:05 am
Ok, solved in two steps:
- use "Ole BD for Oracle" instead of "ODBC"
- check “Use 32 bit runtime” in step execution option
Thank you
Carlo
November 18, 2010 at 4:37 am
Glad that you solved the problem.
Why was the 32-bit option necessary?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2010 at 3:17 pm
The Oracle DB is 32 bit.
When I was using ODBC, I was able to choose for the 32bit System DSN (with odbcad32.exe located in %Windows%\SystemWOW64\ ).
And, anyway, checking that option didn't solve the problem.
Now, using OLE DB, I can't choose betwen 32 or 64. So I thought again about checking that option. And that made it work.
Carlo
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply