DTS Monitoring

I have a web application that users upload a MS Access DB to.  Once the DB is uploaded I run a stored procedure to run a DTS Package that first deletes the tables in the SQL Server db then puts the tables from the MDB into the SQL Server DB.  What i want to do is track the percentage of completion via a web application using AJAX so the user isn't left in the dark for 5 minutes.  Any ideas?

Thanks,
Russ
LVL 3
rbradberryAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
You have a number of challenges here.  First DTS is not really designed for interactive use, hence not much enfasis on any type of progress.  Secondly ASP is not a good tool for long waits and especially not for for handling events.  And yes, I am familar with AJAX.

So we are down to pretty much your only avenue if you insist on going down this road (which I do not recommend) and that is making use of DTS's ProgressRowCount property.  Here is an example of how it works using Visual Basic (which has an event handler):

How to install Data Transformation Services (DTS) Event Handlers in Visual Basic
http://support.microsoft.com/?kbid=221193
0
 
rw3adminCommented:
I dont think you can look inside a DTS package through SQL, what you can do is create a table that hold your status and use Ajax to loop through this table every 10 seconds to see where the steps are
create table
tStatus ( DTSStep_ID id , DTSStep_Name Varchar(1000), ChangeDate DateTime)

Truncate this table in the very first step of DTS
now for every step in DTS create a SQL task that will just insert in tStatus table something like
DTSStep_ID=1, DTSStepName='Starting Access Tables import in SQL', changedate=getdate()
DTSStep_ID=2, DTSStepName='Finished Access Tables import in SQL', changedate=getdate()
DTSStep_ID=3, DTSStepName='Starting SQL table Truncates', changedate=getdate()
DTSStep_ID=4, DTSStepName='Finished with SQL table Truncates', changedate=getdate()
...
...
...
...
and so on.....


0
 
rbradberryAuthor Commented:
so there would be no way of creating a percentage completed?  or time left?
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
HillwaaaCommented:
Hi rbradberry,

Following on from rw3admin's idea - you could implement a percentage completed by creating a table at the start of the dts job, then adding a row (or updating one) for each table that is completed.  This would give your end users a rough idea of how much is completed - without slowing down the job too much.

Alternately, you could to a sum of the number of rows from each table in the Access database before the DTS job starts, to find the total rows to be copied, then do a sum of the count from each table in the target SQL database from your Ajax loop to find out how much has been completed so far.  However, I'm not sure on the performance of this approach.

Cheers!
0
 
nmcdermaidCommented:
The logs for DTS are found in MSDB

I think the table is called SYSDTSPACKAGELOGS or something like that. You'll see it.


This table only measures each task that is completed. If you have a large number of tasks then this log table will allow you to have a useful progress indicator.

If you only have one or two tasks then there will only be one or two steps in your progress indicator... therefore it won't be very useful.
0
 
rw3adminCommented:
Hi nmcdermaid,
>>I think the table is called SYSDTSPACKAGELOGS or something like that. You'll see it.<<

I dont think there is any such table in MSSQL either version 2000 or 2005
0
 
HillwaaaCommented:
sysdtspackages and sysdtspackagelog are both in the msdb database
0
 
nmcdermaidCommented:
Thankyou Hillwaaa
0
 
rw3adminCommented:
nmcdermaid and Hillwaaa  Thanks for pointing that out,
0
 
rbradberryAuthor Commented:
well i have 6 tasks, so maybe if i state the tasks and have the progress bar update 16.67% each time, it can provide some sort of indication.  I think that querying the db to count rows while it is doing a DT would not end up well.
0
 
rw3adminCommented:
I dont understand this line...why do you think it will not end up well?
>>I think that querying the db to count rows while it is doing a DT would not end up well.<<
rw3admin
0
 
rbradberryAuthor Commented:
because when the DT is taking place it usually uses about 60% of my processor to complete it.  This lasts around 5 Minutes as the MDB is around 50MB.  If at the same time ever couple seconds I am counting rows I think it would end up bieng too much of a tax on the SQL server.  Keep in mind I have about 15 other sites using this SQL server.
0
 
nmcdermaidCommented:
No it shouldn't be a problem at all. Selecting from the table is easy. If you requery the data every fifteen seconds that should be no issue at all.

The tricky part is getting the query right, its quite a long winded trail to actually extract the current log for the current version of the current package.

0
 
nmcdermaidCommented:
Here's the SQL I use to get the latest log out, given a package name.

SELECT TOP 100 PERCENT
[PackageOrder], [PackageName], [Step], [DT_Start], [DTEnd], [HMS], [errorcode],
REPLACE([ErrorDescription],CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10),CHAR(13) + CHAR(10)) AS ErrorDescription

FROM

(
SELECT
0 As PackageOrder,
PV.[name] AS PackageName,
LEFT(SL.stepname,25) As Step,
SL.starttime As DT_Start,
SL.endtime As DTEnd,
dbo.f_ConvertHMS(SL.elapsedtime) As HMS,
SL.errorcode,
SL.errordescription As ErrorDescription
FROM [msdb].[dbo].[sysdtssteplog] SL
INNER JOIN
      (
      -- Get the latest version of the log for this package
      SELECT TOP 1 lineagefull, [name]
      FROM msdb.dbo.SYSDTSPACKAGELOG
      WHERE VersionID = (
            -- Get the latest version of the package that also has log entries
            SELECT TOP 1
            P.versionid
            FROM
            msdb.dbo.SYSDTSPACKAGES P
            INNER JOIN
            msdb.dbo.SYSDTSPACKAGELOG L
            ON P.VersionID = L.VersionID
            WHERE P.[name] = 'PUT YOUR PACKAGE NAME HERE'
            ORDER BY CREATEDATE DESC
            )
      ORDER BY starttime DESC
      ) PV
ON SL.lineagefull = PV.lineagefull
0
 
nmcdermaidCommented:
oops it includes this function:

dbo.f_ConvertHMS

which you most likely don't have so try this... it should give you the count of completed steps:




SELECT COUNT(*)
FROM [msdb].[dbo].[sysdtssteplog] SL
INNER JOIN
     (
     -- Get the latest version of the log for this package
     SELECT TOP 1 lineagefull, [name]
     FROM msdb.dbo.SYSDTSPACKAGELOG
     WHERE VersionID = (
          -- Get the latest version of the package that also has log entries
          SELECT TOP 1
          P.versionid
          FROM
          msdb.dbo.SYSDTSPACKAGES P
          INNER JOIN
          msdb.dbo.SYSDTSPACKAGELOG L
          ON P.VersionID = L.VersionID
          WHERE P.[name] = 'PUT YOUR PACKAGE NAME HERE'
          ORDER BY CREATEDATE DESC
          )
     ORDER BY starttime DESC
     ) PV
ON SL.lineagefull = PV.lineagefull
0
 
nmcdermaidCommented:
To save any confusion acperkins, I was addressing rbradberry's comment rather than yours(!) - didn't see your comment. All your points are valid.
0
 
rbradberryAuthor Commented:
thanks, both of you were of great help.  i think i will reference the DTS Event Handler though.  
0
 
Anthony PerkinsCommented:
nmcdermaid,

>>To save any confusion acperkins, I was addressing rbradberry's comment rather than yours<<
I realized and it is no problem.  I always learn something from your comments.  Did not mean to steal your thunder.
0
 
nmcdermaidCommented:
No worries. I concur - I learn at least as much from this site as I give!!!
0
All Courses

From novice to tech pro — start learning today.