home
-- Datawarehouse Builder - File Detection Polling
Author Nigel Rivett
Alter proc s_FilePoll
@Source varchar(50)
as
/*
@Source defines the type of file with which we are dealing
Here the types are hard coded but it might be better to hold information in a table
declare @t table (s varchar(100))
declare @FileName varchar(100)
declare @Data varchar(100)
if @Source = 'Classic'
begin
insert @t
exec master..xp_cmdshell 'dir /B \\FileShare\ExternalDataSource\Classic\File_*.tab'
delete @t
where s is null
or s not like 'File_%.tab'
select @FileName = ''
while @FileName < (select MAX(s) from @t)
begin
select @FileName = MIN(s) from @t where s > @FileName
exec s_FileProcess @Entity = 'Classic' ,
@Action = 'InsertFile',
@Status = 'FileDownloaded',
@FileName = @FileName,
@Data = @Data
end
end
if @Source = 'NewWorld'
begin
insert @t
exec master..xp_cmdshell 'dir /B \\FileShare\ExternalDataSource\NewWorld\File_*.tab.gz'
delete @t
where s is null
or s not like 'File_*.tab.gz'
select @FileName = ''
while @FileName < (select MAX(s) from @t)
begin
select @FileName = MIN(s) from @t where s > @FileName
if SUBSTRING(@data, 26,2) = 'HR'
select @data = left(@data,24) -- hourly file
else if SUBSTRING(@data, 19,2) = 'DY'
select @data = left(@data,17) -- daily file
else
begin
raiserror ('Invalid filename %s', 16, -1, @FileName)
return
end
exec s_FileProcess @Entity = 'OmnitureHermes',
@Action = 'InsertFile',
@Status = 'FileDownloaded',
@FileName = @FileName,
@Data = @Data
end
end
go
home