Using DTS to Load Files with Random Names

[caper1175]

Hi all, I'm fairly new to DTS however I believe that this is the tool needed for my situation and need some assistance.

We have a program which is FTPing csv files to a particular folder every 30 minutes or when the file reaches 20MB, whichever comes first. The files are labeled as such chr0001.1, chr0002.1 and so on. Once a file reaches chr9999.1 the next file would be called chr0001.2 and so on. The extensions can go up to .999 after that the cycle starts all over by overwriting files.

I need to make sure that all of these files get loaded into a particular table. So each time DTS runs, say every 30 minutes, I need some type of process where it is able to check for any NEW files before loading any new files into the table.

If anyone has suggestions or detailed steps that I must take, it would be greatly appreciated. Remember, I'm very new to DTS. Thanks!

 

[DrSql]

You are in the right direction, I assume you might have been able to import 1 file. I would name a standard name somthing like chr.csv and create a DTS.
Logic: Evrytime you recive the file you need rename the file chr.csv, if you need copies please move a copy to seperate folder.

Step one Execute a Sql script prior to starting your DTS.

SQL Script==>On Success==>Source==>Transformation Task==>Destination.

SQL Script:
[tt]
declare @data char(255)
select @data='copy C:\Folder\chr0*.csv C:\Folder\Backup\'
exec master..xp_cmdshell @data
select @data='del C:\Folder\chr.csv'
exec master..xp_cmdshell @data
select @data='ren C:\Folder\chr0*.csv C:\Folder\chr.csv '
exec master..xp_cmdshell @data
[/tt]

Hope this make sense, if you need any clarifications let me know.



Dr.Sql
Good Luck.

 

[mrdenny]

This can also easily be done with a single command in a batch file using BCP to load the data.

@for %%a in (`dir c:\folder /B`) DO BCP {Table} in -T -i %%a

You will need to play with the BCP switches to get them right.

From a command prompt "bcp /?" will give you the switch options. "help for" will give you the doc on how to use the for command.

You'll want to run this from a batch file. For doesn't like working correctly if you just type the command into the doc window.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief