Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Problem starting package using the Agent Expand / Collapse
Author
Message
Posted Tuesday, November 16, 2010 9:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 9:19 AM
Points: 23, Visits: 166
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
Post #1021621
Posted Tuesday, November 16, 2010 10:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 13,565, Visits: 11,375
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1021663
Posted Tuesday, November 16, 2010 10:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 9:19 AM
Points: 23, Visits: 166
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
Post #1021669
Posted Tuesday, November 16, 2010 11:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 13,565, Visits: 11,375
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1021680
Posted Tuesday, November 16, 2010 11:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 9:19 AM
Points: 23, Visits: 166
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
Post #1021694
Posted Tuesday, November 16, 2010 11:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 13,565, Visits: 11,375
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1021709
Posted Tuesday, November 16, 2010 11:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 9:19 AM
Points: 23, Visits: 166
I'll go throw it today an tell you...
Post #1021934
Posted Wednesday, November 17, 2010 5:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 9:19 AM
Points: 23, Visits: 166
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
Post #1022085
Posted Wednesday, November 17, 2010 5:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 9:19 AM
Points: 23, Visits: 166
I'm realizing this section is on sql server 2005, but I'm actually using sql server 2008 R2 64 bits
Post #1022087
Posted Thursday, November 18, 2010 4:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 9:19 AM
Points: 23, Visits: 166
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

Post #1022733
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse