Job scheduling for Packages in SQL Server 2005

-
MS Win XP Pro 2002 SP2
MS SQL Server 2005
MS Visual Studio 2005
-

Can anyone help me (even by pointing me to a documentation) in order to schedule Packages (from file system source) in SQL Server 2005.

I've configured providers logging, but still the error file doesn't give me any explanation why the error happens:
"#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,PC1234,NT AUTHORITY\SYSTEM,D_AGR,{8A4FA774-F5F0-40DE-AB16-A93F27950E09},{8A918844-8E43-403D-A606-C8CB4B7D8238},31/08/2006 16:42:55,31/08/2006 16:42:55,0,0x,(null)"


I've also done the same on the Step properties under 'Logging'


In Management Studio I've added my login name and I'm the only user using the machine and I manage both Visual Studio and Management Studio


The error coming up on the job history is as follow: "Executed as user: PC1234\SYSTEM. The package execution failed. The step failed." "The job failed. The Job was invoked by User UK1\USER123. The last step to run was step 1 (step1)."


By the way the package (.dtsx) runs fine in BI Visual Studio)

Thank you very much.

I ran into the same problem this morning. The default when scheduling a package as a job is to run under the SQL Service Agent, in Visual Studio and executing the package from Sql Server Management Studio you are executing the package under your user account UK1\User123.

This causes problems for connections using Windows Authentication. Your fix might be unique as there is a couple ways to deal with this behavior.

Check out this MS article...

http://support.microsoft.com/?kbid=918760

|||

Thank you.
I went trough the steps, but didn't really get far.

I'm the only user on this pc, thus Server Name, Creator, Author etc. always refer to the same parameters. I ticked for my login all permissions available, but still...
The job that I'm trying to schedule with the SQL Server Agent is still failing. It runs fine in BI Visual Studio and also runs ok if executed from Management Studio connected to Integration Services (Stored Packages\MSDB\Maintenace Plans\Package1)

Another issue is the log file which doesn't explain much:
I've tried to set up a log file (provider for text files with all events ticked) but this is all i get:
"OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{FF9D650E-62D3-44F1-9D76-B34467E8DADC},01/09/2006 16:46:09,01/09/2006 16:46:09,0,0x,(null)"

Thanks for the link anyway

|||What is the value of the SSIS package ProtectionLevel property? We're using configuration files to save all of our secure settings and have set the value of the property to DontSaveSensitive. Using an Indirect XML Configuration to point to the configuration file containing the connection strings and using different configuration files for Production, Staging, and development. Aren't experiencing any issues with SQL Agent using this approach.|||

Hi Martin,

I've tried to use ProtectionLevel property DontSaveSensitive, EncryptSensitiveWithUserKey and ServerStorage. With each attempt I saved a copy of the package as: location=SQL Server, Windows Authentication, path= SSIS Packages\Maintenance Plans

I can see the package in Management Studio under Stored Packages\MSDB\Maintenance Plans.

I've scheduled jobs in each attempt, step type= SSIS Package, Run as= SQL Agent Service Account, Source=as above, on Data Sources I've ticked all Connection Manager, and I've set up the same Logging Provider as set up initially on the package in BI Visual Studio.

If I run the job I get the following error on the log file (this last one with ServerStorage property):
OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{73A25B5B-4CE5-45B2-A1A0-948F0B8AB9A9},06/09/2006 11:20:08,06/09/2006 11:20:08,0,0x,(null)

Any other idea on the issue? And also why isn't the log file more detailed on the actual error?

Thank you

|||

I forgot to mention that (in each attempt), if I right click the package from Management Studio and Run it, the pachage runs successefully.

|||

An error in the OnPreValidate event almost always indicates that a database connection or connection to a network file share could not be established. From the error message you're receiving, it looks as if your SQL Server Agent service is running under a local system account, NT AUTHORITY\SYSTEM. With the service running under this account assuming your database connections are using Windows Authentication, you won't be able to access any resources beyond that machine boundary when scheduling execution of your package. Have you checked to see if SQL Server Agent, and SQL Integration Services (other?) services are running on a network account that has access to all resources referenced by your package?

|||

Sorry it took be a couple days to come across this again.

You said you didn't get very far with the link and that you are the only user. Only the SQL Service Agent doesn't run under your user account. It will run under the setting you selected when installing SQL Server (Local System, Local Service, or Network Service).

Here is what I did, create a new Credential using SQL Server Management Studio (under the Security section). Give the Credential the indentity of your user account on the computer. Then under SQL Server Agent\Proxies\SSIS Package Execution create a new Proxy, assigning it the credential you created in the previous step. I checked SQL Server Integration Sercies Package subsystem.

Finally go back to your scheduled job and edit the Step that runs the SSIS Package. Under the Run As drop down you should now see your new proxy. Select it and try and run the job. I also made sure that the owner of the job was the same user as the proxy, I forget if that was nessisary or not.

Hope that helps.

|||I had the same error with a totally different solution and this may not be your issue, but I had to check the boxes under the data sources tab in my job step and it seemed to fix it.|||

Hi all.

Martin,
I confirm the Agent service property is set to 'Local system account'.
I thought this wouldn't be an issue given that I have everything on one pc where I am the only user.
ie:(local) and UKSERV01 would be the same thing, and I use my domain\account to log on the server/pc,
and use Windows Authentication, thus anything I create in BI Visual Studio and in Management Studio
should use the same account...i believe.

BSHOE,
I have already selected the data source connections. Thanks

infrandom,
as above, yes the Agent service runs as 'Local system account'.
I'm goinng to try create the Proxy and will post back the outcome.

Thanks so far guys, we'll get there eventually!

|||

an update...

i've tried the proxy solution which worked fine, but not for all packages.

I'm currently using a new server account that I use also to run the sql agent service.

Thanks all

|||

Hey, Luz.

I was dealing with the same kind of thing. Some jobs were working just fine, while others were not. The thing I noticed was that packages that dealt with flat files (thereby requiring file system access outside of the database) were the ones dying. I changed the account under which the SQL server agent ran to my domain login account and it all ran fine after that! Just thought I would share.

|||

Hi Drew

that is exactly my current setup.

Thanks for your feedback.

|||

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

|||

Patdev wrote:

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

It could be the package security's ProtectionLevel property in your case. When you save the package to the server, all password information is stripped.

There are plenty of posts on the ProtectionLevel property in this forum should you decide to research that parameter.

Job scheduling for Packages in SQL Server 2005

-
MS Win XP Pro 2002 SP2
MS SQL Server 2005
MS Visual Studio 2005
-

Can anyone help me (even by pointing me to a documentation) in order to schedule Packages (from file system source) in SQL Server 2005.

I've configured providers logging, but still the error file doesn't give me any explanation why the error happens:
"#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,PC1234,NT AUTHORITY\SYSTEM,D_AGR,{8A4FA774-F5F0-40DE-AB16-A93F27950E09},{8A918844-8E43-403D-A606-C8CB4B7D8238},31/08/2006 16:42:55,31/08/2006 16:42:55,0,0x,(null)"
I've also done the same on the Step properties under 'Logging'
In Management Studio I've added my login name and I'm the only user using the machine and I manage both Visual Studio and Management Studio
The error coming up on the job history is as follow: "Executed as user: PC1234\SYSTEM. The package execution failed. The step failed." "The job failed. The Job was invoked by User UK1\USER123. The last step to run was step 1 (step1)."
By the way the package (.dtsx) runs fine in BI Visual Studio)

Thank you very much.

I ran into the same problem this morning. The default when scheduling a package as a job is to run under the SQL Service Agent, in Visual Studio and executing the package from Sql Server Management Studio you are executing the package under your user account UK1\User123.

This causes problems for connections using Windows Authentication. Your fix might be unique as there is a couple ways to deal with this behavior.

Check out this MS article...

http://support.microsoft.com/?kbid=918760

|||

Thank you.
I went trough the steps, but didn't really get far.

I'm the only user on this pc, thus Server Name, Creator, Author etc. always refer to the same parameters. I ticked for my login all permissions available, but still...
The job that I'm trying to schedule with the SQL Server Agent is still failing. It runs fine in BI Visual Studio and also runs ok if executed from Management Studio connected to Integration Services (Stored Packages\MSDB\Maintenace Plans\Package1)

Another issue is the log file which doesn't explain much:
I've tried to set up a log file (provider for text files with all events ticked) but this is all i get:
"OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{FF9D650E-62D3-44F1-9D76-B34467E8DADC},01/09/2006 16:46:09,01/09/2006 16:46:09,0,0x,(null)"

Thanks for the link anyway

|||What is the value of the SSIS package ProtectionLevel property? We're using configuration files to save all of our secure settings and have set the value of the property to DontSaveSensitive. Using an Indirect XML Configuration to point to the configuration file containing the connection strings and using different configuration files for Production, Staging, and development. Aren't experiencing any issues with SQL Agent using this approach.|||

Hi Martin,

I've tried to use ProtectionLevel property DontSaveSensitive, EncryptSensitiveWithUserKey and ServerStorage. With each attempt I saved a copy of the package as: location=SQL Server, Windows Authentication, path= SSIS Packages\Maintenance Plans

I can see the package in Management Studio under Stored Packages\MSDB\Maintenance Plans.

I've scheduled jobs in each attempt, step type= SSIS Package, Run as= SQL Agent Service Account, Source=as above, on Data Sources I've ticked all Connection Manager, and I've set up the same Logging Provider as set up initially on the package in BI Visual Studio.

If I run the job I get the following error on the log file (this last one with ServerStorage property):
OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{73A25B5B-4CE5-45B2-A1A0-948F0B8AB9A9},06/09/2006 11:20:08,06/09/2006 11:20:08,0,0x,(null)

Any other idea on the issue? And also why isn't the log file more detailed on the actual error?

Thank you

|||

I forgot to mention that (in each attempt), if I right click the package from Management Studio and Run it, the pachage runs successefully.

|||

An error in the OnPreValidate event almost always indicates that a database connection or connection to a network file share could not be established. From the error message you're receiving, it looks as if your SQL Server Agent service is running under a local system account, NT AUTHORITY\SYSTEM. With the service running under this account assuming your database connections are using Windows Authentication, you won't be able to access any resources beyond that machine boundary when scheduling execution of your package. Have you checked to see if SQL Server Agent, and SQL Integration Services (other?) services are running on a network account that has access to all resources referenced by your package?

|||

Sorry it took be a couple days to come across this again.

You said you didn't get very far with the link and that you are the only user. Only the SQL Service Agent doesn't run under your user account. It will run under the setting you selected when installing SQL Server (Local System, Local Service, or Network Service).

Here is what I did, create a new Credential using SQL Server Management Studio (under the Security section). Give the Credential the indentity of your user account on the computer. Then under SQL Server Agent\Proxies\SSIS Package Execution create a new Proxy, assigning it the credential you created in the previous step. I checked SQL Server Integration Sercies Package subsystem.

Finally go back to your scheduled job and edit the Step that runs the SSIS Package. Under the Run As drop down you should now see your new proxy. Select it and try and run the job. I also made sure that the owner of the job was the same user as the proxy, I forget if that was nessisary or not.

Hope that helps.

|||I had the same error with a totally different solution and this may not be your issue, but I had to check the boxes under the data sources tab in my job step and it seemed to fix it.|||

Hi all.

Martin,
I confirm the Agent service property is set to 'Local system account'.
I thought this wouldn't be an issue given that I have everything on one pc where I am the only user.
ie:(local) and UKSERV01 would be the same thing, and I use my domain\account to log on the server/pc,
and use Windows Authentication, thus anything I create in BI Visual Studio and in Management Studio
should use the same account...i believe.

BSHOE,
I have already selected the data source connections. Thanks

infrandom,
as above, yes the Agent service runs as 'Local system account'.
I'm goinng to try create the Proxy and will post back the outcome.

Thanks so far guys, we'll get there eventually!

|||

an update...

i've tried the proxy solution which worked fine, but not for all packages.

I'm currently using a new server account that I use also to run the sql agent service.

Thanks all

|||

Hey, Luz.

I was dealing with the same kind of thing. Some jobs were working just fine, while others were not. The thing I noticed was that packages that dealt with flat files (thereby requiring file system access outside of the database) were the ones dying. I changed the account under which the SQL server agent ran to my domain login account and it all ran fine after that! Just thought I would share.

|||

Hi Drew

that is exactly my current setup.

Thanks for your feedback.

|||

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

|||

Patdev wrote:

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

It could be the package security's ProtectionLevel property in your case. When you save the package to the server, all password information is stripped.

There are plenty of posts on the ProtectionLevel property in this forum should you decide to research that parameter.sql

Job scheduling for Packages in SQL Server 2005

-
MS Win XP Pro 2002 SP2
MS SQL Server 2005
MS Visual Studio 2005
-

Can anyone help me (even by pointing me to a documentation) in order to schedule Packages (from file system source) in SQL Server 2005.

I've configured providers logging, but still the error file doesn't give me any explanation why the error happens:
"#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,PC1234,NT AUTHORITY\SYSTEM,D_AGR,{8A4FA774-F5F0-40DE-AB16-A93F27950E09},{8A918844-8E43-403D-A606-C8CB4B7D8238},31/08/2006 16:42:55,31/08/2006 16:42:55,0,0x,(null)"


I've also done the same on the Step properties under 'Logging'


In Management Studio I've added my login name and I'm the only user using the machine and I manage both Visual Studio and Management Studio


The error coming up on the job history is as follow: "Executed as user: PC1234\SYSTEM. The package execution failed. The step failed." "The job failed. The Job was invoked by User UK1\USER123. The last step to run was step 1 (step1)."


By the way the package (.dtsx) runs fine in BI Visual Studio)

Thank you very much.

I ran into the same problem this morning. The default when scheduling a package as a job is to run under the SQL Service Agent, in Visual Studio and executing the package from Sql Server Management Studio you are executing the package under your user account UK1\User123.

This causes problems for connections using Windows Authentication. Your fix might be unique as there is a couple ways to deal with this behavior.

Check out this MS article...

http://support.microsoft.com/?kbid=918760

|||

Thank you.
I went trough the steps, but didn't really get far.

I'm the only user on this pc, thus Server Name, Creator, Author etc. always refer to the same parameters. I ticked for my login all permissions available, but still...
The job that I'm trying to schedule with the SQL Server Agent is still failing. It runs fine in BI Visual Studio and also runs ok if executed from Management Studio connected to Integration Services (Stored Packages\MSDB\Maintenace Plans\Package1)

Another issue is the log file which doesn't explain much:
I've tried to set up a log file (provider for text files with all events ticked) but this is all i get:
"OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{FF9D650E-62D3-44F1-9D76-B34467E8DADC},01/09/2006 16:46:09,01/09/2006 16:46:09,0,0x,(null)"

Thanks for the link anyway

|||What is the value of the SSIS package ProtectionLevel property? We're using configuration files to save all of our secure settings and have set the value of the property to DontSaveSensitive. Using an Indirect XML Configuration to point to the configuration file containing the connection strings and using different configuration files for Production, Staging, and development. Aren't experiencing any issues with SQL Agent using this approach.|||

Hi Martin,

I've tried to use ProtectionLevel property DontSaveSensitive, EncryptSensitiveWithUserKey and ServerStorage. With each attempt I saved a copy of the package as: location=SQL Server, Windows Authentication, path= SSIS Packages\Maintenance Plans

I can see the package in Management Studio under Stored Packages\MSDB\Maintenance Plans.

I've scheduled jobs in each attempt, step type= SSIS Package, Run as= SQL Agent Service Account, Source=as above, on Data Sources I've ticked all Connection Manager, and I've set up the same Logging Provider as set up initially on the package in BI Visual Studio.

If I run the job I get the following error on the log file (this last one with ServerStorage property):
OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{73A25B5B-4CE5-45B2-A1A0-948F0B8AB9A9},06/09/2006 11:20:08,06/09/2006 11:20:08,0,0x,(null)

Any other idea on the issue? And also why isn't the log file more detailed on the actual error?

Thank you

|||

I forgot to mention that (in each attempt), if I right click the package from Management Studio and Run it, the pachage runs successefully.

|||

An error in the OnPreValidate event almost always indicates that a database connection or connection to a network file share could not be established. From the error message you're receiving, it looks as if your SQL Server Agent service is running under a local system account, NT AUTHORITY\SYSTEM. With the service running under this account assuming your database connections are using Windows Authentication, you won't be able to access any resources beyond that machine boundary when scheduling execution of your package. Have you checked to see if SQL Server Agent, and SQL Integration Services (other?) services are running on a network account that has access to all resources referenced by your package?

|||

Sorry it took be a couple days to come across this again.

You said you didn't get very far with the link and that you are the only user. Only the SQL Service Agent doesn't run under your user account. It will run under the setting you selected when installing SQL Server (Local System, Local Service, or Network Service).

Here is what I did, create a new Credential using SQL Server Management Studio (under the Security section). Give the Credential the indentity of your user account on the computer. Then under SQL Server Agent\Proxies\SSIS Package Execution create a new Proxy, assigning it the credential you created in the previous step. I checked SQL Server Integration Sercies Package subsystem.

Finally go back to your scheduled job and edit the Step that runs the SSIS Package. Under the Run As drop down you should now see your new proxy. Select it and try and run the job. I also made sure that the owner of the job was the same user as the proxy, I forget if that was nessisary or not.

Hope that helps.

|||I had the same error with a totally different solution and this may not be your issue, but I had to check the boxes under the data sources tab in my job step and it seemed to fix it.|||

Hi all.

Martin,
I confirm the Agent service property is set to 'Local system account'.
I thought this wouldn't be an issue given that I have everything on one pc where I am the only user.
ie:(local) and UKSERV01 would be the same thing, and I use my domain\account to log on the server/pc,
and use Windows Authentication, thus anything I create in BI Visual Studio and in Management Studio
should use the same account...i believe.

BSHOE,
I have already selected the data source connections. Thanks

infrandom,
as above, yes the Agent service runs as 'Local system account'.
I'm goinng to try create the Proxy and will post back the outcome.

Thanks so far guys, we'll get there eventually!

|||

an update...

i've tried the proxy solution which worked fine, but not for all packages.

I'm currently using a new server account that I use also to run the sql agent service.

Thanks all

|||

Hey, Luz.

I was dealing with the same kind of thing. Some jobs were working just fine, while others were not. The thing I noticed was that packages that dealt with flat files (thereby requiring file system access outside of the database) were the ones dying. I changed the account under which the SQL server agent ran to my domain login account and it all ran fine after that! Just thought I would share.

|||

Hi Drew

that is exactly my current setup.

Thanks for your feedback.

|||

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

|||

Patdev wrote:

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

It could be the package security's ProtectionLevel property in your case. When you save the package to the server, all password information is stripped.

There are plenty of posts on the ProtectionLevel property in this forum should you decide to research that parameter.

Job scheduling for Packages in SQL Server 2005

-
MS Win XP Pro 2002 SP2
MS SQL Server 2005
MS Visual Studio 2005
-

Can anyone help me (even by pointing me to a documentation) in order to schedule Packages (from file system source) in SQL Server 2005.

I've configured providers logging, but still the error file doesn't give me any explanation why the error happens:
"#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,PC1234,NT AUTHORITY\SYSTEM,D_AGR,{8A4FA774-F5F0-40DE-AB16-A93F27950E09},{8A918844-8E43-403D-A606-C8CB4B7D8238},31/08/2006 16:42:55,31/08/2006 16:42:55,0,0x,(null)"


I've also done the same on the Step properties under 'Logging'


In Management Studio I've added my login name and I'm the only user using the machine and I manage both Visual Studio and Management Studio


The error coming up on the job history is as follow: "Executed as user: PC1234\SYSTEM. The package execution failed. The step failed." "The job failed. The Job was invoked by User UK1\USER123. The last step to run was step 1 (step1)."


By the way the package (.dtsx) runs fine in BI Visual Studio)

Thank you very much.

I ran into the same problem this morning. The default when scheduling a package as a job is to run under the SQL Service Agent, in Visual Studio and executing the package from Sql Server Management Studio you are executing the package under your user account UK1\User123.

This causes problems for connections using Windows Authentication. Your fix might be unique as there is a couple ways to deal with this behavior.

Check out this MS article...

http://support.microsoft.com/?kbid=918760

|||

Thank you.
I went trough the steps, but didn't really get far.

I'm the only user on this pc, thus Server Name, Creator, Author etc. always refer to the same parameters. I ticked for my login all permissions available, but still...
The job that I'm trying to schedule with the SQL Server Agent is still failing. It runs fine in BI Visual Studio and also runs ok if executed from Management Studio connected to Integration Services (Stored Packages\MSDB\Maintenace Plans\Package1)

Another issue is the log file which doesn't explain much:
I've tried to set up a log file (provider for text files with all events ticked) but this is all i get:
"OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{FF9D650E-62D3-44F1-9D76-B34467E8DADC},01/09/2006 16:46:09,01/09/2006 16:46:09,0,0x,(null)"

Thanks for the link anyway

|||What is the value of the SSIS package ProtectionLevel property? We're using configuration files to save all of our secure settings and have set the value of the property to DontSaveSensitive. Using an Indirect XML Configuration to point to the configuration file containing the connection strings and using different configuration files for Production, Staging, and development. Aren't experiencing any issues with SQL Agent using this approach.|||

Hi Martin,

I've tried to use ProtectionLevel property DontSaveSensitive, EncryptSensitiveWithUserKey and ServerStorage. With each attempt I saved a copy of the package as: location=SQL Server, Windows Authentication, path= SSIS Packages\Maintenance Plans

I can see the package in Management Studio under Stored Packages\MSDB\Maintenance Plans.

I've scheduled jobs in each attempt, step type= SSIS Package, Run as= SQL Agent Service Account, Source=as above, on Data Sources I've ticked all Connection Manager, and I've set up the same Logging Provider as set up initially on the package in BI Visual Studio.

If I run the job I get the following error on the log file (this last one with ServerStorage property):
OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{73A25B5B-4CE5-45B2-A1A0-948F0B8AB9A9},06/09/2006 11:20:08,06/09/2006 11:20:08,0,0x,(null)

Any other idea on the issue? And also why isn't the log file more detailed on the actual error?

Thank you

|||

I forgot to mention that (in each attempt), if I right click the package from Management Studio and Run it, the pachage runs successefully.

|||

An error in the OnPreValidate event almost always indicates that a database connection or connection to a network file share could not be established. From the error message you're receiving, it looks as if your SQL Server Agent service is running under a local system account, NT AUTHORITY\SYSTEM. With the service running under this account assuming your database connections are using Windows Authentication, you won't be able to access any resources beyond that machine boundary when scheduling execution of your package. Have you checked to see if SQL Server Agent, and SQL Integration Services (other?) services are running on a network account that has access to all resources referenced by your package?

|||

Sorry it took be a couple days to come across this again.

You said you didn't get very far with the link and that you are the only user. Only the SQL Service Agent doesn't run under your user account. It will run under the setting you selected when installing SQL Server (Local System, Local Service, or Network Service).

Here is what I did, create a new Credential using SQL Server Management Studio (under the Security section). Give the Credential the indentity of your user account on the computer. Then under SQL Server Agent\Proxies\SSIS Package Execution create a new Proxy, assigning it the credential you created in the previous step. I checked SQL Server Integration Sercies Package subsystem.

Finally go back to your scheduled job and edit the Step that runs the SSIS Package. Under the Run As drop down you should now see your new proxy. Select it and try and run the job. I also made sure that the owner of the job was the same user as the proxy, I forget if that was nessisary or not.

Hope that helps.

|||I had the same error with a totally different solution and this may not be your issue, but I had to check the boxes under the data sources tab in my job step and it seemed to fix it.|||

Hi all.

Martin,
I confirm the Agent service property is set to 'Local system account'.
I thought this wouldn't be an issue given that I have everything on one pc where I am the only user.
ie:(local) and UKSERV01 would be the same thing, and I use my domain\account to log on the server/pc,
and use Windows Authentication, thus anything I create in BI Visual Studio and in Management Studio
should use the same account...i believe.

BSHOE,
I have already selected the data source connections. Thanks

infrandom,
as above, yes the Agent service runs as 'Local system account'.
I'm goinng to try create the Proxy and will post back the outcome.

Thanks so far guys, we'll get there eventually!

|||

an update...

i've tried the proxy solution which worked fine, but not for all packages.

I'm currently using a new server account that I use also to run the sql agent service.

Thanks all

|||

Hey, Luz.

I was dealing with the same kind of thing. Some jobs were working just fine, while others were not. The thing I noticed was that packages that dealt with flat files (thereby requiring file system access outside of the database) were the ones dying. I changed the account under which the SQL server agent ran to my domain login account and it all ran fine after that! Just thought I would share.

|||

Hi Drew

that is exactly my current setup.

Thanks for your feedback.

|||

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

|||

Patdev wrote:

Hi

I am having same problem too. I have package that is simple export database from one database to other and saved as SSIS package on SQL Server. but if i run that package with job along with proxy account it fails and gives me error.

and that same package i fun on my computer and if i run that by itself by right clicking on package in integration service, and then run package it runs fine!!

Why and what will be the problem?

thanks

Pat

It could be the package security's ProtectionLevel property in your case. When you save the package to the server, all password information is stripped.

There are plenty of posts on the ProtectionLevel property in this forum should you decide to research that parameter.

Job Scheduler fails and Managing Tempdb

Hello,
I have two issues, hoping someone can help.
Issue 1. I have various DTS packages that copy data from Progress
Database to Sql Data Warehouse. The ODBC Connection is stable and
packages have been auto scheduled by creating a job that is managed by
the SQL Agent service to run daily at night. The packages work fine
only in two cases, when running manually by using the DTSRun.exe
command line utility and when the job is manually run in the SQL Agent
service. Auto run is alwayz showing a status failer, I'm now using
windows scheduler and my packs are now always a success.
My question is why is SQL Agent not reliable? I'm 100% sure that there
is absolutely nothing wrong with the packets as they run error free
when manually ran.
Issue 2. My data warehouse is 28 G in size this makes the tempdb to
grow bigger everyday. To downsize it I stop and start the sql service
manually, can you please assist me with a batch command to auto start
and stop the service?
Thanking you in advance
Babalwa
1) This most likely is a permissions issue. Check that the account that SQL
Agent is running under has the appropriate permissions to execute everything
in your DTS package. It can be running under the LocalSystem account, which
doesn't have any permissions outside the local computer.
2) It's not much use shrinking tempdb, as it will grow again when you use
the server. And when tempdb auto grows that will only slow down your server.
You can check however if there are any transaction that stay open in tempdb
for a long time, cause extra space to be used, and prevent reuse of the
transaction log. Use DBCC OPENTRAN ('tempdb') to see if you have any old
transactions.
Otherwise the size of tempdb is something you have to live with, with your
current setup. Check if you can either change your applications and
databases, so tempdb is needed less, or you might have to get more harddisk
space.
Jacco Schalkwijk
SQL Server MVP
"Babalwa Magwentshu" <babalwa@.hotmail.com> wrote in message
news:5999368f.0407280128.75bd9322@.posting.google.c om...
> Hello,
> I have two issues, hoping someone can help.
> Issue 1. I have various DTS packages that copy data from Progress
> Database to Sql Data Warehouse. The ODBC Connection is stable and
> packages have been auto scheduled by creating a job that is managed by
> the SQL Agent service to run daily at night. The packages work fine
> only in two cases, when running manually by using the DTSRun.exe
> command line utility and when the job is manually run in the SQL Agent
> service. Auto run is alwayz showing a status failer, I'm now using
> windows scheduler and my packs are now always a success.
> My question is why is SQL Agent not reliable? I'm 100% sure that there
> is absolutely nothing wrong with the packets as they run error free
> when manually ran.
> Issue 2. My data warehouse is 28 G in size this makes the tempdb to
> grow bigger everyday. To downsize it I stop and start the sql service
> manually, can you please assist me with a batch command to auto start
> and stop the service?
> Thanking you in advance
> Babalwa
|||Thank you for your response Jacco,
Let me briefly expand on the issue, I am the domain administrator and
have designed the data warehouse packets and it's schedules under my
administrative password. I have checked the permissions, security
won't be the issue. Correct me if I'm wrong, this is how I understand
the security concept - for packages created under a Microsoft Windows
NT 4.0 or Microsoft Windows 2000 account, the job runs under the
security context of the account that started SQL Server Agent.
Thank you again,
babalwa@.hotmail.com (Babalwa Magwentshu) wrote in message news:<5999368f.0407280128.75bd9322@.posting.google. com>...
> Hello,
> I have two issues, hoping someone can help.
> Issue 1. I have various DTS packages that copy data from Progress
> Database to Sql Data Warehouse. The ODBC Connection is stable and
> packages have been auto scheduled by creating a job that is managed by
> the SQL Agent service to run daily at night. The packages work fine
> only in two cases, when running manually by using the DTSRun.exe
> command line utility and when the job is manually run in the SQL Agent
> service. Auto run is alwayz showing a status failer, I'm now using
> windows scheduler and my packs are now always a success.
> My question is why is SQL Agent not reliable? I'm 100% sure that there
> is absolutely nothing wrong with the packets as they run error free
> when manually ran.
> Issue 2. My data warehouse is 28 G in size this makes the tempdb to
> grow bigger everyday. To downsize it I stop and start the sql service
> manually, can you please assist me with a batch command to auto start
> and stop the service?
> Thanking you in advance
> Babalwa

Job Scheduler fails and Managing Tempdb

Hello,
I have two issues, hoping someone can help.
Issue 1. I have various DTS packages that copy data from Progress
Database to Sql Data Warehouse. The ODBC Connection is stable and
packages have been auto scheduled by creating a job that is managed by
the SQL Agent service to run daily at night. The packages work fine
only in two cases, when running manually by using the DTSRun.exe
command line utility and when the job is manually run in the SQL Agent
service. Auto run is alwayz showing a status failer, I'm now using
windows scheduler and my packs are now always a success.
My question is why is SQL Agent not reliable? I'm 100% sure that there
is absolutely nothing wrong with the packets as they run error free
when manually ran.
Issue 2. My data warehouse is 28 G in size this makes the tempdb to
grow bigger everyday. To downsize it I stop and start the sql service
manually, can you please assist me with a batch command to auto start
and stop the service?
Thanking you in advance
Babalwa1) This most likely is a permissions issue. Check that the account that SQL
Agent is running under has the appropriate permissions to execute everything
in your DTS package. It can be running under the LocalSystem account, which
doesn't have any permissions outside the local computer.
2) It's not much use shrinking tempdb, as it will grow again when you use
the server. And when tempdb auto grows that will only slow down your server.
You can check however if there are any transaction that stay open in tempdb
for a long time, cause extra space to be used, and prevent reuse of the
transaction log. Use DBCC OPENTRAN ('tempdb') to see if you have any old
transactions.
Otherwise the size of tempdb is something you have to live with, with your
current setup. Check if you can either change your applications and
databases, so tempdb is needed less, or you might have to get more harddisk
space.
Jacco Schalkwijk
SQL Server MVP
"Babalwa Magwentshu" <babalwa@.hotmail.com> wrote in message
news:5999368f.0407280128.75bd9322@.posting.google.com...
> Hello,
> I have two issues, hoping someone can help.
> Issue 1. I have various DTS packages that copy data from Progress
> Database to Sql Data Warehouse. The ODBC Connection is stable and
> packages have been auto scheduled by creating a job that is managed by
> the SQL Agent service to run daily at night. The packages work fine
> only in two cases, when running manually by using the DTSRun.exe
> command line utility and when the job is manually run in the SQL Agent
> service. Auto run is alwayz showing a status failer, I'm now using
> windows scheduler and my packs are now always a success.
> My question is why is SQL Agent not reliable? I'm 100% sure that there
> is absolutely nothing wrong with the packets as they run error free
> when manually ran.
> Issue 2. My data warehouse is 28 G in size this makes the tempdb to
> grow bigger everyday. To downsize it I stop and start the sql service
> manually, can you please assist me with a batch command to auto start
> and stop the service?
> Thanking you in advance
> Babalwa|||Thank you for your response Jacco,
Let me briefly expand on the issue, I am the domain administrator and
have designed the data warehouse packets and it's schedules under my
administrative password. I have checked the permissions, security
won't be the issue. Correct me if I'm wrong, this is how I understand
the security concept - for packages created under a Microsoft Windows
NT 4.0 or Microsoft Windows 2000 account, the job runs under the
security context of the account that started SQL Server Agent.
Thank you again,
babalwa@.hotmail.com (Babalwa Magwentshu) wrote in message news:<5999368f.0407280128.75bd9322
@.posting.google.com>...
> Hello,
> I have two issues, hoping someone can help.
> Issue 1. I have various DTS packages that copy data from Progress
> Database to Sql Data Warehouse. The ODBC Connection is stable and
> packages have been auto scheduled by creating a job that is managed by
> the SQL Agent service to run daily at night. The packages work fine
> only in two cases, when running manually by using the DTSRun.exe
> command line utility and when the job is manually run in the SQL Agent
> service. Auto run is alwayz showing a status failer, I'm now using
> windows scheduler and my packs are now always a success.
> My question is why is SQL Agent not reliable? I'm 100% sure that there
> is absolutely nothing wrong with the packets as they run error free
> when manually ran.
> Issue 2. My data warehouse is 28 G in size this makes the tempdb to
> grow bigger everyday. To downsize it I stop and start the sql service
> manually, can you please assist me with a batch command to auto start
> and stop the service?
> Thanking you in advance
> Babalwasql

job running SSIS package keeps failing but the SSIS package by itself runs perfectly fine

Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.

Thank you

Tej

Need to add some mopre to the description of my problem. These packages were originally on a SQL 2000 server. I migrated them to SSIS. When I run a package it says

'SSIS package <package name> starting.
Error: 0x0 at <package name>: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() Task failed: <package name> Warning: 0x80019002
at <package name>: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package <package name> finished: Failure.'

This is in Article 904796. But the thing is these packages were never MetaDataServices.. I had them on SQL Server and now on a file system. I imported them in a SSIS project and have it on a share. but it still gives me the above error.

Thank you

Tej

|||

I have the same problem. Except that I have had these packages working prior to this failure. I tried re-installing the SQL Server 200 DTS Designer components as suggested in the article, but to no avail. Same result. I will be very interested if you get any helpful responses.

|||

Hey Peter. First of all, when I put these in MSDB, they worked fine. Also, I didn't try this but try to look for the security part for every package in its properties. It's encrypted with User Key or something. Remove the encryption and it should be fine.

Let me know how it goes. Thanks

|||

so, sounds like the common issue of getting agent and package permissions in sync.

for others, the KB
An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

http://support.microsoft.com/?kbid=918760

|||

I have the same problem.

My solution is to click on the "Load DTS Package Internally" and it works perfectly. Hope this would help.

|||Hey. I think I've figured out what the problem is. SSIS by default stores the packes encrypted by user key. It's in th eproperties of the package. If you use, SQL Server encryption which I used, it removes that encryption. Also, if you want to use th efile system for this, you might want to go to the properties of the package and say no encryption instead of encrypting by user key. This should work just fine... Thank you

job running SSIS package keeps failing but the SSIS package by itself runs perfectly fine

Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.

Thank you

Tej

