August 11, 2014 at 9:17 am
Dear All,
I have a requirement where I need to fetch data from Cache database and load data to SQL 2005. I installed KB SQL Odbc driver and in SSIS I have created a ODBC source to connect the cache database. It works fine in BIDS window now. When the same package is deployed and ran through a job I get below error. Please help.
Message
Executed as user: XXX\XXX01. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 7:34:06 AM Error: 2014-08-11 07:34:06.39 Code: 0xC0047062 Source: Load ADT Table - Current Date Fetch Cache data [1] Description: System.Data.Odbc.OdbcException: ERROR [28000] [Knowledge Based Systems, Inc.][ODBC Driver][05.01.11.11]SQLDriverConnect: Invalid authorization specification ERROR [28000] [Knowledge Based Systems, Inc.][ODBC Driver][05.01.11.11]SQLDriverConnect: Invalid authorization specification ERROR [28000] [Knowledge Based Systems, Inc.][ODBC Driver][05.01.11.11]SQLDriverConnect: Invalid authorization specification ERROR [28000] [Knowledge Based Systems, Inc.][ODBC Driver][05.01.11.11]SQLDriverConnect: Invalid authorization specification at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.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.Odbc.OdbcConnection.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: 2014-08-11 07:34:06.39 Code: 0xC0047017 Source: Load ADT Table - Current Date SSIS.Pipeline Description: component "Fetch Cache data" (1) failed validation and returned error code 0x80131937. End Error Error: 2014-08-11 07:34:06.39 Code: 0xC004700C Source: Load ADT Table - Current Date SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-08-11 07:34:06.39 Code: 0xC0024107 Source: Load ADT Table - Current Date Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:34:06 AM Finished: 7:34:06 AM Elapsed: 0.171 seconds. The package execution failed. The step failed.
November 20, 2014 at 9:10 am
I believe what is happening is that your login credentials for your CACHE database aren't being stored with the SSIS package when you deploy, so it fails to authenticate properly when you execute the package as a job.
When deploying your package, make sure the Package Protection Level is set to the following:
"Rely on server storage and roles for access control"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply