DTS Import from Excel to SQL hangs when Spreadsheet is Open

[klgrube]

Hi!

I have a DTS package that imports an Excel spreadsheet into a SQL table (and then executes a stored procedure to massage the data, moves it into other tables, etc.). However, it is possible that someone may be in the spreadsheet updating the information when the package runs. Right now, it looks as though the package just sits and waits until the file is open. That's not good. I need for the entire package to fail the moment it detects that the spreadsheet cannot be opened. The process runs every half hour, so it isn't that crucial that it import the file each time. It's just that I'd rather that it bypassed any file it can't open and write an error to the error log. I've tried setting the "connectiontimeout" to 5 seconds, but it doesn't seem to help.

Any suggestions?

Thanks!
Karen Grube
kgrube@ffres.com

 

[Corran007]

off the top of my head, i would want to look into having it copy the file that people actually update, then import the copy, then delete the copy. Ive had a similiar issue and i found this to be the easiest solution.