Need to add some mopre to the description of my problem. These packages were originally on a SQL 2000 server. I migrated them to SSIS. When I run a package it says

'SSIS package <package name> starting.
Error: 0x0 at <package name>: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() Task failed: <package name> Warning: 0x80019002
at <package name>: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package <package name> finished: Failure.'

This is in Article 904796. But the thing is these packages were never MetaDataServices.. I had them on SQL Server and now on a file system. I imported them in a SSIS project and have it on a share. but it still gives me the above error.

Thank you

Tej

|||

I have the same problem. Except that I have had these packages working prior to this failure. I tried re-installing the SQL Server 200 DTS Designer components as suggested in the article, but to no avail. Same result. I will be very interested if you get any helpful responses.

|||

Hey Peter. First of all, when I put these in MSDB, they worked fine. Also, I didn't try this but try to look for the security part for every package in its properties. It's encrypted with User Key or something. Remove the encryption and it should be fine.

Let me know how it goes. Thanks

|||

so, sounds like the common issue of getting agent and package permissions in sync.

for others, the KB
An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

http://support.microsoft.com/?kbid=918760

|||

I have the same problem.

My solution is to click on the "Load DTS Package Internally" and it works perfectly. Hope this would help.

|||Hey. I think I've figured out what the problem is. SSIS by default stores the packes encrypted by user key. It's in th eproperties of the package. If you use, SQL Server encryption which I used, it removes that encryption. Also, if you want to use th efile system for this, you might want to go to the properties of the package and say no encryption instead of encrypting by user key. This should work just fine... Thank yousql

job running SSIS package keeps failing but the SSIS package by itself runs perfectly fine

Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.

Thank you

Tej

Need to add some mopre to the description of my problem. These packages were originally on a SQL 2000 server. I migrated them to SSIS. When I run a package it says

'SSIS package <package name> starting.
Error: 0x0 at <package name>: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.
at DTS.PackageClass.Execute()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() Task failed: <package name> Warning: 0x80019002
at <package name>: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package <package name> finished: Failure.'

This is in Article 904796. But the thing is these packages were never MetaDataServices.. I had them on SQL Server and now on a file system. I imported them in a SSIS project and have it on a share. but it still gives me the above error.

Thank you

Tej

|||

I have the same problem. Except that I have had these packages working prior to this failure. I tried re-installing the SQL Server 200 DTS Designer components as suggested in the article, but to no avail. Same result. I will be very interested if you get any helpful responses.

|||

Hey Peter. First of all, when I put these in MSDB, they worked fine. Also, I didn't try this but try to look for the security part for every package in its properties. It's encrypted with User Key or something. Remove the encryption and it should be fine.

Let me know how it goes. Thanks

|||

so, sounds like the common issue of getting agent and package permissions in sync.

for others, the KB
An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

http://support.microsoft.com/?kbid=918760

|||

I have the same problem.

My solution is to click on the "Load DTS Package Internally" and it works perfectly. Hope this would help.

|||Hey. I think I've figured out what the problem is. SSIS by default stores the packes encrypted by user key. It's in th eproperties of the package. If you use, SQL Server encryption which I used, it removes that encryption. Also, if you want to use th efile system for this, you might want to go to the properties of the package and say no encryption instead of encrypting by user key. This should work just fine... Thank you

Job ownership problem

Dear all,
I have scheduled some jobs in SQL Server Agent to run some DTS packages. The
original owner of these jobs is "sa". However, when I changed to the owner to
be another SQL login id, e.g. myUser, the scheduled jobs failed. "myUser" has
the "db_owner" role. The owner of DTS packages is "Domain\Administrator" and
I logon the Windows server as local Administrator. I am not sure if there is
any problem on this. Can anyone advise? Thanks.
Ivan
If the job is owned by sa or a login that is a member of the
sysadmins server role, then the job executes under the
security context of the SQL Server Agent service account. If
the job is owned by a login that is not a member of the
sysadmins server role, then the job is executed under the
security context of the proxy account.
And then...the default setting for SQL Agent is that
non-sysadmins cannot execute CmdExec or ActiveX scripting
jobs. Scheduling a package is done by executing a CmdExec
step in the job.
That the basics of it and why you are having problems with
your job. You can find more information in the following
article:
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
-Sue
On Mon, 31 Oct 2005 20:34:02 -0800, "Ivan"
<Ivan@.discussions.microsoft.com> wrote:

>Dear all,
>I have scheduled some jobs in SQL Server Agent to run some DTS packages. The
>original owner of these jobs is "sa". However, when I changed to the owner to
>be another SQL login id, e.g. myUser, the scheduled jobs failed. "myUser" has
>the "db_owner" role. The owner of DTS packages is "Domain\Administrator" and
>I logon the Windows server as local Administrator. I am not sure if there is
>any problem on this. Can anyone advise? Thanks.
>Ivan
sql

Job ownership problem

Dear all,
I have scheduled some jobs in SQL Server Agent to run some DTS packages. The
original owner of these jobs is "sa". However, when I changed to the owner t
o
be another SQL login id, e.g. myUser, the scheduled jobs failed. "myUser" ha
s
the "db_owner" role. The owner of DTS packages is "Domain\Administrator" and
I logon the Windows server as local Administrator. I am not sure if there is
any problem on this. Can anyone advise? Thanks.
IvanIf the job is owned by sa or a login that is a member of the
sysadmins server role, then the job executes under the
security context of the SQL Server Agent service account. If
the job is owned by a login that is not a member of the
sysadmins server role, then the job is executed under the
security context of the proxy account.
And then...the default setting for SQL Agent is that
non-sysadmins cannot execute CmdExec or ActiveX scripting
jobs. Scheduling a package is done by executing a CmdExec
step in the job.
That the basics of it and why you are having problems with
your job. You can find more information in the following
article:
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
-Sue
On Mon, 31 Oct 2005 20:34:02 -0800, "Ivan"
<Ivan@.discussions.microsoft.com> wrote:

