Passing parameters into DTS

[nwm76]

Gurus,

Could be a rather dumb question, but here goes anyway....

I've set up a simple DTS package in SQL Server 7.0 that I will be using to transfer a large number of Excel spreadsheets with an identical structure into a table in my database.

I would prefer not to have to go into the DTS GUI all the time and change the source Excel filename each time I want to run this. Is it possible to run the DTS package from within, say, a stored procedure and pass the filename as a parameter, and if so how???

Any suggestions much appreciated.

TIA,
Neil

 

[JayKusch]

Here is a trick I use. In the DTS package, set you excel source connection to a generic filename like "ImportXL.xls". This way the name stays the same for each run.

When all you need to do is a file Rename/Copy on the file that needs to be imported. One way to do this is w/ a Execute SQL Task task w/ an xp_CmdShell verb. Something like

EXEC Master..xp_CmdShell 'Copy C:\MyDir\NewExcel.xls Import.xls'

This way you can now execute another Copy command to move the original file, NewExcel, to an archive folder of your choice for safe keeping and then execute a delete command to clean the original file up and get ready for the next file to import.

This is just one of many ways to handle this operation. I am sure you can work this to suite your desired needs.


Thanks

J. Kusch

 

[nwm76]

That will do nicely...

Thanks a lot.