need to 'select' filename from hardrive - slightly odd request 1

[MaffewW]

Hi chaps, dont know if this can be done easily

have a DTS package pointing to a UNC share. A file is placed in the folder for importing into database + some other steps. Theres only ever one file loaded into the import folder at a time then the package is run.

The first step uses xp_cmdshell to rename the file as its a different filename everytime.

declare @cmd varchar(200)
select @cmd = 'rename \\dumas\data\client\dataload\*.csv import.csv'
exec trinity.master.dbo.xp_cmdshell @cmd

Previously this has been fine. Now I need to record the filename in a table so need a step before this to capture the filename. Anyone any idea how to go about this?

cheers

Matt

Brighton, UK

 

[vongrunt]

One way... probably not the best but works:

declare @cmd varchar(200)

[b]set @cmd = 'dir \\dumas\data\client\dataload\*.csv /b'
create table #out( col nvarchar(255) )
insert into #out exec trinity.master.dbo.xp_cmdshell @cmd

declare @filename nvarchar(255)
select @filename = col from #out where col is not null

drop table #out

print @filename[/b]

set @cmd = 'rename \\dumas\data\client\dataload\*.csv import.csv'
exec trinity.master.dbo.xp_cmdshell @cmd

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[MaffewW]

works good for me mate cheers

Matt

Brighton, UK