Dynamic File location for DTS transfer

Does anyone know if this is possible:
I have an FTP server that will be receiving files. The directoryand file structure will be a folder with a client name (can be calledanything) and it will have files in it (these files will have the samefilenames as all the other directories. So I will have folderJimmyDoe with files a.txt, b.txt, c.txt and I will have JonnyDue withfiles a.txt, b.txt, and c.txt.
Now I'm trying to figure out a way to get that dynamic file location toa DTS package so I can import all the data from the text file into aSQL server. The way the SQL server will be set up is that eachFolder from the FTP site will be a separate Database and each file will1:1 with a table with the same name..
My biggest issue is figuring out a way to tell the DTS package the filelocation to pull all those files and then importing them to the properdatabase.
I'm not limiting the solution to DTS packages so if .NET can beincorporated to make it easier then so be it. But keep in mind Ican have up to 200 folders with 12 - 20 text files ranging fromhundreds of rows of data to many thousands of rows. And thepackage needs to be ran twice a day so time/performance is anissue.
To recap: Need DTS package that uses Dynamic file source and transfers data to Dynamic database destination.
(And I'll write slow VB.NET code to handle this before I create/manage 200+ DTS packages as a solution)
Any help at all is greatly appreciated.
How are you executing the package? If you execute dynamically, such as through the dtsrun utility or through SQLDMO code, you should be able to pass a value into a global variable. Then use the dynamic properties task to change the default global variable value to your new value.|||I've used DTS Run before so that's the only way I know how to dothat. How would I use the dynamic properties task to change thedefault global variable value? Can you give me a snippet,pseuodcode or something of how that would work?
Never heard of SQLDMO, what is that?
|||

netflash99 wrote:

Never heard of SQLDMO, what is that?

SQLDMO(SQL Server Data Management Object) Microsoft property it creates everything you do with Enterprise Manager manually through code but it uses System tables from the master to do its work so your code will be orphaned in SQL Server 2005 where those tables are really Microsoft Property you because cannot use them. DTS Run is good practice. Try the url below on using Global Variable. Hope this helps.

http://www.sqldts.com