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.
-
I'd attack this from a different angle. One DTS package that loops through gathering the data and emailing the file for each user.
One of the first tasks in the DTS package would be to obtain a list of employees. Then subsequent tasks create the excel file and email it out. At the time the email is sent, you'd make a determination on whether the manager email goes out as well.
Take a look at this article which shows how to loop through a Global Variable recordset,
http://www.sqldts.com/default.aspx?298
--------------------
Colt 45 - the original point and click interface
-
Thank you for your reply, Phil
This sounds reasonable... The real issue, however, is the hundred or so queries stored in a table that need to run individually and whose results each need to be transformed for .xls output. (the email issue will be tackled at a later time).
But, if I understand your thought processes, you're saying I could:
1. create a pkg containing a sql statement like "SELECT myQuery FROM myQryTable WHERE qryID = ?"
2. add an ActiveX script that loops via "x <= count(qryID)... x++" passing "x" as gvQryID, and runs each query
3. add a transform task to send (append) the results of each to my .xls file?
or have I completely confused the matter?
Thanks,
Kevin
-
Sounds about right.
Let me know how you get on.
--------------------
Colt 45 - the original point and click interface
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply