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.