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!
kshumphreysAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
kshumphreysAuthor Commented:
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 RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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/
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

kshumphreysAuthor Commented:

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 RSQL Server DBA & Architect, EE Solution GuideCommented:
Are you able to connect to Oracle machine from your SQL Server machine running SSIS packages..
Kindly test that once.
kshumphreysAuthor Commented:

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.





Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> why is the production job all of a sudeen trying to access the development server?

I hope you or the person managing the packages/servers needs to answer better.
If development server is referenced in the package, then it would reference it by default.
Else try checking for any server name changes or consolidation of servers which might have caused this.

Or else someone would have replaced/changed the config file by mistake( Try checking the config file modified date).

>> 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?

When a job is scheduled ( which is referencing any configuration files) it would load all configured values into memory and would run again with those cached information.
When cache is cleared manually or by Server restart, it would try to load again from the config file which might land up into issues like this..( working fine over a period of time).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kshumphreysAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.