>Dear all,
>I have scheduled some jobs in SQL Server Agent to run some DTS packages. Th
e
>original owner of these jobs is "sa". However, when I changed to the owner
to
>be another SQL login id, e.g. myUser, the scheduled jobs failed. "myUser" h
as
>the "db_owner" role. The owner of DTS packages is "Domain\Administrator" an
d
>I logon the Windows server as local Administrator. I am not sure if there i
s
>any problem on this. Can anyone advise? Thanks.
>Ivan

Job ownership problem

Dear all,
I have scheduled some jobs in SQL Server Agent to run some DTS packages. The
original owner of these jobs is "sa". However, when I changed to the owner to
be another SQL login id, e.g. myUser, the scheduled jobs failed. "myUser" has
the "db_owner" role. The owner of DTS packages is "Domain\Administrator" and
I logon the Windows server as local Administrator. I am not sure if there is
any problem on this. Can anyone advise? Thanks.
IvanIf the job is owned by sa or a login that is a member of the
sysadmins server role, then the job executes under the
security context of the SQL Server Agent service account. If
the job is owned by a login that is not a member of the
sysadmins server role, then the job is executed under the
security context of the proxy account.
And then...the default setting for SQL Agent is that
non-sysadmins cannot execute CmdExec or ActiveX scripting
jobs. Scheduling a package is done by executing a CmdExec
step in the job.
That the basics of it and why you are having problems with
your job. You can find more information in the following
article:
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
-Sue
On Mon, 31 Oct 2005 20:34:02 -0800, "Ivan"
<Ivan@.discussions.microsoft.com> wrote:
>Dear all,
>I have scheduled some jobs in SQL Server Agent to run some DTS packages. The
>original owner of these jobs is "sa". However, when I changed to the owner to
>be another SQL login id, e.g. myUser, the scheduled jobs failed. "myUser" has
>the "db_owner" role. The owner of DTS packages is "Domain\Administrator" and
>I logon the Windows server as local Administrator. I am not sure if there is
>any problem on this. Can anyone advise? Thanks.
>Ivan

Job from Local DTS Package not running

Hi,

I created a DTS to run a VB.net exe from local packages dts.
If I execute the package, it would do what I want.
I scheduled it to be a job running every minute of every day to test out the schedule.

The job status only stays at "Excuting Job Step 1"...and this hangs there forever.

I've check my SQLServerAgent is started.

Some info:
my sqlserveragent service is run under a username specifically for SQLServer I call "SQLService".
I'm using SQL Server Developer Edition.

Anyone can explain why this is happenning? I'm running into dead alleys here.Why do you want to do that using SQL . Can It not be done through Windows task Scheduler ?|||Cause it needs to be refresh almost a few minutes...or hour..depending..

Windows Task shceduler...I'm using w2k...only details the time of the day, and repeats only on "daily" basis.

So...anything I should check?|||Try going to the advanced schedule properties in windows task scheduler ... you can schedule for every hour nad minute|||Thanks, I can see the detail settings from my home pc...(XP)...
Will try that once I'm at work.

Well...if it works...anything goes...but for knowledge sake, what could have possibly cause the job to just hang? Any ideas?|||I dont think you can call an external exe from a job or xp_cmdshell ... havent found out a way to do so yet.

Job failure notification

Hello,
I created a job that will execute two dts packages at night. Both back up
different databases from the remote server to the local server, so they are
not dependent on each other. Also, if the first one fails, the job steps to
the second one. However, I would like to be notified if either/both of them
fail (and know which one failed).
Can I do this or should I have two separate jobs. If possible, I would
rather get an email saying DTS Package 1 failed versus having to look at the
each log file to determine which one failed.
--
Thanks in advance,
StevenWhy are you using DTS to do backups ?
You can have a job fire that will backup databases. You would have a job
per server you wanted to backup. You can specify the location of the backup
file using UNC. When you say backup do you actually mean data transfer ?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Steven K" <sckeels@.lucent.com> wrote in message
news:uspA2dlRDHA.2116@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I created a job that will execute two dts packages at night. Both back up
> different databases from the remote server to the local server, so they
are
> not dependent on each other. Also, if the first one fails, the job steps
to
> the second one. However, I would like to be notified if either/both of
them
> fail (and know which one failed).
> Can I do this or should I have two separate jobs. If possible, I would
> rather get an email saying DTS Package 1 failed versus having to look at
the
> each log file to determine which one failed.
> --
> Thanks in advance,
> Steven
>|||Sorry Allan,
What I meant to write was data transfer.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:OtAVkklRDHA.3132@.tk2msftngp13.phx.gbl...
> Why are you using DTS to do backups ?
> You can have a job fire that will backup databases. You would have a job
> per server you wanted to backup. You can specify the location of the
backup
> file using UNC. When you say backup do you actually mean data transfer ?
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Steven K" <sckeels@.lucent.com> wrote in message
> news:uspA2dlRDHA.2116@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I created a job that will execute two dts packages at night. Both back
up
> > different databases from the remote server to the local server, so they
> are
> > not dependent on each other. Also, if the first one fails, the job
steps
> to
> > the second one. However, I would like to be notified if either/both of
> them
> > fail (and know which one failed).
> >
> > Can I do this or should I have two separate jobs. If possible, I would
> > rather get an email saying DTS Package 1 failed versus having to look at
> the
> > each log file to determine which one failed.
> >
> > --
> > Thanks in advance,
> >
> > Steven
> >
> >
>

Job Fail

Hi

I have scheduled a job and the job is getting failed.I have scheduled two DTS packages in the job.But when I run the DTS Packages separately its running fine without throwing any errors.But when the Job fails I am getting the error message like "Dts package not found"

The error message which I am getting while the Job fails is

"Executed as user: TEST\sqlservice. DTSRun: Loading... Error: -2147217900 (80040E14); Provider Error: 14262 (37B6) Error string: The specified DTS Package ('Name = 'DTS_MASTER'; ID.VersionID = {A35AEABF-8F05-41B5-A4C9-47F57A3208B9}.{[not specified]}') does not exist. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed. "

Can anybody pls help me on this.

this knowledge base article might help: http://support.microsoft.com/kb/918760/en-us