DTS - text file to table

[nyzja]

Hi! I need to create a DTS that pulls data from a text file and send them to a table. The name of the text file would depend on the date and time of the day (this process would have to be run hourly). I wrote the script to pick up the correct date and time. But I'm not sure what's the next step.

Please advise.
Thanks!

 

[DrSql]

As long as the format is not changed then it should be easy.
As a first step of your DTS I would rename the file to same as you configured intially. If you want to keep the copies of the file move a copy to a folder.
[tt]
declare @data
set @data='copy C:\Folder\File* C:\Folder\Bak\
exec master..xp_cmdshell @data
set @data='ren C:\Folder\FileDate* C:\Folder\NewFileName.txt'
exec master..xp_cmdshell @data
[/tt]
On sucess of above sql stmnt perform your data pull. Last step, delete the file
[tt]
declare @data
set @data='del C:\Folder\File'
exec master..xp_cmdshell @data
[/tt]




Dr.Sql
Good Luck.