November 6, 2003 at 4:01 pm
I am new to DTS but have successfully made and scheduled a DTS pkg. I am having results of a t-sql query written to an excel file and stored on the same server as the database. When i add a send mail task to mail that file as an attachment the dts pkg fails saying it can't open the attached file. to see if it was a permissions problem i created a another dts pkg that simply mailed that already exisiting file. i ran that dts pkg and the file was attached to an email without problems and the email was sent. to see if it was a file locking problem i've tried to add steps between the excel file being created and the attachment of that file to the email and i still get the same error.
is there a known problem or something i'm missing on mailing an excel file that is created in a dts pkg.
also, i tried this with a csv file in place of excel and everything worked fine. but i really need it to be excel so i can maintain the formatting in the excel and just refresh the data at regular intervals.
November 6, 2003 at 4:11 pm
The DTS package still holds a handle to that file. I've seen similar issues. I'd setup a 2nd job step to do the email so the package closes.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 6, 2003 at 4:45 pm
i think that makes sense now... i went to the jobs area and see how i could add a second step. so are you saying setup a second but separe dts pkg to pickup the file, attach it and email then add that dts pkg in as the second step? how can i add a dts pkg to an existing job like that?
November 7, 2003 at 6:16 am
In the same package create a new step to do the email (remove code from first step) and change the properties of the first step to quit on failure so that you do not email if there is a problem.
Far away is close at hand in the images of elsewhere.
Anon.
November 10, 2003 at 7:24 pm
You could always modify the workflow properties of each task in the package to "Close Connection on Completion" this will free up the exclusive lock on the Excel file without having to go through adding a second step to a SQLAgent Job.
Trey Johnson
----------------
Principal Architect
BI Practice
Encore Development
------------------
VP of Marketing
Professional Association for SQL Server (PASS)
------------------
Founder
SQLServerBI.com
Trey Johnson | Chief Business Intelligence Architect | Cizer Software (www.cizer.com)
Who? - Cizer - http://www.cizer.com/about.htm - Blog - http://www.sqlserverbi.com/
What? - Products enhancing Microsoft Business Intelligence - http://www.cizer.com/products.htm
Wow! - Empower your Developers.... NEW Drop In Reporting - http://www.cizer.com/cnr-drop-in-reporting.htm
How? - BI Training - http://www.cizer.com/training.htm - Cizer Solutions - http://www.cizer.com/solutions.htm
November 11, 2003 at 10:46 am
Trey, thanks a lot! That's exactly what I needed. That released the lock on the file and it was able to email with no problems. I really appreciate the help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply