?
Solved

DTS Monitoring

Posted on 2006-11-10
19
Medium Priority
?
677 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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
 
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 1500 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

771 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