Unanswered: SSIS - problem with data sources when run as job/validated
I have posted the Sybase side of this to the Sybase forum, but it could be that it's a more generic problem with SSIS so I am posting again here with emphasis on the SSIS parts, I hope nobody minds.
I am having a problem running a MS SQL Server SSIS package that talks to Sybase and really need some input as to what the issue might be.
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 Providers for OleDb\Sybase OLEDB Provider" as the provider, and specify my connection name as created in the Sybase ASE 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?
I only get this far because I published the package without validating at the end. If I validate, it fails with the same error.
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. I am developing on the same SQL server that I am trying to run this on.