How to stop a package

Hi,

In one of my packages, I have a script component to do a transformation and I am inserting the output to table. Within that script component, if the data does not match my requirement, I have to stop the job. For that, I am using 'componentmetadata.fireError' to raise an error. The problem is, now even when an error is raised, the job completes by loading the remaining records and then aborts. But I want to abort the job as soon it raises an error. How to do this?

Thanks.

Errors are not the way to do it.

Use OnQueryCancel. Books online should document it.

K

|||

HI, sorry to jump in. But I looked in books online and did not found a way to stop the package in the dataflow using the system cancel variable. Is there a way to raise an event in a script component?

Thank you,
Ccote

|||

Hi,

Anybody found a way to stop the package from script component?

Thanks.

|||

The only way I figured out to achieve this is to add a column on the output of the script component (e.g. bln_Continue, a boolean value). Before the error is triggered with ComponentMetadata.FireError, I assign this column to false. So when SSIS exits the script component, I use a conditional split transform that stop the pipeline if the value of the boolean column is false.

In my case, I use it for a logging purpose, so I will have only a fewlines in the pipeline. But in a case that there would many more lines, I would use a variable and initialize it in the post_execute. Then, using a combination of a derived column and conditional split transforms, I would still be able to stop the pipeline.

There may be a way to do it better but it is OK for me right now.

Ccote

|||Thx Ccote. That is a good option. I will try it.|||

Hi,

I am not sure how can I use the conditional split to stop my package. In my package I am loading data to a table and the source may have duplicate keys. So what I am doing, I am redirecting the error rows from destination to a script component and inside that, I am checking the error code. If it is corresponding to index key violation, I am just ignoring it. Otherwise I am raising an error. But how can I stop my package there. Now what is happening is, after processing an error record, it raises an error and reads the next record. If that also an error record, raises an error and it goes like that till the end of the input. But I want to stop the package as soon as the first error is raised. How can I do that?

Thanks.

|||

HI, for sure it will step to the other row as soon as the error is raised. The only thing that I could do for it is to prevent that other rows are processed. They will be read but not processed.

I added another output to the scriot component and called it ErrorOutput. I set its synchronous ID and exclusion group to their counterpart on the regular output (let's say output0). Then, in the code as soon as an error is trigerred, I set the script variable to true and redirect the row to ErrorOutput:

dim ErrorVar as Boolean = False

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Error = <your error code> Then
ErrorVar = True
End If

If ErrorVar Then
Row.DirectRowToErrorOutput
Else
Row.DirectRowToOutput0
End If
End Sub

HTH,
Ccote