SSIS package works but fails when run as a SQL Server job.
-
I have an SSIS package that runs fine from Visual Studio. It runs fine when parked in a folder and double clicked. It fails when run as a SQL Server job.
Specifics: SQL Server 2008 R2.
The job is owned by "sa". The connection strings to the remote datasource are using my credentials, not those the job owner, "departed.dba."
The job is reaching into NetSuite (Oracle platform) using a 32 bit ODBC connector. Here is the relevant error message from the job history.
[font="Tahoma"]Message
Executed as user: mycompany\departed.dba. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4000.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:46:02 AM Error: 2016-03-18 10:46:04.24 Code: 0xC0047062 Source: Extract from NetSuite ADO NET Source [16]
Description: System.Data.Odbc.OdbcException: ERROR [S1000] [NetSuite][ODBC 32bit driver][OpenAccess SDK SQL Engine]You have entered an invalid e-mail address, password or context (AccountID, RoleID) information. Please try again. Error ticket# ilxxtdjuun0tbtpbwlqi[221]
ERROR [01S00] [NetSuite][ODBC 32bit driver]Invalid attribute in connection string: timeout. ERROR [01S00] [NetSuite][ODBC 32bit driver]Invalid attribute in connection string: timeout. ERROR [S1000] [NetSuite][ODBC 32bit driver][OpenAccess SDK SQL Engine]You have entered an invalid e-mail address, password or context (AccountID, RoleID) information. Please try again. Error ticket# ilxxtdjuun0tbtpbwlqi[221] 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: 2016-03-18 10:46:04.24 Code: 0xC0047017 Source: Extract from NetSuite SSIS.Pipeline Description: component "ADO NET Source" (16) failed validation and returned error code 0x80131937. End Error Error: 2016-03-18 10:46:04.24 Code: 0xC004700C Source: Extract from NetSuite SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2016-03-18 10:46:04.24 Code: 0xC0024107 Source: Extract from NetSuite Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:46:02 AM Finished: 10:46:04 AM Elapsed: 2.137 seconds. The package execution failed. The step failed.[/font]
-
your job is probably running under the context of a specific user (operator) in SQL Agent.
If you edit the job step calling the package, what is the name of the "Run As" operator?
you probably have one that is related to the departed dba, and need to create a new one.
That involves create a credential, creating an operator ties to the credential, and granting access to the SSIS subsystems to the new operator
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell,
I've tried both option available to me, same result. No Bueno.
-
in that case, I'm thinking the issue is most likely that you've included a username and password in the connection, instead of letting the security be handled by the AD user calling the package.
actually, i think it has something to do with sending an email, base don this error:
invalid e-mail address, password or con..
the issue is the same as if it was a connection manager; sensitive passwords get erased.
if it was a connection manager issue,
it probably looks something like this:
depending on your encryption for the package, the password will be erased if anyone other than the package creator calls the package....so you get a blank password, and an invlaid login
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply