SQL DTS Traverse subfolders, copy files, run package to import.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

SQL DTS Traverse subfolders, copy files, run package to import.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-13 : 08:17:45

Jason writes "Greetings,

I hope someone might be able to point me in the right direction on this one. I am new to this so....... here goes.

I have a DTS ActiveX script that I would like to traverse through a series of subfolders and copy selected files, rename them and drop them in another directory. Secondly, I need to be able to run a DTS package against the files that were copied to the holding tank. The DTS package imports the contents of the files into a series of tables for further processing. Then the process would move to the next subfolder and repeat the process, and again, and again, until all folders have
been processed.

Any ideas?

**************************************************
Set fso = CreateObject("Scripting.FileSystemObject")

Set f = fso.GetFolder("\\fileserver\f$\UserDBs")
For Each Folder In f.Subfolders

path = "\\fileserver\f$\UserDBs\" & Folder.Name & "\"
Set objContacts = fso.GetFile(path & "builder.dbf")

objContacts.Copy "\\fileserver\f$\holdingtank\" & FolderName &
"builderDBF.dbf", 1

Set objActivity = fso.GetFile(path & "builder.adb")

objActivity.Copy "\\fileserver\f$\holdingtank\" & FolderName &
"builderADB.dbf", 1

Set objListTable = fso.GetFile(path & "builder.ddb")

objListTable.Copy "\\fileserver\f$\holdingtank\" & FolderName &
"builderDDB.dbf", 1

Set objSales = fso.GetFile(path & "builder.sdb")

objSales.Copy "\\fileserver\f$\holdingtank\" & FolderName &
"builderSDB.dbf", 1

Set package = CreateObject("DTS.Package")

package.LoadFromSQLServer "SQLRPTS", "", "","256",,,,"FillSalesOppsTMP"
package.Execute

package.Uninitialize()
Set package = nothing

Main = DTSTaskExecResult_Success
Next

***********************************************************
Sincerely,

Jason
Dallas, TX"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-13 : 09:06:59

Thought about doing this in sql?
This will proces all files in a directory and it moves them to another (archive) directory after processing.
The processing coukld be a call to a dts package with the file name if you wish.

http://www.mindsdoor.net/SQLTsql/s_ProcessAllFilesInDir.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources