Dynamic DTS?
-
I have a requirement to provide an automatic reporting tool that runs each night during down time, then emails the appropriate employee with an attached Excel file showing the errant data. If a certain amount of time has passed and the error indicated in the report has not been resolved, it emails the employee’s manager as well.
I have a couple ideas in mind, both using a stored procedure and one or more DTS packages. I want to keep the number of SPs and DTSs to a bare minimum, and creating DTSs on the fly then destroying them is not out of the question, although that approach seems to be too much cpu overhead in my opinion.
The need for a DTS package comes from the requirement that errors be reported in an Excel file, since a simple email wraps the result set making it difficult to read.
The trouble for me is my lack of DTS knowledge. What I’d like to do is create one DTS package that’s called by a looping SP passing it a string variable with each iteration that represents the query that the package will run. Queries are stored in a table, and the SP would loop through all the queries and only call the DTS if there is a result set. (if there are results, then there are errors). I know that DTS supports global variables, but don’t know if this can be done in the manner I’m suggesting. By the way, I’m using SQL Server 7.0.
Another thought is to run the report query in the SP and only use the DTS for transformation. This would require creating a table on each loop iteration to store the result set, then dropping it before the next iteration. The question then becomes, how do I create a table with the appropriate number of columns when each query will return a result set with a different number of columns?
Whether I email from the DTS package or from the SP using xp_sendmail is not important to me, but if anyone has some insight on which would be better and why, I’m open for suggestions.
THANKS FOR ANY AND ALL ASSISTANCE!!
Kev
-
Sorry, no real answer from me, but http://www.sqldts.com is a very oftern referenced site for DTS-related questions. Maybe you find something there.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you for your reply and suggestion, Frank... I did post this the DTS section of this forum. For anyone interested, there was small amount of discussion there.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=146543
Thanks again
Kevin
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply