Scheduling a DTS Package Questions

[bassguy]

Hello All,

I am trying to copy data from a SQL 2000 server inside our ISA to a SQL server in the DMZ.
A few questions:

1. What is the difference between Using enterprize manager to connect to the inside box and using Terminal Services to the box then connecting with TS? I am referring to differences for the ISA server.

2. Why did an Import Package created on the External (desination) Box save itself as a job and a DTS package on the Internal (source) box?

3. Can I use the Import Export wizard to schedule jobs in this fashion?

any help would be greatly appreciated

Thanks

bassguy

 

[Flloyd]

Here are my thoughts:

1. The difference is the context of where Enterprise Manager is invoked. If you run EM on the 'external' box and manage objects or jobs on the 'internal' server then any processing will occur locally on the 'external' box. If you TS over to the 'internal' server and then invoke EM any processing will occur locally in the TS session on the 'internal' server.

2. Again, likely a context issue. I would expect this if you were running EM on the 'external' box but had the 'internal' box selected while creating and saving the Import DTS package it would have been saved on the 'internal' server.

3. You do have the option to schedule DTS packages.

Lloyd Freiday

 

[bassguy]

O.k. Then here is the scenario:

while T.S. on the external server used the Import Wizard to create and schedule a database copy of a couple of tables.

when I hit run, it works.

when the package runs it does not work. The package was saved on the internal server as was the job.

 

[Flloyd]

Is it possible that in the first case you are logging in to the internal SQL with a user/pwd that has more 'power' than the user context that the job runs in? (e.g. you connected to SQL as "sa" and the package ran ok, but SQLServerAgent runs under a user account with less privledges)

After that, I would review the source and target objects to verify that they are available to the job natively on the internal machine as they are in the TS session (long shot).

Lloyd Freiday

 

[tlbroadbent]

I agree with Lloyd. Almost all problems running DTS packages as Jobs are permissions related. Most of the time, the SQL Agent account doesn't have permissions to access a file or folder on a remote server or cannot connect to the remote server at all. Another frequent problem is the use of MAPPED drives that are available to the package creator but not to the SQL Agent account.

The knowledgebase article at the following link has some useful information when running a DTS package as a job.

INF: How to Run a DTS Package as a Scheduled Job

http://support.microsoft.com/default.aspx?kbid=269074

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm