FSO & T-SQL & DTS - 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

SQL Server Development (2000)

FSO & T-SQL & DTS

Author

Topic

punchcardRay
Starting Member

7 Posts

Posted - 2004-06-22 : 11:11:41

I have a reasonably simple problem, I want to send all of the files from a folder to a DTS package , one at a time. The programming constraint I have is that I do it in T-SQL for Maintenance reasons.
THe VB code below works
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("e:\downloads\cibtfee\")
Set files = folder.Files
For Each file in Files
print file.Name
Next
But I cant use the VB solution

I did some t-sql work to copy this
declare @FolderID int, @fileid int, @Sfiles varchar(50)
EXECUTE @oResult = sp_OACreate 'Scripting.FileSystemObject', @oFSO OUT
execute @oResult = sp_OAMethod @oFSO, 'GetFolder', @FolderID OUT, @Download_path
execute @oResult = sp_OAMethod @FolderID, 'files', @fileid OUT
select @fileid
execute @oResult = sp_OAMethod @fileid, 'files', @sFiles OUT
select @sFiles

I can get a Count of the files but I canot get the file NAMES
to come back form the Files Method,
Does anyone have a small piece of code I could use as a starting point

TIA
Ray

Ray

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-22 : 12:24:26

Easier to use a commandline
see
http://www.nigelrivett.net/ImportTextFiles.html
for fso see here for how to load it
http://www.nigelrivett.net/CheckIfFileExists.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.

punchcardRay
Starting Member

7 Posts

Posted - 2004-06-22 : 13:15:54

I don't know the file names in advance, they have a format of
WRR20040621.dat,WRR20040620.dat,WRR20040619.dat and I might have between 1 and three of them.
I was hoping to do a loop throught the list
e.g. for each @filename
----
next
Thanks
Ray

Ray

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-22 : 13:48:17

Look at the code I pointed to - it imports all files in a directory - you can use it to get all the file names and work from there.

create table #Dir (s varchar(8000))
select @cmd = 'dir /B ' + @FilePath + @FileNameMask
delete #Dir
insert #Dir exec master..xp_cmdshell @cmd

delete #Dir where s is null or s like '%not found%'
while exists (select * from #Dir)
begin
select @FileName = min(s) from #Dir
select @File = @FilePath + @FileName

-- remove filename just imported
delete #Dir where s = @FileName
end

==========================================
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