November 30, 2001 at 7:55 am
I am trying to export a recordset to excel, change or copy the excel file to a new file, and then email this result to a group of users. I am using a vbscript task to execute the file copy (changing the name). The problem I am having is that the excel file is locked and therefore nothing is copied. The file is created but the file is corrupt. Further more, if I skip this task and go straight to an emailing task that sends the file, the task fails. Any ideas would be greatly appreciated.
Mike Walsh
November 30, 2001 at 8:46 am
What I have found to work is by setting up an Excel file as a data source, setting up the SQL data source, and finally utilizing a transform data task to pump the data from the SQL connection to the Excel sheet. In order for this to be successful, you have to create a template Excel file that can be replaced prior to run. For example, I have an execute SQL Task which does an xp_cmdshell command to delete the current Excel file and then copy a template into place. That way you always have fresh data rather than continuing to populate the same Excel sheet with old data.
Ultimately your DTS package (I'm assuming you are using DTS) should have steps like the following;
-SQL Task to delete current file and copy in template using xp_cmdshell (the vbscript should work as well but, I LOVE SQL)
-SQL Connection
-Transform Data Task (tie between SQL Connection and following Excel Connection) that should contain your options including what data is being selected out of SQL and where it is going in Excel.
-Excel connection
-Mail statement (either Mail Task of SQL Task)
Not really sure why you would be having problems with the Mail task as you mentioned but, I would tend to think it would be caused by the corrupted file issue.
Not sure if this helps all that much, but I have used this scenario in the past for a temp reporting solution.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 30, 2001 at 9:51 am
I've had this problem and solved it by doing the email in the second step of the job rather than the DTS package.
Steve Jones
November 30, 2001 at 10:12 am
David... Thanks For your response. I tried the method you suggested and am still having some problems. If I do the file copy first there is no problem. If you try to do the copy at the end of the process it reports success but does not copy the file apppropriately. It is almost like there is a lock (excel is open) on the file and can't be copied. This is the same for the email as an attachment. Any thoughts.
Thanks
Mike
November 30, 2001 at 10:34 am
Are you doing this within DTS and if so, are you using the "On Success" or "On Completion" links between the job steps?
If yes, then I am puzzled.
If no, then I would add an "On Completion" link between the step for the transformation and the copy or email. It may be trying to do those steps while really still in process.
Let me know. Interesting..... Thanks.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 30, 2001 at 10:37 am
Sorry, one additional note, I agree with Steve's suggestion for taking the mail step out of the package and making it part of the job. I recently started doing that on all my packages and it seems to be a cleaner way of doing things.
DTS error reporting is tough to interpret (i.e. unclear) when you have a mail failure but, when it is a job step, you know what failed clearly.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 30, 2001 at 1:19 pm
Guys,
Thanks for the great advice. I was able to send the email in a job using xp_sendmail. Is this the only way to do it?
The only unfortunate thing about this is it seems to be a little klugey(?). It would seem a little more streamlined if you could execute all tasks from the dts. It almost appears like the connection is live until all steps are complete. Would this cause a lock on the file like another user has it opened?
Thanks
Mike
November 30, 2001 at 1:32 pm
No, you can use an Execute SQL task within DTS for the sendmail and use the same code you have in the job step. The one concern with doing this in DTS is that you will not get the error reporting you would hope for if the mail step fails. Much better if you can look at the job history and see that step 2 or 3 failed, look at the job and see that it was just mail. Just my opinion.
As for the file being open, it should release as soon as the transform task is done. I just verified that I could put in an Execute SQL Task to move the Excel file following the transformation and it worked great.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply