DTS Package Migration --- File Path Issue

Question

DTS Package Migration --- File Path Issue

danielleinsf

SSC Veteran

Points: 232
More actions
April 4, 2002 at 5:33 pm

#104892

BACKGROUND: We are currently working on Win 2000 service Pak 2 and SqlServer 2000 on dev , Test and Production systems. The Dev and Test instances are in local office (in one domain)and Production system is in Data Center in another domain ( We don't have access to the server - Except through using Enterprise Manager with limited access as dbo).
Problem :
The DTS package, developed on the development box, is reading a data file from E: folder on the local development Win2000 machine. The same data file is on the "D" Folder on production Win 2000 box in the other domain at the prod. data center. When we try to migrate the script to production using Sql Enterprise Manager - We are not able to change the folder from E to D (Because D folder on local box is CDROM).
We cannot map a drive from the production box to our local box and use this drive specifically.
So we were wondering is there any we can send the file name as a parameter to DTS?
We do not have an option of using VB for DTS.
Your help is appreciated.

"Doing easily what others find difficult is talent; doing what is impossible for talent is genius." -
Henri-Frederic Amiel

"Real knowledge is to know the extent of one's ignorance." - Confucius

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Deuce SSCrazy Points: 2712 More actions · Answer 1

Create a share of the same name on both boxes and use the UNC path to the file. When you move to production, you only have to change the name of the server.

\\testbox\mysare\myfile.txt

\\prodbox\myshare\myfile.txt

danielleinsf SSC Veteran Points: 232 More actions · Answer 2

We tried this and it does not work.

"Doing easily what others find difficult is talent; doing what is impossible for talent is genius." -
Henri-Frederic Amiel

"Real knowledge is to know the extent of one's ignorance." - Confucius

Deuce SSCrazy Points: 2712 More actions · Answer 3

Deuce

SSCrazy

Points: 2712

April 9, 2002 at 10:52 am

#426886

What doesn't work about it?

danielleinsf SSC Veteran Points: 232 More actions · Answer 4

DTS is not accepting relative path like you mentioned (i.e., as you suggested previously "..replace \\testbox\mysare\myfile.txt

by \\productionbox\mysare\myfile.txt")

. It only accepts mapped drive name like D:\myshare\myfile.txt. or E:\myshare\myfile.txt. And also as I mentioned before, I don't have physical access to the production system, to enable me to map drives on that box with a tool like PCAnywhere (because of the data center security). This is a typical issue which we have found with regard to defining an automated process for object/code promotion from a test to a production environment. Sometimes there is a necessity to have this automated so that, in our case when we have a tight promotion window and less than free access to shared server resources, we can effect a smooth fast promote process.

Any other ideas?

Eg

"Doing easily what others find difficult is talent; doing what is impossible for talent is genius." -
Henri-Frederic Amiel

"Real knowledge is to know the extent of one's ignorance." - Confucius

Simon Sutcliffe SSC Eights! Points: 807 More actions · Answer 5

Try creating a database table which contains the name(s) of the files to be imported.

The first step of the DTS would be to read the name of the file to be used from the table, the second would be to assign it to the Text File Source File Name parameter. These steps can all be achieved using the Dynamic Properties feature of DTS's.

Then provided the database table in production has the correct name reflected in it then the file will be processed accordingly.

This can lead to quite a versatile solution for processing multiple sets of files, since the table could be made generic to work with many types of interface files. eg.

tbl_Interface

Interface_Id Integer

Interface_File VarChar(1000)

Etc.....

Each Interface_Id could relate to a different file.

Hope this helps.

Simon Sutcliffe

"To ask is human, to assume is dangerous"

throbinson SSC Enthusiast Points: 104 More actions · Answer 6

All my environment sensitive properties are set via a dynamic properties task in my packages. This includes paths and more specifically connections. As I move through DEV to UA to Production, all on different machines, I set these properties using global variables that are in turn initialized by the command line as the package is called (either by Autosys or the Scheduler). In other words, wherever I am executing, I pass the proper variables into the packages and they are set by the dynamic properties task as the first step of the package. Most properties that are editable in disconnected Edit are available to be altered by the dynamic properties task.

danielleinsf SSC Veteran Points: 232 More actions · Answer 7

Thanks so much...I will give this a try

"Doing easily what others find difficult is talent; doing what is impossible for talent is genius." -

Henri-Frederic Amiel

"Doing easily what others find difficult is talent; doing what is impossible for talent is genius." -
Henri-Frederic Amiel

"Real knowledge is to know the extent of one's ignorance." - Confucius