SSIS Job run in SQL Agent

Question

SSIS Job run in SQL Agent

Jason Coleman

SSCrazy

Points: 2187
More actions
February 28, 2006 at 4:40 am

#189540

I'm going crazy with this.....

I have created a simple SSIS package that tests an FTP transfer. Works fine if I run from the Development studio - no problem.

If I then create a job in the SQL Server agent to run the job, and add a schedule for it to run daily, it fails every time! Doesn't even start! Even starting the new job manually without the schedule causes it to fail!
What am I doing wrong? The logs dont give me anything apart from "Job failed to Start". I have a feeling its security related, but I created the package and logged into the SQL server with the same crdedentials.

Any help would be great

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jamie Thomson SSChampion Points: 11805 More actions · Answer 1

What logs are you talking about? Agent logs or those provided by a SSIS log provider? If its the former then you should read this article: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html as you will then get more detailed information.

The SSIS logs should contain more useful debug info. Are you using logging?

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Jason Coleman SSCrazy Points: 2187 More actions · Answer 2

Thx for the info, but solved the issue. The sql server agent was running under its own credentials, and I had ceated the package under my credentials which have higher access rights.

Updating the sql server agent user credentials fixed the problem.

Usman Tanveer-239283 Ten Centuries Points: 1104 More actions · Answer 3

Jason can u plz tell me how did u create the SQL Agent job for package, i need a detailed reply, what commands or functions did u use for packages.

I know how a to create a SQL agent job and its scheduling for a simple DB procedures so i have some idea of SQL agent jobs and its scheduling.

Jamie Thomson SSChampion Points: 11805 More actions · Answer 4

Usman,

Read the article that I linked to above!

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Usman Tanveer-239283 Ten Centuries Points: 1104 More actions · Answer 5

Jamie,

U also reffered me this article in my post, and i opened that link but its just about the explanation of the erros of Scheduling packages.

What i want to know is what commands do we use to create a SQL Agent job for DTS packages. OR how do we configure SQL Agent job for DTS packages.

Jamie Thomson SSChampion Points: 11805 More actions · Answer 6

Taken from the article linked to above:

"Start by changing to the Operating system (CmdExec) sub-system"

That's what you should use"

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Jason Coleman SSCrazy Points: 2187 More actions · Answer 7

No I have hit a different problem although related to the above. If I have an FTP step in my SSIS package it fails under the Server agent, although if I run from the development studio, it works fine.

I have tried running under a CmdExec type and a SSIS type in the job steps, neither runs successfully. However in the job history I find that the failed package has the message that "the system cannot find the fie specified" - strange as all I am trying to do is receive files which are stored on an FTP site in another location.

Any pointers?

Thx

J.M.Jarrell Grasshopper Points: 11 More actions · Answer 8

still fighting with this issue - please indicate which credentials got updated, and where/how that was accomplished...?

Thx!