Wednesday, March 7, 2012

Error with schedule a DTS package...

Hi experts...

I'm trying to schedule a DTS package (import some tables from Mysql database) but there is
an error and I don't know how to resolve it.

The error is always the same...
"The job failed. The Job was invoked by Schedule 24 (Import RT data). The last step to run was step 1 (Import RT data)."

I have tried changing the all the parameters in the job properties, but I always obtein the same message.

The DTS package works fine, I can execute and it works, the problem is the schedule...

Thanks in advanced...What account is the scheduled task running under? Normally if you run it yourself and it works, you are running the DTS package with YOUR permissions. However, if you run the DTS package under a schedule, it is using the standard SQL Server account (which is either Local System or another specified account). You need to change the account the schedule runs under to have enough permissions to run properly.|||Thanks for your reply...

I have one more question... How can i change the account of the schedule task?... I don't know how can I change it.

The strange thing is that I've done the same with another databases, only one thing is different, the source, the schedules that work fine are between SQL databases and this is from Mysql to sql database... but this is not the problem becouse as I said I can execute it and it works fine.

thanks again.|||If you schedule a job in SQL Server, then on the Job Properties dialog window, click the Steps tab. Highlight the step (here, executing the DTS package job) that you want, and click Edit or properties. In the Job Step properties dialog window, click the Advanced tab, and there is a Run as user dropdownlist. Try changing that to your credentials.|||Hi Again,

There is a problem with this solution... the step type is CmdExec and you can't see this option unless you choose SQL Script type... I don't understand what's happening, I have read the documentation about Package Scheduling and Security Issues but I don't know how to change who invoked the job...

thanks again...|||That's an operating-system command or executable program command step. I'm not familiar with that. Is there any way to change the security context in the DTS package designer?|||Hi again...

The step is an operating system command... but as I said this job is similar to other... the only I can see related to the security context is the owner, and it's the same in all jobs that I have done (and they work fine).

thanks for your interest.|||Aha, I found this from databasejournal.com (link

You can also schedule execution of DTS packages using SQL Server Agent jobs. This ability is granted by default to the public group, based on permissions to execute sp_add_job and sp_add_jobschedule stored procedures in the MSDB database (this applies to all SQL Server Agent jobs, not just the ones that invoke DTS packages). Typically, it is recommended to restrict access to them to a limited number of privileged logins. While jobs typically execute in the security contexts of their owner, in the case of DTS packages this might be different. The reason is the fact that a task that contains invocation of a DTS package accomplishes this by the running DTSRun command line utility, which involves, in turn, the use of CmdExec. If you limit permissions to launch CmdExec jobs to members of the SysAdmin fixed server role (which is the default behavior), jobs will still execute in the same security context as their owners. However, you can change this default in the SQL Server Agent Properties dialog box, accessible by selecting the Properties option from the SQL Server Agent node under the Management folder in the SQL Server Enterprise Manager console. In the Job System tab, you need to clear the checkbox next to the "Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps" and provide a Proxy Account name and password. The proxy account provides the security context for execution of DTS packages, so ensure that it has access to all relevant data stores, file system paths or COM components, which might be used by ActiveX scripting tasks. Remember, however, that at the same time you allow users without SysAdmin privileges to run potentially dangerous ActiveScripting jobs.|||Thanks a lot, but I haven't resolved the problem, I have seen this choice, and I put an administration user, but It didn't work... I don't know...

Sorry about your lost time.|||An administrative user for the box, or an administrative user on the domain? Domain permissions could be the problem.

You could also try posting to the Microsoft newsgroups, specifically the sqlserver.dts one:

http://communities.microsoft.com/newsgroups/default.asp

Then look for sqlserver.dts in the left frame after it downloads (it takes a while).

No comments:

Post a Comment