Determine Previous Task Executed

I have the failed path of many tasks pointing to a single Script Task that logs a failed entry into my database. In this script task, I need to be able to determine the task that faliled as I need to write a unique error message depending on the task that failed.

In my VB script, how would I determine which task was the one that failed?

Thank You!!!

a SSIS newbie

There is no way I can see to do that.

Have you investigated the logging available in SSIS before you write your own?

If you do want a task for additional error handling work, then try the on error event handler. Event handlers can host an entire workflow of tasks if you want, and are a better way of capturing this type of event compared to workflow constraints. You have some system variables scoped to the event handler that should give you all the information you want.

|||What I do is add a derived column to each error flow. Then in that derived column, I add a new column, ErrorReason (or whatever you like), which is where I store a small string of what the error was. So then in your script component, you could look at that column and act accordingly.

However, it sounds like you can do all of this outside of a script component. (You are inside the data flow, correct? I assume so because you talk of "failed paths.") If you're talking about a failed precedence constraint, then I'll defer to Darren above.|||

Hi AspUser123,

I agree with Darren.

Recently, I implemented a custom error logging mechanism for a client using the onError Event Handler. They wanted the package to continue executing, but capture information about each fault.

Since the Error events bubble, I implemented the entire custom logging solution in the Package onError Event Handler. You can use the SourceName system variable to identify the name of the task that originally generated the error - even if it's nested inside several layers of containers. You can also collect information such as the ErrorDescription and ErrorCode (error number).

In this case I ended up writing the information out to a SQL Server database for storage, reporting, and analysis - but you could easily implement a flat file output. You can use an Execute SQL Task (calling a stored procedure) to write to SQL Server, or add a Data Flow to the onError Event Handler to store the system variable values to a flat file destination.

Hope this helps,
Andy

|||I agree with Darren.

Detect changes on indexes in a published table

Hi,

Regarding to my previous post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=553652&SiteID=1, just wondering if there's a way to detect any changes which is made on the existing indexes in a published table so that the device can determine whether reinitialization of subscrption is needed before every synchronization.

Cheers,
Justin

One thing you may be able to use is DDL trigger. DDL trigger are fired in response to a variety of DDL statements, including create/alter/drop index. Just look for DDL trigger in BOL for more information.

Peng

|||

Yes, you may use special tools for compare structure changes

link http://www.red-gate.com/products/index.htm

sql

designing data entry form with scope_identity .....help

hi i'm thinking of putting 3 texboxes for the user to enter their previous work exp which look something like this:

experience
First job.textbox
Secondjob.textbox
Thirdjob.textbox

i'm kinda confuse on with the database. The 3 tables required means i will hav 3 sqldatasource ...
which i didin't quite get it right with the Secondjob table.I try using identity_scope() and @.@.identity to get the ID from my first table(personel)
which is autogenerate number. It works fine with Firstjob table but not secondjob table.

Generated error message:

(1 row(s) affected)

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 6

The INSERT statement conflicted with the FOREIGN KEY constraint "Personnel_Secondjob". The conflict occurred in database "test", table "dbo.Personnel", column 'IDPersonnel'.

The statement has been terminated.

INSERTINTO dbo.Personnel

(Name)

VALUES

('Jon')

insert

into Firstjob(Designation, Employer, YearWorking, IDPersonnel)

values

('Teachier,'Gam','7thn',scope_identity())

insert

into Secondjob(Designation, Employer, YearWorking, IDPersonnel)

values

('Musician','KKSB','8thn',scope_identity())

any suggestions...

Declare @.id1int, @.id 2intINSERT INTO dbo.Personnel (Name)VALUES ('Jon')SELECT @.ID1 = SCOPE_IDENTITY()insert into Firstjob(Designation, Employer, YearWorking, IDPersonnel)values ('Teachier', 'Gam', '7thn', @.ID1) SELECT @.id2 = SCOPE_IDENTITY() insert into Secondjob(Designation, Employer, YearWorking, IDPersonnel) values ('Musician', 'KKSB', '8thn', @.ID2)

|||

it generate an error:

Msg 547, Level 16, State 0, Line 9

The INSERT statement conflicted with the FOREIGN KEY constraint "Personnel_PnglmnKerja2". The conflict occurred in database "test", table "dbo.Personnel", column 'IDPersonnel'.

The statement has been terminated.

i changed line 5 to
SELECT @.id2= @.ID1

or reduce the code to

SELECT @.ID1 = SCOPE_IDENTITY()
insert into Firstjob(Designation, Employer, YearWorking, IDPersonnel)values ('Teachier', 'Gam', '7thn', @.ID1)
insert into Secondjob(Designation, Employer, YearWorking, IDPersonnel) values ('Musician', 'KKSB', '8thn', @.ID1)

and it worked... thanx for the idea i'm just beginner,learning mssql through forums & ebook. I really appreciate it

do u foresee any problem if many people try to insert their data concurently?10-20 people at the same time