Error when running DTS package as job

I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.

When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:

Executed as user: LOMMELDOM\Administrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.

The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.

Any clues?

You are using a custom class / transform / task that is not registered on the server. You will need to register it through the GAC....|||But how come it works perfectly on the same server when I just execute the package? And on the same server it fails if job agent does it?|||

You are executing it on YOUR machine, even if it is hosted on the server.

Edit: Unless if you are running it from the job by right clicking and hitting execute job starting at step...

|||

Hi Eric,

Thanks for the reply.

Euh, my machine = server so there is no other machine around.

It fails when sheduled but it also fails when I right click the job and say execute. It only works if I right click the package and execute that! Further there are no other steps in the job and the job was created by right clicking on the package and sheduling it.

|||Looking a bit more closely at your error messages, are you running SSIS (SQL 2005)? It kind of looks like you are still using SQL 2000 and DTS...|||Yes I'm using SQL2000 with DTS, that's why I also mentionned this in the title of my question. I didn't find a DTS specific newsgroup.|||Try this one: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

Error when running DTS package as job

I have a DTS package which deletes some rows in a table and then inserts new ones from a Dbase file.

When I execute the package it works perfectly, but when I shedule it as a job it gives this error message:

Executed as user: LOMMELDOM\Administrator. ...DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: Copy Data from GRES to [ZNA].[dbo].[GRES] Step DTSRun OnError: Copy Data from GRES to [ZNA].[dbo].[GRES] Step, Error = -2147221164 (80040154) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error: -2147221164 (80040154); Provider Error: 0 (0) Error string: Class not registered Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4700 Error: -2147221164 (... Process Exit Code 5. The step failed.

The deleting of the rows works but the import from the Dbase doens't, I thought it was a security issue. So I made sure the JobAgent has all the rights it needs to access the directory of the Dbase file.

Any clues?

You are using a custom class / transform / task that is not registered on the server. You will need to register it through the GAC....|||But how come it works perfectly on the same server when I just execute the package? And on the same server it fails if job agent does it?|||

You are executing it on YOUR machine, even if it is hosted on the server.

Edit: Unless if you are running it from the job by right clicking and hitting execute job starting at step...

|||

Hi Eric,

Thanks for the reply.

Euh, my machine = server so there is no other machine around.

It fails when sheduled but it also fails when I right click the job and say execute. It only works if I right click the package and execute that! Further there are no other steps in the job and the job was created by right clicking on the package and sheduling it.

|||Looking a bit more closely at your error messages, are you running SSIS (SQL 2005)? It kind of looks like you are still using SQL 2000 and DTS...|||Yes I'm using SQL2000 with DTS, that's why I also mentionned this in the title of my question. I didn't find a DTS specific newsgroup.|||Try this one: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

error when inserting same data into database

hello,
im having a problem with my sql statement. i have an insert statement
that inserts new "comments" into my database, but every so often, the
data that needs to be inserted is the same "comment" that is already in
the database. this produces an error. i want the statement to add a
new comment if it's not there, and to override or skip over the
comments that already exist in the database. here's my code:
'Insert New Comments Into Comments
strSql = "INSERT INTO Comment ( [Order], Comment, CommentDate,
Commentator ) " & _
"SELECT [Order Log].ID AS [Order], Iris_Comments_Temp.Comment,
Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
"FROM Iris_Comments_Temp INNER JOIN [Order Log] ON
Iris_Comments_Temp.[TRACKER NO] = [Order Log].[TRACKER NO];"
objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic
If Err < 0 Then
MsgBox "Insert Comments: " & Err.DESCRIPTION
Err.Clear
End If
'Insert Comments Into Comments_History
strSql = "INSERT INTO Iris_Comments_History ( [Order], Comment,
CommentDate, Commentator ) " & _
"SELECT [Order Log].ID, Iris_Comments_Temp.Comment,
Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
"FROM [Order Log] INNER JOIN Iris_Comments_Temp ON [Order
Log].[TRACKER NO]=Iris_Comments_Temp.[TRACKER NO];"
objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic
If Err < 0 Then
MsgBox "Insert Comments History: " & Err.DESCRIPTION
Err.Clear
End If
any help would be outstanding!
thanks!!!
rickWhat is the error you are getting? The most likely suspect is that you are
inserting duplicate values into a column with a primary key or unique index
defined. Logically, it would seem that [Order], [CommentDate] would be the
primary key, but it's a crazy world and the database developer may have
placed the key on [Comment].
<RSummersJr@.gmail.com> wrote in message
news:1135025948.470130.281510@.o13g2000cwo.googlegroups.com...
> hello,
> im having a problem with my sql statement. i have an insert statement
> that inserts new "comments" into my database, but every so often, the
> data that needs to be inserted is the same "comment" that is already in
> the database. this produces an error. i want the statement to add a
> new comment if it's not there, and to override or skip over the
> comments that already exist in the database. here's my code:
> 'Insert New Comments Into Comments
> strSql = "INSERT INTO Comment ( [Order], Comment, CommentDate,
> Commentator ) " & _
> "SELECT [Order Log].ID AS [Order], Iris_Comments_Temp.Comment,
> Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
> "FROM Iris_Comments_Temp INNER JOIN [Order Log] ON
> Iris_Comments_Temp.[TRACKER NO] = [Order Log].[TRACKER NO];"
> objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
> adLockOptimistic
> If Err < 0 Then
> MsgBox "Insert Comments: " & Err.DESCRIPTION
> Err.Clear
> End If
> 'Insert Comments Into Comments_History
> strSql = "INSERT INTO Iris_Comments_History ( [Order], Comment,
> CommentDate, Commentator ) " & _
> "SELECT [Order Log].ID, Iris_Comments_Temp.Comment,
> Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
> "FROM [Order Log] INNER JOIN Iris_Comments_Temp ON [Order
> Log].[TRACKER NO]=Iris_Comments_Temp.[TRACKER NO];"
> objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
> adLockOptimistic
> If Err < 0 Then
> MsgBox "Insert Comments History: " & Err.DESCRIPTION
> Err.Clear
> End If
>
> any help would be outstanding!
> thanks!!!
> rick
>|||well...what i think is happening is that the person is trying to
re-save the same comments, and then the insert statement produces an
error because there are two of the same comments in the database...the
error is:
Insert Comments History: The changes you requested to the table were
not successful because they would create duplicate values in the index,
primary key, or relationship. Change the data in the field or fields
that contain duplicate data, remove the index, or redefine the index to
permit duplicate entries and try again.
I want the comment to override the existing comment instead of adding
the same comment again. any help would be great...thanks!
rick|||So you want your INSERT to become an UPDATE, right?
You need to prevent an insert if the key already exists and instead update
the values. Please post DDL and sample data to get better help.
ML
http://milambda.blogspot.com/|||Ask whomover designed the database what primary keys, unique indexes or
constraints are defined on the table. Without that information, it's hard
for a developer to code against it.
<RSummersJr@.gmail.com> wrote in message
news:1135030402.665994.163870@.g47g2000cwa.googlegroups.com...
> well...what i think is happening is that the person is trying to
> re-save the same comments, and then the insert statement produces an
> error because there are two of the same comments in the database...the
> error is:
> Insert Comments History: The changes you requested to the table were
> not successful because they would create duplicate values in the index,
> primary key, or relationship. Change the data in the field or fields
> that contain duplicate data, remove the index, or redefine the index to
> permit duplicate entries and try again.
> I want the comment to override the existing comment instead of adding
> the same comment again. any help would be great...thanks!
> rick
>