How to read a package variable in a custom component?

What I tried, based on something I found in Jamies blog:

IDTSVariables90 vars = null;
this.VariableDispenser.GetVariables(out vars);

However the vars stays empty, while I have 3 user variables defined. I saw in the documentation that one should not use 'VariableDispenser' since it is for internal use. But is there a better way, like in a script task, you just do
Dts.Variables("BatchID") .

Youre input is appreciated,
HenkPlease note the additional comments in the BOL topic on the Variable Dispenser property of the PipelineComponent base class. (The HTML always suffers when being copied and pasted, see below.) Can you try locking the variable first?
--

Remarks

The VariableDispenser is used to read and write variables in the package that contains the component. Before reading or writing a variable, it must be locked using one of the following methods; LockForRead, LockForWrite, LockOneForRead, or LockOneForWrite. After the variables are locked using the dispenser, they are available through the IDTSVariables90 interface.

Example

The following example demonstrates how to use the VariableDispenser to lock a single variable, and multiple variables.

C# Copy Code // Lock two variables, and then retrieve them by calling GetVariables. IDTSVariables90 variables = null; VariableDispenser.LockForRead("variable1"); VariableDispenser.LockForRead("variable2"); VariableDispenser.GetVariables(ref variables); object variable1 = variables[0].Value; object variable2 = variables[1].Value; // Retrieve a single variable. IDTSVariables90 variables = null; VariableDispenser.LockOneForRead("variable1", ref variables); object variable1 = variables[0].Value;

|||Thanks Douglas!

How to raise error in script

I can't believe I can't find this in books online...

I have a transformation script component and I have a case where I want the object to completely fail if it is encountered. How do I code that? All my searching BOL could find is how to redirect rows to an error output which is not what I need in this case.... I'd think it would be something simple like Me.RaiseError("Something really bad happened") but I haven't found it ...

Chris,

A simple example from books online to raise an error in the Script Component is:

Dim myMetadata as IDTSComponentMetaData90

myMetaData = Me.ComponentMetaData

myMetaData.FireError(...)

Further details can be found on the Raising Events in the Script Component page at: http://msdn2.microsoft.com/en-us/library/aa337081.aspx.

Thanks,
Patrik

|||Thanks... I knew it it had to be in books online somewhere.. I just couldn't find it |||

One thing to note that I have found with this method is that it doesn't immediately error..

For example lets say you have a buffer (in a dataflow) containing 1000 rows. In my experience, if you code a FireError when it encounters null it will not exit the object on the first instance but continue with the whole buffer - then your dtslog will be filled with 1000 errors (if indeed each row of the 1000 had a null). Just something to keep in mind.

|||

Throw an exception to simulate the Fail Component disposition. This action will cause the transformation script component to immediately fail.

ComponentMetadata's FireError, as you noted, will not cause immediate failure, but rather, form a basis for comparison with the data flow's MaximumErrorCount (which may be useful in other cases)

|||

"Throw an exception to simulate the Fail Component disposition. This action will cause the transformation script component to immediately fail."

Thanks, Can you provide an example or a pointer to where to look in books online?

|||

From BOL, please use the following example.

Using Error Outputs in a Data Flow Component

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/a2a3e7c8-1de2-45b3-97fb-60415d3b0934.htm

First off, to establish the relevance of the above BOL link related to data flow component error outputs, recall that a script component (actually a script component host) is just another type of custom pipeline component. A ScriptComponentHost derives from PipelineComponent. PipelineComponent is the base class used when writing pipeline components. The ScriptComponentHost provides services to the auto-generated classes known as the "Script Component".

Anyway, in the BOL reference example, the FailComponent disposition is emulated by throwing an exception. Furthermore, Kirk Haselden, who I consider an authority on the subject of SSIS, states something similar in https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=601735&SiteID=1.

As far as script component example, say, for the sake of a simple example, you are adding non-negative numbers, the sum of which cannot exceeed 42. At some point, the sum exceeds 42. You can keep adding and calling Dts.FireError() repeatedly, indicating the sum is logically impossible in your event message. Instead, to immediately fail the component, the solution is to call Dts.FireError() once with the appropriate error message, and then throw an exception to fail the component.

|||

Thanks jaegd for the above.

I have followed your advice of the FireError() then 'throw new exception'

However if one has an OnError event (as I do) to send an email of the error description, then no less 4 messages are sent:

1. The FireError error

2. A result of the exception -"System.Exception"

3 A result of the exception "The ProcessInput method on component: " (something to do with a lookup transform up-line)

4.Another result of the exception "Thread "WorkThread0" has exited with error code 0x"

So how does one allow the FireError OnError to execute but suppress the other spurious events?

Thanks

|||Put a test in front of your email task to decide what gets sent. Then the recipient receives a single email from an error event cascade.

For one, suppress the errors 3 ("The process InputMethod on component"), and 4 ("Thread "WorkThread0" has exited...) via an expression based precedence constraint to your Send Mail task. The specific Error Codes are accessible in the handler upon which a suppression decision can be made.

That leaves you with two errors, which are both more relevant to why the data flow was halted. You can dispense with with FireError call, and just use the exception, leaving a single email.|||

Thanks

Of course this is only usefull if you know in advance what the extraneous error messages are going to be in all cases. So I don't consider that to be an elegant solution. If I knew why multiple error messages where generated for only the one error then I might have a chance at obtaining a better solution. Also access to a system variable such as ErrorCount would be usefull too. Nevermind, I'll attack this in a similar manner to what you have suggested (instead of the message I'll go off the error code).

How to raise error in script

I can't believe I can't find this in books online...

I have a transformation script component and I have a case where I want the object to completely fail if it is encountered. How do I code that? All my searching BOL could find is how to redirect rows to an error output which is not what I need in this case.... I'd think it would be something simple like Me.RaiseError("Something really bad happened") but I haven't found it ...

Chris,

A simple example from books online to raise an error in the Script Component is:

Dim myMetadata as IDTSComponentMetaData90

myMetaData = Me.ComponentMetaData

myMetaData.FireError(...)

Further details can be found on the Raising Events in the Script Component page at: http://msdn2.microsoft.com/en-us/library/aa337081.aspx.

Thanks,
Patrik

|||Thanks... I knew it it had to be in books online somewhere.. I just couldn't find it |||

One thing to note that I have found with this method is that it doesn't immediately error..

For example lets say you have a buffer (in a dataflow) containing 1000 rows. In my experience, if you code a FireError when it encounters null it will not exit the object on the first instance but continue with the whole buffer - then your dtslog will be filled with 1000 errors (if indeed each row of the 1000 had a null). Just something to keep in mind.

|||

Throw an exception to simulate the Fail Component disposition. This action will cause the transformation script component to immediately fail.

ComponentMetadata's FireError, as you noted, will not cause immediate failure, but rather, form a basis for comparison with the data flow's MaximumErrorCount (which may be useful in other cases)

|||

"Throw an exception to simulate the Fail Component disposition. This action will cause the transformation script component to immediately fail."

Thanks, Can you provide an example or a pointer to where to look in books online?

|||

From BOL, please use the following example.

Using Error Outputs in a Data Flow Component

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/a2a3e7c8-1de2-45b3-97fb-60415d3b0934.htm

First off, to establish the relevance of the above BOL link related to data flow component error outputs, recall that a script component (actually a script component host) is just another type of custom pipeline component. A ScriptComponentHost derives from PipelineComponent. PipelineComponent is the base class used when writing pipeline components. The ScriptComponentHost provides services to the auto-generated classes known as the "Script Component".

Anyway, in the BOL reference example, the FailComponent disposition is emulated by throwing an exception. Furthermore, Kirk Haselden, who I consider an authority on the subject of SSIS, states something similar in https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=601735&SiteID=1.

As far as script component example, say, for the sake of a simple example, you are adding non-negative numbers, the sum of which cannot exceeed 42. At some point, the sum exceeds 42. You can keep adding and calling Dts.FireError() repeatedly, indicating the sum is logically impossible in your event message. Instead, to immediately fail the component, the solution is to call Dts.FireError() once with the appropriate error message, and then throw an exception to fail the component.

|||

Thanks jaegd for the above.

I have followed your advice of the FireError() then 'throw new exception'

However if one has an OnError event (as I do) to send an email of the error description, then no less 4 messages are sent:

1. The FireError error

2. A result of the exception -"System.Exception"

3 A result of the exception "The ProcessInput method on component: " (something to do with a lookup transform up-line)

4.Another result of the exception "Thread "WorkThread0" has exited with error code 0x"

So how does one allow the FireError OnError to execute but suppress the other spurious events?

Thanks

|||Put a test in front of your email task to decide what gets sent. Then the recipient receives a single email from an error event cascade.

For one, suppress the errors 3 ("The process InputMethod on component"), and 4 ("Thread "WorkThread0" has exited...) via an expression based precedence constraint to your Send Mail task. The specific Error Codes are accessible in the handler upon which a suppression decision can be made.

That leaves you with two errors, which are both more relevant to why the data flow was halted. You can dispense with with FireError call, and just use the exception, leaving a single email.|||

Thanks

Of course this is only usefull if you know in advance what the extraneous error messages are going to be in all cases. So I don't consider that to be an elegant solution. If I knew why multiple error messages where generated for only the one error then I might have a chance at obtaining a better solution. Also access to a system variable such as ErrorCount would be usefull too. Nevermind, I'll attack this in a similar manner to what you have suggested (instead of the message I'll go off the error code).