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.

Packages related with its jobs...

Hi,

I have diferent jobs scheduled in the system but I cant find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)

DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A 4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB 376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE923 7DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E7 0F47EA7C01FB44CB28

I would like to know the Package name related to.

Any help would be very appreciated.

RegardsHi,

I have diferent jobs scheduled in the system but I cant find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)

DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A 4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB 376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE923 7DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E7 0F47EA7C01FB44CB28

I would like to know the Package name related to.

Any help would be very appreciated.

Regards

Try:

select * from sysdtspackages where id = '[package id]'

[edit: d'oh, I should read SQL BOL first! The /~Z is an indicator that the string that follows is encrypted. I do not know SQL's encrption algorithm so I'm afraid I can't really help here.]|||thanks. I have tested the sysdtspackages table but I cant find any useful information to relate a job with the package that is running.

Meanwhile I will try SQL BOL.

Regards|||Is the dts packagae writing a log file in the logs folder of the mssql folder? If not, modify the dts package to write the log. Then, aftter the dts packagees execute, check the log. It will hold the clear text name of the package and the results of each step in the package. You should be able to correlate the run time and the associated package. And if you have to delete old logs, the dts package will recreate them on the next execution.

Package could not be loaded - SQL Server Agent

I'm having an issue with executing an ssis package using SQL Server Agent. I have 7 packages that I've got 7 separate jobs for. I also have 7 separate stored procedures that each call the appropriate job using sp_start_job. We are using BizTalk to execute these 7 stored procedures, which in turn start a job, which in turn will call an SSIS Package to run. When we execute the 7 jobs spaced apart, i.e. 2 or 3 seconds apart, they all run fine. When we BizTalk executes them all at the same time, within milliseconds of each other, sometimes they all run fine, sometimes a few of the SSIS Packages end up with the error "The Package could not be loaded" in the SQL Server Agent history log.
Is there an issue when trying to execute multiple Packages all at the same time? It's not like I'm calling the same package 2 times, they are all different packages. Can anyone shed any light on this?
Thanks,
AndyI tried running 7 jobs each running differerent package, and everytime 1 failed with message package execution failed. With a second delay they passed always. I have opened a tracking bug for this.

There is workaround for this.

Set retry_attempts(also retry_interval using sp_update_jobstep) to any value other than default which is 0 .

Thanks for reporting this.

Thanks,
Gops Dwarak|||Thanks Gops. I was trying to avoid using the re-try attempts, but I guess for now I'll have to. Hopefully this will be fixed in SP1!

-Andy

Package could not be loaded

Hello,

I'm having an issue with executing a package using SQL Server Agent. I have 7 packages that I've got 7 separate jobs for. I also have 7 separate stored procedures that each call the appropriate job using sp_start_job. We are using BizTalk to execute these 7 stored procedures, which in turn start a job, which in turn will call an SSIS Package to run. When we execute the 7 jobs spaced apart, i.e. 2 or 3 seconds apart, they all run fine. When we BizTalk executes them all at the same time, within milliseconds of each other, sometimes they all run fine, sometimes a few of the SSIS Packages end up with the error "The Package could not be loaded" in the SQL Server Agent history log.
Is there an issue when trying to execute multiple Packages all at the same time? It's not like I'm calling the same package 2 times, they are all different packages. Can anyone shed any light on this?
Thanks,
Andy

There is no SSIS issue with doing this (as far as I am aware) so you might want to post this in the agent forum as well.

Thanks,
Matt

owner of maintenance jobs-permission hole?

Hello,
I have a SQL 2000 user who created some maintenance jobs (they are owned by
her account). She can run them manually but says she can't schedule them. If
I change the owner of the jobs to SA, does that mean she could then modify
those jobs and get data with SA rights that she didn't have access to with
her account?
tia,
jjjj,
Why can't she schedule her jobs? If she has public rights to the msdb
database, I believe that is enough to allow her to schedule jobs that run
under her own rights.
If you allow these jobs to run at higher levels of security, then certainly
the job could technically get access to additional data.
If you do not want to grant your user rights to create SQL Agent jobs in
msdb, then you will need to set up the jobs. However, I would suggest
creating a service user account (e.g. User1MaintJobs), grant that account
the needed rights for the job, and assign that account as the job owner.
FWIW,
RLF
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLzCr3nlIHA.696@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a SQL 2000 user who created some maintenance jobs (they are owned
> by
> her account). She can run them manually but says she can't schedule them.
> If
> I change the owner of the jobs to SA, does that mean she could then modify
> those jobs and get data with SA rights that she didn't have access to with
> her account?
> tia,
> jj
>

owner of maintenance jobs-permission hole?

Hello,
I have a SQL 2000 user who created some maintenance jobs (they are owned by
her account). She can run them manually but says she can't schedule them. If
I change the owner of the jobs to SA, does that mean she could then modify
those jobs and get data with SA rights that she didn't have access to with
her account?
tia,
jj
jj,
Why can't she schedule her jobs? If she has public rights to the msdb
database, I believe that is enough to allow her to schedule jobs that run
under her own rights.
If you allow these jobs to run at higher levels of security, then certainly
the job could technically get access to additional data.
If you do not want to grant your user rights to create SQL Agent jobs in
msdb, then you will need to set up the jobs. However, I would suggest
creating a service user account (e.g. User1MaintJobs), grant that account
the needed rights for the job, and assign that account as the job owner.
FWIW,
RLF
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:eLzCr3nlIHA.696@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I have a SQL 2000 user who created some maintenance jobs (they are owned
> by
> her account). She can run them manually but says she can't schedule them.
> If
> I change the owner of the jobs to SA, does that mean she could then modify
> those jobs and get data with SA rights that she didn't have access to with
> her account?
> tia,
> jj
>

Owner for jobs(Sql2k)

I��m looking for the owner of our scheduled jobs but I can��t work out; Sysjobsteps, sysjobs, sysjobschedules

Sysjobservers, etc.. and neither of them I��ve been able to find this value. I saw owner_sid in hexadecimal notation, nothing else.

Does this search make any sense otherwise sql encrypt this data?Where this data stored is?

Thanks for any comment or though,

this should do:

select [name],suser_sname(owner_sid) [owner]
from msdb..sysjobs|||Thanks for that.