Scheduled SQL Server Agent SSIS Package Job Problem

HELP! I have been banging my head against a brick wall on this one all this morning AAAAAAGGGHHH!

1. I have an SSIS package that runs a simple SQL script and then updates a few tables by downloading some XML of the web. It runs fine when I kick it off manually under SSMS.

2. I created a SQL Server Agent job to run it every day. This always fails. The error information in the log is useless ("Executed as user: domain\user. The package execution failed. The step failed." - I had already figured that out!). It fails almost straight away, and when I enable logging for the SSIS package, no info is ever logged (text file, windows event log, whatever).

3. Out of desperation I have changed Agent to run under the same domain user account that I created the package with. No use.

My questions:

1. How can I get more detailed logging from SQL Server Agent?

2 Any ideas about why it's failing in the first place.

Many thanks in advance.

Ben

If you store the package in the filesystem you should give read/write and maybe execute permission on the file packagename.dtsx to the account sql server agent is running.

To all Microsoft people:

I propose to put on top of the forum one task with solutions for those permission issue as a lot of the posts are because of wrong permission settings. I had this problem too in the past and I would appreciate such a "top" post

Regards

Nobs

|||

Thanks for your quick response. The SSIS package is actually stored in SQL Server though (in Stored Packages->MSDB).

Any more ideas? How about the more detailed logging for SQL Server Agent?

Thanks again,

Ben

|||

Have a look at some issues here-

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

If we get some answers (mark it as so) then I'll get it locked at the top, that is one of the goals for these forums.

|||

Thank you!

Running the SSIS package as a command line step instead of an SSIS Package step enabled me to obtain all the error info I needed to fix the problem.

Why does Microsoft not give full error information for SSIS packages run through the SQL Server Agent job scheduler (even in SP1)?!?!

Many thanks indeed for helping me to solve the problem.

Ben

|||

Give them the feeback- http://lab.msdn.microsoft.com/productfeedback/default.aspx

|||

Can you explain what "running as a command line step" means? I am having the same problem as you, my SSIS packages won't run as a SQL Agent job, and "the job failed" gives me no information.

Thanks.

|||

JustJFe wrote:

Can you explain what "running as a command line step" means? I am having the same problem as you, my SSIS packages won't run as a SQL Agent job, and "the job failed" gives me no information.

Thanks.

When you are creating a SQL Agent job; you have to create 'steps'; well, there is a dropdown list for 'Type' where you can select 'Operative System (CmdExec)' that is what "Running as a command line step" should mean. This approach is helpful under some scenarios like wehn you want to run your package in a 64-bit machine in a 32-bit mode.

BTW, SQL Agent is not that good providing error descriptions; but you can enable logging in you packages, so you can have more details.

I hope this clarify your doubts.

Rafael Salas

|||

Thanks for the quick response - I will change the Step as you listed - currently, it's an SSIS package type of step.

How do I "enable logging"? (My DBA is out on maternity leave, and I'm trying to cover - my VS skills are good, but SQL Server 2005 is Beginner...)

Thanks!

|||

My Suggestion about using logging may require changes in the packages; I would check fisrt if logging is not being already used first; the table SSIS uses for logging, by default, is sysdtslog90 but that could have been changed by a custom log table; in both cases is something that you can check by opening the packages.

Rafael Salas

|||

By default SQL Agent is not great at giving you output as you say, the View job History stuff is truncated. The whole point of my use CmdExec step recommendation, as illustrated in the link, is that you can then turn on the step level logging in SQL Agent, either to text file or SQL table. When using DTEXEC this means you get the same much the console output in your log files as you would get if running in BIDS looking in the output window, or exactly the same as if using DTEXEC from a command prompt and watching what comes out. SSIS logging is good if the package is running, but what happens if the package cannot be found, or permissions prevent access to the package file even? You will not get SSIS logging, or anything useful in the Job level history. The step log however will have the information you need. Check out the link I posted for an illustration of this which solved an annoying file permission issue for me, when the package could not even be loaded.

Personally I use both logging and a CmdExec steps with step logging.

|||In VS, the package has no logging options - I'm guess that means it's not turned on. Also, there is no table sysdtslog90 in our SQL Server. Does that help direct your answer?|||Thanks for the quick reply, but the link you posted does not have any information on how to set up logging or how to find where the messages are listed. I still only get "job failed" in the Job History screen.|||

DarrenSQLIS wrote:

SSIS logging is good if the package is running, but what happens if the package cannot be found, or permissions prevent access to the package file even? You will not get SSIS logging, or anything useful in the Job level history.

That is a good point...

I have not used the combination you described before: CmdExec and step level logging in SQL Server Agent but seems to be a powerfull tool to debug Agent execution issues.

Thanks

Rafael Salas

|||

Never mind the "Job History screen", can you find the Advanced tab on the job !STEP!, if so set some logging there.

To get started with SSIS logging, have a look on the SSIS menu in VS. You need to ensure the package has the focus to see the SSIS menu, it has a habit of hiding.