Unanswered: ASE Data Source issues with different user?
I am having a problem running a MS SQL Server SSIS package that talks to Sybase and really need some Sybase experts' input as to what the issue might be as it seems to be a problem with the Sybase connection.
I have been building an SSIS package in MS SQL Server 2008 to pull data from a Sybase ASE 15 database. I have used the Sybase ASE Data Source Administrator and created a connection using the "ASE OLEDB Provider by Sybase" to my database. I then create a new connection manager in SSIS and specify the .Net Provider of OleDb\Sybase OLEDB Provider as the provider, and specify my connection name as created in the Data Source Administrator in the "Server or file name" box. This all tests fine and works great and can pull data over when I debug in the studio...but does not work when run as a SQL job.
The error I get is: Invalid port number: Connection does not exist.
This is the full error:
Executed as user: MYDOMAIN\SQLAgent. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 13:46:13 Error: 2010-05-04 13:46:13.89 Code: 0xC0047062 Source: Agent ADO NET Source  Description: System.Data.OleDb.OleDbException: Invalid port number Connection does not exist at System.Data.OleDb.OleDbConnectionInternal..ctor(Ol eDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateCon nection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at Microsoft.SqlServer.Dts****ntime.ManagedHelper.Get ManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts****ntime.Wrapper.IDTSConne ctionManager100.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper100 wrapper, Object transaction) End Error Error: 2010-05-04 13:46:13.89 Code: 0xC0047017 Source: Agent SSIS.Pipeline Description: component "ADO NET Source" (1) failed validation and returned error code 0x80004005. End Error Error: 2010-05-04 13:46:13.89 Code: 0xC004700C Source: Agent SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2010-05-04 13:46:13.89 Code: 0xC0024107 Source: Agent Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 13:46:13 Finished: 13:46:13 Elapsed: 0.765 seconds. The package execution failed. The step failed.
The only thing I can think is that the connection in the Sybase ASE Data Source Administrator was not created using the SQLAgent account. Would this make a difference? If not, what could the problem be?
As I said, this works fine in the BIDS studio where the SSIS package is developed, it's just when I try and run the job that it fails.