Scheduling in SQL server 2000

Hi all,

I have a case :

I have an employee SQL-View (not table) in my sql server 2000 database. This view actually connect to an oracle database server, because all our employee data is located on the oracle database server.

This linked server soon proven to be the cause of poor application performance (response time is very slow). So we decide to put a local copy of employee table in our sql server 2000 database. We have successfully copy it to our database. But the most up-to-date employee data is still reside on oracle server. To deal with this problem we need to scheduling a sinchronization between sqlserver 2000 and oracle everyday at 01:00 o'clock midnight.

I never get task like this before, how to make a scheduling in sql server 2000? my friend suggest a dts scheduling but unfortunately I haven't found any tutorial about this for beginner

thanks

You could just schedule a linked server INSERT INTO statement or use the DTS wizard to create a package and create a SQL Server Agent JOB to run at the above time. If you are not the DBA SQL Server Agent wil need a service account with Admin permissions to run your package through JOBs. Try the links below for more about SQL Server Agent permissions and DTS sample code. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

http://www.sqldts.com

schedule package

I am trying to create a job to automatically execute a package.The package was created by use of the wizard.It’s very simple…export a view to a flat file.If I run the package as is it generated the file.However when I create a job and try to run the job it errors out with package cannot be found even though the path of the dtsx file is correct.

Shouldn’t I be able to schedule a package which was created by the wizard?Do you have any idea when I’d get a msg that package cannot be found even though the path is correct?

Please let me know your thoughts on this issue…I am at a lost.

Thanks,

Nats

Did you promote the package to the server where SQL Server Agent exists? (I assume you're using SQL Server Agent)|||Yes I am using SQL Server Agent. What do you mean "promote the package to the server"?|||Well, the package has to be on the server where SQL Server Agent is running.

If you developed the package on your desktop and are using SQL Server Agent on another machine, the package has to be moved to that machine.

If you do have it on the same machine, then you have to be sure that SQL Server Agent has access to the folder/file.|||

The package was save to my desktop...once i moved it out to the server it works fine.

Thanks for the the advise.

schedule a ssis job

I created a ssis package on my local developer sql server, the package is to connect to another sql server, and export a view to an excel file and send an email with excel attachment.

If I run the package manually it works great.

But now I want to schedule it to run as a job in my local sql server, I cannot make it right.

What I did is:

Create my credential to map to my windows log in
Then create a proxy account to map credential.
Then run the job using my proxy account, but I keep getting errors, . The package execution failed. The step failed.

I cannot get more detailed info.

Please advise.

ThanksThe end of this KB article has instructions for getting detailed info:
http://support.microsoft.com/kb/918760