Look for any file

[balistikb]

Is it possible to have a DTS look for a file with anyfile name in a folder? I would like to import a text file that the file nmae changes everyday.

 

[nigelrivett]

You can do it using dts but easier using bulk insert (5 times today I've said that).
Here's a proc which does it and a few other things

http://www.nigelrivett.net/ImportTextFiles.html

The important thing is to archive the file after the import so that you can process everything with that file mask in the directory.

If you really want to use dts you need to scan the directory to find the file name and set a global variable (easiest) with the filename then run a dynamic properties task (or activex script) to set the property in the import task. You can either code a loop withing the dts package to deal with all files but probably easier to get the filenames from an SP (or query in the jobstep) and use dtsrun to set the global variable (why I advise using a global variable).

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

 

[balistikb]

Ok, it seems that you are suggesting a couple of solutions. I am new to this so what do you think would be easiest for me to duplicate? I tried looking at that link and I can't really comprehend what is going on. Please Help.

 

[nigelrivett]

Easiest is to use bulk insert.
Look at the link I gave you - it gets all the files in a directory and loops through them.
For each file it does a bulk inisert and moves the file to an arcghive directory.
Just take out the bits you need.


This gets a list of all files in the directory
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%'

This loops though each file
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

This bulk inserts the file
select @cmd = 'bulk insert'
select @cmd = @cmd + ' ##Import'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd + ' with (FIELDTERMINATOR=''|'''
select @cmd = @cmd + ',ROWTERMINATOR = ''' + char(10) + ''')'

truncate table ##Import

-- import the data
exec (@cmd)

This archives the file
-- Archive the file
select @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath + @FileName
exec master..xp_cmdshell @cmd


The proc MergeBCPData just moves the data to the production table.


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

 

[balistikb]

Ok is the @cmd @FilePath @FileNameMask all things that I need to declare? As well as @File @FileName? I am not really sure where to begin. I did read through the link and I am not sure what order things need to be done. This is why I asked for help understanding the code.
Forgive my ignorance but like I said I am new to this.
Thanks for your help.

 

[nigelrivett]

@cmd is just a variable it uses.

@FilePath is the directory which contains the files to import (including the trailing "\".


@FileNameMask is the mask which defines the files to import e.g. impfile*.txt.

You also need an @ArchivePath which deines where to put the files afetr the import.

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