Another Mysterious issue in SQL 2005 & SSIS

Explain this:

Package runs successfully from BIDS
It runs successfully in SSIS store
It runs successfully when I manually execute the Job

JOB FAILS EVERY FREAKING NIGHT

It'd be easier to explain if you provided more information. Such as an error message.

I would imagine this is something to do with the user that SQL Agent is running under.

I also can't recommend this article highly enough:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

And the SQL Agent is running under the MyNetworkDomain/Administrator account.|||

TheViewMaster wrote:

Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

Exactly. Which is why you should change how you call the package as explained in the article that I linked to above.

-Jamie

|||

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?|||

TheViewMaster wrote:

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?

When you setup the job step, select "Operating System (Cmdexec)" from the 'Type' dropdown box.

-Jamie

|||

Change the job step type from "SQL Server Integration Services Package" to "Operating System (CmdExec)". Just enter the command to run dtexec ("C:\PROGRAM FILES\Microsoft SQL Server\90\DTS\Binn\dtexec") and then add the switches. You can copy all the switches from the command line tab in your SSIS step before you change it to a cmdexec step.

Are any of your connections in your package using sql authentication by chance?

|||Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory
|||

TheViewMaster wrote:

Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory

Have you installed SSIS on the server that SQL Server Agent is running on?

-Jamie

|||Have you checked the NT Event log for any signs of an error? I often forget to check this...

If you set it to execute during the day does it run? If it runs then compare the night failure times with your tape backup schedule. I recently had a maintenance plan failure caused by the nightly tape backups.

If it is still failing then it could be a security problem of some type. I have gotten this exact generic error message from security mis-configurations.

Thanks,
Greg Van Mullem
|||

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed|||

TheViewMaster wrote:

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed

Whereabouts is it installed? Because the C:\Program Files\Microsoft SQL Server\90\DTS folder is where it gets installed to by default.

-Jamie

|||I just dont get it (I used to think myself as a moderately intelligent guy but MS has really made me feel like a complete dumbass whenever I try to start using their new products - talking about that the Vista RC2 installation just hanged on me the other night with no apparent reason) 2 jobs and both have issues running automatically:

1st - runs on BIDS, runs on SSIS, runs as Manual job, Automatic Jobs fail
2nd - runs on BIDS, runs on SSIS - jobs (manual and automatic) always fail

And in do not have that DTEXEC installed on that machine to troubleshoot it according to the article posted

Another day - another load of problems
Thanks BG for making our job so nightmarish that we can excuse the $ spent on software - otherwise normal ppl could do it, rite?|||The management of SSIS is quite non-intuitive in my opinion, there are loads of posts about job failures. They usually have to do with encrypting sensitive information using a user key or the account used to run the job not having sufficient access. You should, however, have dtexec on the server somewhere. Did you do a file search for it?|||I didt search for dtexec - nada
So im copying the DTS/Binn directory from my development pc - let's see if that will help to come closer to solving this mystery