July 20, 2003 at 8:32 pm
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 available again. 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
July 21, 2003 at 1:47 am
Hi Karen,
quote:
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 available again. 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.
not directly an answer, but just some observations I've made here.
1. importing a file, that potentially could be in use, is something I try to avoid. I guess you have your reasons doing so?
2. Although I think Excel could be regarded as the least common denominator among many users, what about getting rid of Excel and implement the whole stuff in SQL Server?
3. Excel issues an error when you want to open an already opened file in shared mode.
4. all intensive searching is useless, when the Excel option 'Shared usage allowed'?!? (well, the third menu entry in Extras) is checked.
HTH
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply