Solved

DTS Monitoring

Posted on 2006-11-10
19
654 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:rbradberry
  • 7
  • 4
  • 4
  • +2
19 Comments
 
LVL 11

Expert Comment

by:rw3admin
ID: 17917634
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
 
LVL 3

Author Comment

by:rbradberry
ID: 17918115
so there would be no way of creating a percentage completed?  or time left?
0
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17918800
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17924673
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
 
LVL 11

Expert Comment

by:rw3admin
ID: 17926554
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
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17926568
sysdtspackages and sysdtspackagelog are both in the msdb database
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17928821
Thankyou Hillwaaa
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17930310
nmcdermaid and Hillwaaa  Thanks for pointing that out,
0
 
LVL 3

Author Comment

by:rbradberry
ID: 17930491
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Expert Comment

by:rw3admin
ID: 17930754
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
 
LVL 3

Author Comment

by:rbradberry
ID: 17931355
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 17935276
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17935315
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17935327
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17936746
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17942789
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
 
LVL 3

Author Comment

by:rbradberry
ID: 17942815
thanks, both of you were of great help.  i think i will reference the DTS Event Handler though.  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17943555
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17944872
No worries. I concur - I learn at least as much from this site as I give!!!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now