| Author |
Topic |
|
prpball
Starting Member
19 Posts |
 Posted - 2008-01-25 : 14:23:51
|
| I have an SSIS package that runs just fine in BI but when I go to run it as a job I get this error message:SQL Server Scheduled Job Status: Failed - Invoked on: 2008-01-25 12:23:08 - Message: The job failed. The Job was invoked by User. The last step to run was step 1 (Step 1).I have created a credential with domain admin rights and a proxy that uses the credential. The domain admin account is a member of the SQLServer2005SQLAgentUser group. The package protection level is set to EncryptSensitiveWithKey. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-26 : 00:36:09
|
| Most likely permission issue. What does the package do? What's sql agent service account? Try turn on package logging to get more details. |
 |
|
|
prpball
Starting Member
19 Posts |
Posted - 2008-01-28 : 08:11:12
|
| Package exports table information from SQL to an Access db on a different server. I am running the job as an domain admin so there should be no problem with permission across the network and the package was created by the domain admin, so if it is permissions I don't know where to start since it has the highest permissions allowed.I'll try turning on package logging and see if that helps. |
|
|
|
prpball
Starting Member
19 Posts |
Posted - 2008-01-28 : 09:49:33
|
| I enabled logging on the package in BI and I got no errors. I package runs fine in BI but not when I attempt to run it in a job. I believe it maybe a permissions issue but I don't know where to begin. The created of the package and executer of the job are both the domain admin. I even put the Access db on the C:\ drive of the sql server and still no luck running the job. The event viewer still comes up with the same error.I have other packages that run just fine but they are converted DTS packages. This is the first SSIS package I have created from scratch, so maybe I am doing something wrong. |
|
|
|
sparkle
Starting Member
7 Posts |
Posted - 2008-01-28 : 12:29:57
|
quote:
Originally posted by prpball
package runs fine in BI but not when I attempt to run it in a job.
I have the same issue going on. I have created a few other packages that run fine as jobs. My SQLServerAgent user is a local admin on the server that runs the job.I get no errors even when I turn on logging. I separated the task that doesn't complete in it's own job, and it executes fine within the Integration Services import area (as well as in BI). It still won't run as a job. I've tried changing the owner within the package to match the Sql Agent user and have tried the various encryption settings. Nothing works yet.Any advances you have, please post! |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2008-01-28 : 14:36:33
|
| check out this kbhttp://support.microsoft.com/kb/918760 |
|
|
|
prpball
Starting Member
19 Posts |
Posted - 2008-01-29 : 08:12:55
|
| I have already read that article and noting worked. I have even set the owner of the job to SA and still it fails. Maybe if someone could tell me what everything should be set to, to make a job run successfully then I could look at mine and fix it.I don't know if it matters but I am exporting data from sql into an access database on another server. I have even brought those databases over onto the sql server desktop and tried that and it still bombs. I have a maintenance plan that work just fine.I have even tried using the export wizard and scheduling it to run as a job and even it fails.There has to be something I am missing. |
|
|
|
sparkle
Starting Member
7 Posts |
Posted - 2008-01-29 : 09:54:23
|
| Yeah, I read it too. I used the Dtutil.exe, which seemed to do nothing. I'll try again.I guess mine is slightly different. The job does run, but never finishes the last task. So I separated that last task into it's own job, which really never errors out. I've started looking into setting up configuration files, since I need to tell the job that the package now has a password, but haven't finished the research.It doesn't make sense that the last task within the same package doesn't have permission to run (only in a job, that is), if it IS a permission issue. |
|
|
|
sparkle
Starting Member
7 Posts |
Posted - 2008-01-29 : 12:17:33
|
| OK! I've got it working.This site helped:http://bi-polar23.blogspot.com/2007/12/ssis-and-sql-server-agent.htmlBasically setup a proxy account. I used the same domain user that is the user for SSIS, SQLAgent, etc. What I read somewhere is that even domain admins can't decrypt the encryption in SSIS packages. This proxy account can, I guess.Since mine wasn't erroring, just never finishing, it was helpful to 'Use a CmdExec job step to execute DTEXEC.EXE' (The rest of the instructions are on the site listed in the above paragraph). Finally an error; which stated 'The process could not be created for step 1 of job (reason: The system cannot find the file specified)'. This wasn't the exact reason, but I got an idea for the next step:I logged onto the server as that domain user (since I had made it a local admin, and it was now also the proxy account) and tried to run it as that user. For my particular situation, it was hanging on a window for a password for the FTP site. duh. I had thought that the FTP site had our domain ip's listed, so didn't think this would have been an issue.Hope this helps! |
|
|
|
prpball
Starting Member
19 Posts |
Posted - 2008-02-04 : 11:53:07
|
| Thank you all for your help but I don't think any of these are my problem. I think it is the fact that I am using a mapped drive for the Access db locations. I have changed them to UNC locations and the job seems to run fine.I had initially setup a proxy with domain admin creds. The package running in BI was owned by the same user as the proxy creds, a domain admin account. Even with all of this the job still would fail. So I tried running the package using DTSUtil and that is where I realized there was a problem. The error code I kept getting was about one of the steps in the package, so I took a closer look. It was one that was trying to access the Access db, so I changed the connection string from a mapped drive to a UNC and that worked.I have run into this problem once before but I thought I had already tried using a UNC name without success, but now it works. |
|
|
|
|
SSIS Package Fails when run as Job