Setting up a global varaible

Question

Setting up a global varaible

FdoArens

SSC Enthusiast

Points: 196
More actions
September 22, 2004 at 12:23 pm

#163206

New to SQL Server and DTS. How can I add a global variable to load many text files into defined SQL Server tables?
Here is my case: Our offices host thier own SQL Server Database and Intranet application. Main office received those flat the text files from them on nightly basis to populate databases used by main office to be read only by external users.
So far, I was able to succesfully create those packages. Now, our offices are requesting my package, to create text files to be shipped to them. Of course the location where they have to place all those text files (about 200 text files are created with the DTS)I varies from office to office. I use the most common location to create my DTS (E:\CH_Files&nbsp Some offices have E:, but some other use D: (the folder name is always the same, what varies is the drive to be used) and so on.
So the long story is to ask HOW CAN I ADD A GLOBAL VARIABLE TO THE PACKAGE TO BE ABLE TO SHIP THIS DTS TO EVERY OFFICE WITHOUT HAVING THE CHANGE TO EACH INDIVIDUAL STEP TO EACH INDIVIDUAL OFFICE!!! help please!!!!
If I have to change each step and each office it will be a never ending task!! I read that it can be done, but do not lnow how!! Thank you for all your help!!!!!!!!!!!!!!

Viewing 6 posts - 1 through 5 (of 5 total)

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

bgmst5 SSC Eights! Points: 964 More actions · Answer 1

Hey,

Could you have the package create all of the files, then iterate back through an push the file to the appropriate drive (based on information stored in a table perhaps)? I don't know if you want to store 200 file names in global variables.

You could push the files several ways, although I think probably the best way is to use an ActiveX task. vbscript supports copying or moving files. The vbscript could use a database table (or other source such as global variables if need be).

Brian

philcart SSC-Forever Points: 47796 More actions · Answer 2

So do you want the offices to run your DTS package on their server, or do you want your DTS package to send just the output to each office?

If it's the first one, you could setup the package to accept the destination as a global variable and then set it in the package using a Dynamic properties task.

For the second one, I'd put all the destinations in a table and then process each one in turn.

Take a look at the following articles,

http://www.sqldts.com/default.aspx?298

http://www.sqldts.com/default.aspx?246

http://www.sqldts.com/default.aspx?200

--------------------
Colt 45 - the original point and click interface

FdoArens SSC Enthusiast Points: 196 More actions · Answer 3

Phil,

That's is exactly what I need. Set up the global variable, so they can save it and use it as they need. I will be sending the package and they will run it at their server as they feel fit. How can I do that? I am absolutely new to this. Thank you

philcart SSC-Forever Points: 47796 More actions · Answer 4

You'll need to setup a couple of global variables in a the package along with a Dynamic Properties task. Use the dynamic properties task to set the appropriate properties on the connections.

EG: This is a DTSRUN command from one of our scheduled extracts. The /A parameters set the values for the global variables. Inside the package the dynamic properties task uses the first three variables to set the SQL Server connection properties and the last one to set the Text File destination properties.

DTSRun 
/S "SQLASX1\WEB" 
/N "EXTRACT Data" 
/A "SERVER_NAME":"8"="SQLBOX" 
/A "DBASE_NAME":"8"="DBA" 
/A "TRUST_CONN":"8"="True" 
/A "EXTRACT_FILE":"8"="C:\Extracts\ExtractData.csv" 
/W "0" 
/E

--------------------
Colt 45 - the original point and click interface

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 5

If these are the only files in that folder I would setup a Global Variable to hold the path to the directory and then use the FileSystemObject to read in each file (and then archive or remove the original - preferably archive ). This allows you to handle 0 to many files without adjusting your package. To create the Global Variable - in DTS designer select the Package option from the menu and then select the Global Variables tab.