DTS on remote server
-
Hi All
Just want some feedback on a process currently being formulated to load data into a small Datamart.
The environment,
Server1 - NT4 sp6, Running COnnect:Direct(basically a secure FTP program with added extras)
Server2 - Win2k, SQL2K DataMart
** NOTE **
- DTS Packages are not allowed to be run on Server2.
- Application development in VB or other language is not an option.
The proposed approach
- Have DTS packages saved as storage files on Server1.
- Call DTSRun from Connect:Direct to execute the packages.
- The packages load data from text files into the DataMart staging tables. Then perform any required data transformations before loading the data into the dimension and fact tables of the datamart.
The data loading would be done via BULK INSERT. All necessary data transformation would be done using stored procedures.
My thinking is that this approach will maintain a RPC connection between the two servers for the duration of package execution. Given that one package will conceivably call 3 or 4 other packages, this could mean a considerable length of time. Something the network guys will frown upon.
What are people's thoughts on this process? And given the environment, are there any suggestions?
Thanks
Phill
--------------------
Colt 45 - the original point and click interface
-
I thought you would have had another SQL server from which to run a package that just uses Server2 as the source and destination.
Can you run a saved package with DTSRun? I have never tried anything like that before. Does that work?
Bruce Szabo, MCSE+I, MCDBA, MCSD
Bruce Szabo, MCSE+I, MCDBA, MCSD -
quote:
I thought you would have had another SQL server from which to run a package that just uses Server2 as the source and destination.Can you run a saved package with DTSRun? I have never tried anything like that before. Does that work?
Bruce Szabo, MCSE+I, MCDBA, MCSD
Yes you can use DTSRun. It has a command line parameter where you specify the name of the file.
Phill
--------------------
Colt 45 - the original point and click interface -
The DBA and the Network guys are always going to be at odds. From a networking perspective, it makes more sense to copy the downloaded file onto a share on Server2, then load it. The DBA doesn't want anything but SQL Server stuff happening on Server2. Mr DBA doesn't want his drives doing anything but SQL work. Unless you're on a very slow network, what you propose sounds like a good solution.
Just my two cents.
John
-
Networks usually have the capacity to handle it. I'd guess(!) most are 100m switched these days. Now if you're going across T1 or slower, might want to reconsider.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply