DTS Error Message

[dom24]

Can anyone tell me how I can get a DTS to show my own error message rather than the default one?
What is fails I want it to tell the 'non IT literate' user what the problem was.

Also, my DTS is inserting records from one table to another.
Can I get it to write the records which are not successfully inserted due to constraints to a new table?
Basically course codes (and some other details) are being passed from one table to another. There is a constraint on the original table where the course code must exist in the course table also. If it doesn't, the DTS shows an error.
Instead of showing this error I want it to copy the record to a new table and carry on with inserting the other records.

Thanks.

 

[yksvaan]

Tough ones, but good questions.

About errors, the way you put it, I don't remember what would be the method if any exists. Where do you want the messages to appear? What do you want to notify end-users about? But of course there are various ways notifying from DTS with email, windows event log, your own log textfiles.

Inserting. An approach where rows are inserted in an application even if they violate primary key constraint, is not acceptable to me. I think this can be done somahow, but I'm not that interested in this.

In DTS (SQL2k) you have Data Driven Query Task, where you can check the existence and decide not to insert. It is not the easiest little feature to use, but with it you could "redirect" conflicting rows to a lets say problem_rows table, which you can dump out to a log textfile or something.

One other approach is to move the data to a help table and insert from there, when the checking can be done with sql.

In datawarehouse loading I've also used a different approach, but I won't go into that now.

Cheers

 

[dom24]

With regards to errors, all I want is a message box to appear with something like "Update has failed" rather than "Line 2 error.dts blah blah......."

For the insert:
I've had a go at the Data Driven Query Task but I can't work it out. What I want to do is select everything from the coursecomplete table, insert the rows into the course table where COURSECOMPLETE.COURSECODE IS IN COURSE.COURSECODE , and insert the rows (what are left) into WrongCourseCode table where COURSECOMPLETE.COURSECODE IS NOT IN COURSE.COURSECODE. Help!

 

[yksvaan]

Errors: how do users start the packages, are they running them dts designer within Enterprise manager ?

Insert:
Two inserts and in this order and in one transaction, though it is doesn't have to be necessary in this case:

BEGIN TRANSACTION -- not essential to the logic
-- First the WrongCourseCode !
INSERT INTO WrongCourseCode (COURSECODE -- possible other column names separated with commas
)
SELECT COURSECODE -- possible ...
FROM COURSECOMPLETE
WHERE NOT EXISTS -- !! NOT !!
(SELECT * FROM COURSE
WHERE COURSE.COURSECODE=COURSECOMPLETE.COURSECODE)
-- Second the correct courses
INSERT INTO COURSE
SELECT COURSECODE -- possible ...
FROM COURSECOMPLETE
WHERE EXISTS -- !! without NOT !!
(SELECT * FROM COURSE
WHERE COURSE.COURSECODE=COURSECOMPLETE.COURSECODE)
COMMIT TRANSACTION -- not essential to the logic

I have not tested this, but the idea is right. You can test it by leaving INSERT statements out and just look what the select produce as result. There are other ways of writing the operation and this may not be the most effective one, but most understandable to you.

Cheers

 

[dom24]

Errors: Users are running the DTS packages through Access 97. I've added a button to a form which automatically runs the package, which is why I want a message box to display if the package fails at any point.

As for the Insert, i'll try that now thanks.

 

[dom24]

Inserts:
This is the code I had originally, I'm just not sure how the rest of the DDTS task works as there are alot of other things it asks for.
I've put the CourseComplete table as the source, and the Course table as the binding table. Is that right?
I'm getting an error now anyway saying:

Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description: ActiveX Scriptiing Transform requires script Text and Language and at least one Phase function to be specified.

???

 

[yksvaan]

Errors: Does Access start dtsrun on users computers, or can Access launch dts remotely on server?

But the core is, you shouldn't be getting error message about this matter if you do handle right ..

..the inserting:
I forgot to mention, because it was for me so evident(?) that my suggestion is to use SQL Task, to do it straight in sql, and not to use the non-self-explanatory Data Driven task.

You have both of the tables in the same server and same database, am I right? (even if they are on the same server that's enough, then it's a question of referencing tables right, ask in case)

Cheers


[blue]Backup system is as good as the latest recovery[/blue]