Packages, Jobs and Scheduling issues

I'm starting to have second thoughts about switching to SQL 2005.

After hours of trying, I finally managed to get SSIS packages to run as intended. Now, I need to create a schedule for the package. I create a job, owned by the Local System account, which SQL Server Agent also uses. I can run the Package myself, but when I schedule it, it always fails. After much trial and error, I decided to give up on this manner of running SSIS packages. I decided to create the job as a CmdExec type job instead. When run manually, it woks fine, but when I schedule it, it doesn't run. Now, it's not that it gives an error message, it just DOESN'T run, it appears as "not scheduled" in the Job Activity Monitor, even though the schedule is listed in the Schedules property. How then, can I schedule the command to run? Thanks for the info.

The reason is very simple the local systems account does not have access to network resources anything you run through that account fails. xp_cmdshell is a better choice but you have to create a proxy to admin level account to run the Agent. All you need is covered below post again if you still need help, I know it works and can run for hours like clock work.

http://support.microsoft.com/kb/912911

|||

I usually run the Sql Server Agent under a service account that has the privilges required to run any processing.

You may run into another issue where errors in Analysis Services Commands are not reported when run via the Sql Server Agent. I am in the process of trying to find out if MS is aware of this issue. It appears to be an issue in all versions of SQL 2005 up to and including SP2.

sql

Packages, Jobs and Scheduling issues

I'm starting to have second thoughts about switching to SQL 2005.

After hours of trying, I finally managed to get SSIS packages to run as intended. Now, I need to create a schedule for the package. I create a job, owned by the Local System account, which SQL Server Agent also uses. I can run the Package myself, but when I schedule it, it always fails. After much trial and error, I decided to give up on this manner of running SSIS packages. I decided to create the job as a CmdExec type job instead. When run manually, it woks fine, but when I schedule it, it doesn't run. Now, it's not that it gives an error message, it just DOESN'T run, it appears as "not scheduled" in the Job Activity Monitor, even though the schedule is listed in the Schedules property. How then, can I schedule the command to run? Thanks for the info.

The reason is very simple the local systems account does not have access to network resources anything you run through that account fails. xp_cmdshell is a better choice but you have to create a proxy to admin level account to run the Agent. All you need is covered below post again if you still need help, I know it works and can run for hours like clock work.

http://support.microsoft.com/kb/912911

|||

I usually run the Sql Server Agent under a service account that has the privilges required to run any processing.

You may run into another issue where errors in Analysis Services Commands are not reported when run via the Sql Server Agent. I am in the process of trying to find out if MS is aware of this issue. It appears to be an issue in all versions of SQL 2005 up to and including SP2.

Package scheduling and error trace-

I am scheduling the package to run on the nightly basis everyday using windows scheduled task.
How do i check whether the package ran successfully or it has given any error.

Can anyone please suggest me if I can log the error in some table of SQL server.

PLease suggest the steps.

When building a package in BIDS, have a look at the logging options. See the SSIS menu item, Logging.

You can log to several locations including a table.

I would expect DTEXEC to send the appropriate return code when it failed, so the scheduled job should fail. Unfortunately the windows scheduler is not very advanced, so you cannot capture output or configure the return code. The lack of output and monitoring means I would not use it.

Package configuration, Parent-child packages and Job scheduling

Hi,

I've found this problem that when I change settings in my configuration file it does not automatically apply to all child packages which uses the same configuration file if run from a job in SQL Server Agent. I need to open the package and save it again from BIDS. I use one "load group" package to execute all other packages.

Is there a way from the job configuration to set a setting so the package allways will have the newest configuration?

Regards
Simon
Hi,

Seems like noone has an answer for this.

Isn't it a fundamentally important that changes in configuration files automatically apply to packages that uses them. Am I the only one with this problem?

Regards
Simon
|||I haven't observed it myself. This only occurs when you are running from the SQL Agent? Does it work properly when you run from the command line?|||

When the package is loaded it will lok at the config settings. If you aren't passing entries from the parent to the child then it will get them from the config file (where are you holding the config settings - sounds like xml?).

You seem to be suggesting the parent package gets the correct values but the child packages get old values. I've not seen a package ignore a config file - are you sure the packages are reading the correct files? Are the files on the same server (where the packages are being run)?

Opening the package and saving it again shouldn't affect the config entries unless you look at them - in which case they will be overwritten with the values in the package.

|||

Hi,

I have the same problem with configuration file being ignored when executed ssis package via sql agent job. Have you found the solution to the problem yet?

regards,

mnguyen

|||

mnguyen wrote:

Hi,

I have the same problem with configuration file being ignored when executed ssis package via sql agent job. Have you found the solution to the problem yet?

regards,

mnguyen

If the package is running with the default values, maybe it is not able to find the configuration file. Are you sure it is in an appropriate location? Have you tried explicitly setting the configuration file via the /ConfigFile switch?

|||Thanks both to NigelRivett and jwelch for your suggestions and I'm sorry that I've havn't had the time to reply.

When I return from holiday I will investegate futher...
|||

mnguyen wrote:

Hi,

I have the same problem with configuration file being ignored when executed ssis package via sql agent job. Have you found the solution to the problem yet?

regards,

mnguyen

I spoke with mnguyen offline and we determined that it was an issue with using a relative path to the configuration file.

Package configuration, Parent-child packages and Job scheduling

Hi,

I've found this problem that when I change settings in my configuration file it does not automatically apply to all child packages which uses the same configuration file if run from a job in SQL Server Agent. I need to open the package and save it again from BIDS. I use one "load group" package to execute all other packages.

Is there a way from the job configuration to set a setting so the package allways will have the newest configuration?

Regards
Simon
Hi,

Seems like noone has an answer for this.

Isn't it a fundamentally important that changes in configuration files automatically apply to packages that uses them. Am I the only one with this problem?

Regards
Simon
|||I haven't observed it myself. This only occurs when you are running from the SQL Agent? Does it work properly when you run from the command line?|||

When the package is loaded it will lok at the config settings. If you aren't passing entries from the parent to the child then it will get them from the config file (where are you holding the config settings - sounds like xml?).

You seem to be suggesting the parent package gets the correct values but the child packages get old values. I've not seen a package ignore a config file - are you sure the packages are reading the correct files? Are the files on the same server (where the packages are being run)?

Opening the package and saving it again shouldn't affect the config entries unless you look at them - in which case they will be overwritten with the values in the package.

|||

Hi,

I have the same problem with configuration file being ignored when executed ssis package via sql agent job. Have you found the solution to the problem yet?

regards,

mnguyen

|||

mnguyen wrote:

Hi,

I have the same problem with configuration file being ignored when executed ssis package via sql agent job. Have you found the solution to the problem yet?

regards,

mnguyen

If the package is running with the default values, maybe it is not able to find the configuration file. Are you sure it is in an appropriate location? Have you tried explicitly setting the configuration file via the /ConfigFile switch?

|||Thanks both to NigelRivett and jwelch for your suggestions and I'm sorry that I've havn't had the time to reply.

When I return from holiday I will investegate futher...
|||

mnguyen wrote:

Hi,

I have the same problem with configuration file being ignored when executed ssis package via sql agent job. Have you found the solution to the problem yet?

regards,

mnguyen

I spoke with mnguyen offline and we determined that it was an issue with using a relative path to the configuration file.