Unanswered: Setting DTS Global Variable from Excel Locks spreadsheet
I have a fairly simple task which I would like to accomplish, but it has turned out to be quite painful. I am working on SQL server 2000 in a DTS package.
I am using a Dynamic Properties Task to set a global variable. This variable come from querying an excel spreadsheet for a record count.
Upon Success of this task I am moving on to an ActiveX script. This script has a simple function. Check the global variable that was set in the previous step and do one of two things. If the reccount is 0 then it should just move on to the next step. If the record count is >=1 then it is supposed to send an email with the above spreadsheet as an attachment.
The variable is being set properly as I can use a msgbox at the top of the activex script. I have done a lot of debugging and as soon as I comment out the attachement line all works fine. I did a couple of tests and even after the global variable is set in the Dynamic Properties Task and has moved on to the activex script, the spreadhseet appears to remain locked. I can't open the spreadsheet until after the package is completed. Everything worked until I added the record count step. Is there anything I can do to unlock the spreadsheet so I can send it as an attachement?