Invalid class string when running DTS pkg as scheduled job

Question

Invalid class string when running DTS pkg as scheduled job

Rance Richardson

Old Hand

Points: 377
More actions
October 11, 2004 at 9:36 am

#59622

I have several DTS packages that basically go out to a DB2 database and bring back records which I store in my SQL Server database. The packages run fine when I manually execute the packages, but when I try to run them as scheduled jobs, all of them always fail. The error message looks like this:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Drop table myTempTable Step DTSRun OnFinish: Drop table myTempTable Step DTSRun OnStart: Create Table myTempTable Step DTSRun OnFinish: Create Table myTempTable Step DTSRun OnStart: Copy Data from Results to myTempTable Step DTSRun OnError: Copy Data from Results to myTempTable Step, Error = -2147221005 (800401F3) Error string: Invalid class string Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221005 (800401F3); Provider Error: 0 (0) Error string: Invalid class string Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 DTSRun OnFinish: Copy Data from Results to myTempTable Step DTSRun: Package execution complete. Process Exit Code 1. The step failed.
I've looked at MS KB articles on this, but none seem to be pertinent to my particular situation.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 1

I have had this error message when attempting to run a dts package connecting to Oracle when the package was designed on a computer with the Oracle drivers but I was attempting to run it from a computer that did not have the Oracle drivers. Could you be running the job from a different computer than the one that developed the package?

Also, I have just gotten through dealing with a similar job problem (ultimately caused by Oracle permissions changes between versions) which was only resolved by creating a .bat file that used DTSrun and then scheduling the .bat file in Windows AT Scheduler instead of using the SQL Server Jobs Scheduling. Perhaps that might help you? The syntax is

DTSRUN /S "<servername>" /E /N "<packageName>"

Where <servername> is your SQL Server, and <packageName> is the name of your DTS package. Just put this in a text file and save it as a .bat, then run the .bat from either [Start]/[Run] or the command prompt. If it works, then schedule it from the Windows Control Panel (Add Scheduled Task).

Good luck,

Metra

Rance Richardson Old Hand Points: 377 More actions · Answer 2

I don't think that drivers are the issue in my case. The jobs run fine when I execute them by selecting the DTS package in EM and selecting Execute package. It's only when I attempt to schedule them. I've tried both scheduling the package directly in Data Transformation Services and by creating a scheduled job and using the DTSRUN command (as shown in previous reply). BTW, I didn't mention previously that these jobs are saved as Local Packages on the server.

Thanks for any help.

Michael Girdler Right there with Babe Points: 780 More actions · Answer 3

Are you trying to run this as a local package on the server or from another location?

Michael

Rance Richardson Old Hand Points: 377 More actions · Answer 4

These DTS packages were saved as local packages on our remotely located SQL Server. (I am assuming that local package means that the package is saved on the SQL Server I am connecting to via EM, and not that it is actually saved on my machine. Is that a correct assumption?)

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 5

What I am saying is to bypass the EM and go to DOS. There, use the DTSrun command. Does that work?

I have had to abandon all scheduled jobs that use non-SQL Server data and go to DOS and create the schedule in WINDOWS and not in SQL Server.

look2jesus SSC Enthusiast Points: 101 More actions · Answer 6

Metra, I am having a similar problem. I used the DTS run script from the command prompt it executed my package, and it gave me the same "Invalid class string" error as I had before. Now I did not save a .bat and schedule, but for a once-off run this should have worked, right ?

Thanks,

Ken

user4301 SSC-Addicted Points: 438 More actions · Answer 7

I'm having the same problem with an import transformation written on a SQL2K machine. It uses a lot of ActiveX transformations.

The whole package has to be run on a SQL7 machine. I thought it was a library problem but am very interested in alternatives to the "usual suspects"

I've tried moving package from SQL2K to SQL7 but it just fails.

Ray Migliaccio SSC Journeyman Points: 90 More actions · Answer 8

I was experiencing the same problem. DTS scheduled job failed, but runs fine from the EM. I was copying data from an Oracle database. Once my tnsnames file was correct at the sql server that owned the database I created a DTS package and set up a schedule. It ran fine! No domain service account was required. Which was proved in the test as well. Basicly create the DTS package at the server which it will be scheduled to run from and there should be no issues.