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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy