SSIS Solution doesn't work with SQL SERVER Agent Job Scheduler

[dnfrantum]

I have an SSIS Solution (Package) that works fine when I run it from BIDS, but when I run if from the SSA Job Scheduler, I get a positive response, but it doen't run the package. I have tried to troubleshoot it, but now I am lost. Has anyone else had a similar experience? If so, could you provide some insight as to how you resolved it.

Thanks in advance,
Donald

 

[paulkeys]

A few questions...

Where are you running the package from - File System, SQL?

What is the package protection level for the package you're trying to run.

What version of SQL Server are you using (2005/2008) and is it 32-bit or 64-bit.

What are the data sources being used in the package (are any of them oracle?)

I've had issues when using agent to run ssis packages, and they are usually due to one or more of the above

Paul

 

[dnfrantum]

I am running them from File System.

How do I determine the package protection level?

I am running 2005 on 64 bit server.

Data Sources are MS 2005 and MySQL 5.1

Thanks for your comments. Would it be more effective to run these packages from the SQL or the package store in SSIS?

Donald



Thanks in advance,
Donald

 

[paulkeys]

Hi,

No, running them from the file system is fine. Are you trying to run them from within the solution folder, or are you deploying the packages to a 'deployed' area?

The package protection level is set within BIDS - can be thinks like 'Don't save sensitive', 'encrypt sensitive with user key' and a few others.

Are you using a config file for the package? One issue may be that the connection info for the data sources may be being stored within the package, and this is encrypted and thus not visible to the user running the package via SSA

Paul

 

[dhulbert]

I would have a look at the user account that the package is running under in Scheduler.

The Job is obviously Ok as it works when you run it in BIDS. What happens if you execute the Scheduled Job manually?

It maybe that the Scheduler account doesn't have full access to one of other of your data sources.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[plevey]

Have a look at Package Protection again. It's likely that the password is not being saved. Set it to Encrypt sensitive with password and set the password. Then edit the step in SQL Server Agent job and apply the same password

If fishing was easy it would be called catching