asked on

Sql2008 Job Losing Reference to SSIS Config File?

We have a job step running an ssis package with a config file. It has worked for several months. The job started failing on a missing password error.

In researching the problem, it seems that the job was no longer referencing the config file...
it looks to be defaulting to the connection strings are they are defined witihin the ssis package (hence the missing password as the ssis package didn't have the pw).

Re-deploying the package/config file fixed the problem but the cause is unknown.

Any ideas? Anyone ever seen this happen?

Thanks!

Raja Jegan R

Seems like the account under which job executed might have its password expired or changed and hence re-deploying would have fixed the issue.

Posting the exact error encountered might narrow down the reasons further..

kshumphreys

ASKER

Nope, expired passwords were checked first thing.

This is the error message Description: "ORA-01005: null password given; logon denied" but I believe
it is simply noting the failure using the login referenced in the ssis pkg.

Raja Jegan R

>> ora-01005

Oracle error code you obtained clearly denotes that there is a login password failure.
Check password provided for all source destinations once ( at all places in your Package).

http://ora-01005.ora-code.com/

kshumphreys

ASKER

The config file contained the correct login/password for the connection and It's been working fine for months. Then the job seemed to stop referencing the config file and started trying to access the login as defined in the ssis pkg.

The connection definitions in the config file should override the connection definitions defined in the ssis pkg.

My question is not what the meaning of the error message is (I understand the error and why it was generated), but why a job would stop using a config file.

Raja Jegan R

Are you able to connect to Oracle machine from your SQL Server machine running SSIS packages..
Kindly test that once.

kshumphreys

ASKER

The actual error message is somewhat of a red herring... the error is being generated because the production ssis server is attempting to access the development oracle machine (and doesn't have permissions, by design).

The question is... why is the production job all of a sudeen trying to access the development server?
The job has been running in production for months but then mysteriously stopped referencing the config file (where the connections are defined for production)... and defaulted back to the connections as defined in the ssis pkg (where the connections are defined for development).

We're positive the job was trying to access the development machine as we can see the failed logins there.

The question is, what would cause a job to stop using a config file when absolutely nothing realted to this job (connections, logins, passwords, servers, ssis pkg, config file, job) has changed? Everything was checked to make sure something hadn't been accidently modified and all was good.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kshumphreys

ASKER

Responses tended to suggest things I'd already mentioned as having tried.

I think the last solutiion was on the right track - job needing to reload the config file.