May 3, 2010 at 7:09 am
Hi.
I have created an SSIS package in BIDS which pulls data from Sybase to SQL 2008. This works fine in BIDS, but when I try and execute it as a SQL job it fails with the following 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:56:20 Error: 2010-05-03 13:56:21.54 Code: 0xC0047062 Source: Agent ADO NET Source [1] Description: System.Data.OleDb.OleDbException: Invalid port number Connection does not exist at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction) End Error Error: 2010-05-03 13:56:21.54 Code: 0xC0047017 Source: Agent SSIS.Pipeline Description: component "ADO NET Source" (1) failed validation and returned error code 0x80004005. End Error Error: 2010-05-03 13:56:21.54 Code: 0xC004700C Source: Agent SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2010-05-03 13:56:21.55 Code: 0xC0024107 Source: Agent Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 13:56:20 Finished: 13:56:21 Elapsed: 0.703 seconds. The package execution failed. The step failed.
The DSN seems to be specified to no particular user, but the job acts as if the DSN doesn't exist.
I have set the package to use EncryptSensitiveWithPassword and have set it to use SQL encryption when I run the import. The real problem is probably that the package always fails validation (though some people tell me they don't use the validation because of how often if falsely fails). If I validate during deployment, it basically fails with the same error: "Login failed: connection does not exist".
Any ideas?
Thanks.
May 3, 2010 at 8:03 am
Does the DSN exist on both servers (assuming your BIDS machine isn't your deployed server)? And is it a System DSN on both machines (ie if User DSN it won't be seen by other users).
Steve.
May 3, 2010 at 8:27 am
It's on the same server. The connection is made in the sybase data source adminstrator and is an oledb connection. There seems to be no way to specify whether it is a user or system dsn.
If I choose EncryptSensitiveWithUserKey then it passes validation, but then fails to run as it the job runs under a different user.
May 3, 2010 at 8:44 am
Sorry, missed the last bit before.
Isn't there an option to save sensitive with password? Saving with user key will fail unless you're creating the package as your SQLAgent user. SaveWithUserKey is tied to that user account that created the package.
Steve.
May 3, 2010 at 8:46 am
Yeah, it's the EncryptSensitiveWithPassword option that fails validation.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply