Error after Installing DTS Designer Components

DTS. Sometimes it is hard to let go. I still use DTS early and often. I even migrate some DTS jobs to run on SQL Server 2005 servers. I know, I know. I should invest the time in converting them to SSIS and I keep putting it off. I will get to it some day.

In the meantime I ran across the below error today after installing the 2000 DTS Designer components.

The procedure entry point ?ProcessExecute@@YAXPAUHWND__@@PBG1@Z could not be located in the dynamic link library SEMSFC.dll.

The Designer Components are apart of the 2005 Feature Pack located here. I had the following tools installed. SQL Server 2000 Client tools (Enterprise Manager, Query Analyzer, Profiler, etc), SQL Server 2005 Client tools (SSMS, Books Online, SSIS Design Stuido, etc.). I had patched the SQL Server 2000 client tools to SP4. I went to install the DTS Designer Component. The install was successful no errors. I went to fire up Enterprise Manager and then started receiving the error.

I promptly rebooted to see if that would fix the problem. No dice. So I did a google search and stumbled on a installation path that would prevent the error from popping up. I didn’t really want to reinstall everything so I just re-applied SP4 for SQL Server 2000 and boo-yeah, no error. Working good.

SQL Job Tracker

I am not big on buying off the shelf software to monitor SQL for two main reasons:

  1. Not much flexibility.
  2. You can learn much more by developing scripts, websites, and such yourself.

Now I understand that in some circumstances it is nice to buy off the shelf products. It is nice having a piece of software that someone else can support. Also buying off the shelf software is necessary at times due to time constraints and project loads.

One custom process I have running is a SQL Job Checker.  The job checker will report on all the SQL Jobs that have failed. Even if you only have a handful of servers it is nice to have a single place to monitor all your jobs across multiple servers.

Now I am still old skool when it comes to DTS vs SSIS. DTS is simple and works. I am sure SSIS would get the job done just as well if not better than DTS. I am slow to make the jump to SSIS.  I created a DTS package on server that points to all the other servers. I created the following table:

CREATE TABLE [dbo].[SQLJobTracker](
[servername] [varchar](20) NULL,
[jobname] [varchar](75) NULL,
[message] [varchar](200) NULL,
[rundate] [datetime] NULL,
[stepid] [int] NULL,
[capturedate] [datetime] NULL CONSTRAINT [DF_SQLJobTracker_capturedate] DEFAULT (getdate())
) ON [PRIMARY]

In order to keep the job tracker table clean, I run a delete command in the DTS package as the first step to delete the old stuff  from the table:

delete from SQLJOBTracker where capturedate < Getdate() – 2

Then on each source in the DTS package I use the following query to extract the data and dump it into the table above:

select @@SERVERNAME ServerName,sj.name, sjh.message,
cast(dateadd(ss,
cast(substring(cast(run_duration + 1000000 as char(7)),6,2) as int),
— Add Run Duration Minutes
dateadd(mi,
cast(substring(cast(run_duration + 1000000 as char(7)),4,2) as int),
— Add Run Duration Hours
dateadd(hh,
cast(substring(cast(run_duration + 1000000 as char(7)),2,2) as int),
— Add Start Time Seconds
dateadd(ss,
cast(substring(cast(run_time + 1000000 as char(7)),6,2) as int),
— Add Start Time Minutes
dateadd(mi,
cast(substring(cast(run_time + 1000000 as char(7)),4,2) as int),
— Add Start Time Hours
dateadd(hh,
cast(substring(cast(run_time + 1000000 as char(7)),2,2) as int),
convert(datetime,cast (run_date as char(8))))
))))) as char(19)) rundate,sjh.step_id
from sysjobhistory sjh, sysjobs sj
where sj.job_id = sjh.job_id
and sjh.run_status = 0
and convert(datetime,cast (run_date as char(8))) > Getdate() – 2

I like to keep 2 days worth of logs in the table to show jobs failing one day but then success the next. Just how my mind works. Now one of the draw backs on DTS is the lack of control about what steps happen in what order, step precedence. SSIS does a much better job with step precedence. This limitation was glaring in DTS when trying to make the  generate report step the last step after all the extracts. So as a workaround I just created another DTS package to extract some data into a CSV, and then send me the report in an email:

select * from sqljobtracker where order by rundate

So the end result is a daily email with a list of the jobs that failed. Each day you get a quick snapshot of the problem jobs. I am sure there are more elegant ways to make this happen, but this method is about 10 minutes of work and works out pretty well.