issue with recurive script - SQL Server Forums

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)

issue with recurive script

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-27 : 20:07:20

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

Dim fso
Dim f
dim objCN
dim objCMD
dim objRS
Dim file_modified_date

strConnectionString = "Provider=SQLOLEDB.1;Password=data$;Persist Security Info=True;User ID=data_migration;Initial Catalog=import_export;Data Source=10.50.220.139\REALM"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("N:\Test FilePath")

For Each file In f.Files

'File Name
'-----------------
file_name= file.Name
'msgbox file_name

'File Location
'---------------------
file_location =file.path
'msgbox file_location

'Length of File
'------------------------
split_extension = Split(File.Name, ".", -1, vbBinaryCompare)
length_of_file_name = Len(split_extension(0))
'msgbox length_of_file_name

'File Extension
'------------------------
file_extension =split_extension(1)
'msgbox file_extension

'Doc Abbreviation
'----------------------------
file_abbreviation =Right(split_extension(0),length_of_file_name-10)
'msgbox file_abbreviation

'File Modified Date
'------------------------------
file_modified_date = file.DateLastModified
msgbox file_modified_date

set objCN = CreateObject("ADODB.Connection")
set objCMD = CreateObject("ADODB.Command")
objCN.open strConnectionString
objCMD.ActiveConnection = objCN
objCMD.CommandText = "test"
objCMD.CommandType = 4 'adCmdStoredProc
objCMD.Parameters.Append objCMD.CreateParameter("@file_name",200,1,100,file_name)
objCMD.Parameters.Append objCMD.CreateParameter("@file_location",200,1,7000,file_location)
objCMD.Parameters.Append objCMD.CreateParameter("@file_extension",200,1,20,file_extension)
objCMD.Parameters.Append objCMD.CreateParameter("@file_abbreviation",200,1,20,file_abbreviation)
objCMD.Parameters.Append objCMD.CreateParameter("@file_modified_date",135,1,8,file_modified_date)
Set objRS = objCMD.Execute
set objRS = nothing
set objCMD = nothing
set objCN = nothing

Next

Main = DTSTaskExecResult_Success
End Function

Here is the Vbscript for getting information of a file in a particular directory.now I have an
issue where there can be multiple folders and subfolder in there.how can I search and get those
information.How to use a recursive function in vbscript for DTS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-27 : 20:10:21

I suggest posting this in a VBScript forum as this isn't really SQL Server related even though this code is going to execute in a DTS package. Here's the VBScript forum over at tek-tips.com:

http://www.tek-tips.com/threadminder.cfm?pid=329

Tara

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-27 : 20:12:45

what are you doing??

to get files in a directory:


Create Table #diroutput (id int identity(1,1), diroutput varchar(1000))

Insert Into #diroutput
exec master.dbo.xp_cmdshell 'dir /s /b /a-d "c:\*.*"'

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-27 : 21:02:51

to do something to all files in a folder, including subfolders,you write recursive function like this:


sub Dostuff(folder)
   dim Fd
   dim Fl
 
   for each fd in folder.Folders
       DoStuff(fd)
   next

   for each fl in folder.Files
       <do whatever you need here>
   next
end sub

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources