Error Redirected to Script Component : Package Doesnt fail.

I have created a data flow task. In that, in a 'data conversion', if a column fails validation then that row is redirected to a script component, which in turn writes the error to a variable.

But though the error is generated and script component receives the error, package doesnt fail.

Is there any way to set the package result to failure inside the script component? I tried set 'FailPackageOnFailure' to true but doesnt work.

Any help is greatly appreciated.

Thanks,

Don

donMahya wrote:

I have created a data flow task. In that, in a 'data conversion', if a column fails validation then that row is redirected to a script component, which in turn writes the error to a variable.

But though the error is generated and script component receives the error, package doesnt fail.

Is there any way to set the package result to failure inside the script component? I tried set 'FailPackageOnFailure' to true but doesnt work.

Any help is greatly appreciated.

Thanks,

Don

You could raise an error within the script component using:

Me.Events.FireError(...) (something like that anyway)

-Jamie

|||

Sorry for the delay. Wasnt working on this part for some time.

I tried

Me.ComponentMetaData.FireError(0, "", strErrDesc, "", 0, True) - Where strErrDesc is error decription.

but still no luck

I tried setting the maxerrorcount to 0 but No Luck. Package still completes without any failure.

Any help or ideas are greatly appreciated.

Thanks,

Don

|||

You should return the failure value inside of your script main method. I do not remember the exact enumeration value but the intellisence will give it to you.

Thanks.

Error Redirected to Script Component : Package Doesnt fail.

I have created a data flow task. In that, in a 'data conversion', if a column fails validation then that row is redirected to a script component, which in turn writes the error to a variable.

But though the error is generated and script component receives the error, package doesnt fail.

Is there any way to set the package result to failure inside the script component? I tried set 'FailPackageOnFailure' to true but doesnt work.

Any help is greatly appreciated.

Thanks,

Don

donMahya wrote:

I have created a data flow task. In that, in a 'data conversion', if a column fails validation then that row is redirected to a script component, which in turn writes the error to a variable.

But though the error is generated and script component receives the error, package doesnt fail.

Is there any way to set the package result to failure inside the script component? I tried set 'FailPackageOnFailure' to true but doesnt work.

Any help is greatly appreciated.

Thanks,

Don

You could raise an error within the script component using:

Me.Events.FireError(...) (something like that anyway)

-Jamie

|||

Sorry for the delay. Wasnt working on this part for some time.

I tried

Me.ComponentMetaData.FireError(0, "", strErrDesc, "", 0, True) - Where strErrDesc is error decription.

but still no luck

I tried setting the maxerrorcount to 0 but No Luck. Package still completes without any failure.

Any help or ideas are greatly appreciated.

Thanks,

Don

|||

You should return the failure value inside of your script main method. I do not remember the exact enumeration value but the intellisence will give it to you.

Thanks.

error output is bringing too many rows with it

I've got a Derived Columns component as part of a data flow. On this I've set the error output for my columns to Redirect Row in all cases. I've set a data watcher on the error output and then ran the package.

There are several rows which I'm expecting to fail - about 3 of them. These fail but there are also another 697 which seem to have no problem. So I fixed one of the problem columns in the source data and then re-ran the package. I only updated one row in the source so this row no longer appeared in the error output, but neither did several hundred of the other rows.

Is it possible that the error output has been tripped for that one row but for some reason it sends several hundred more rows? The ids on these additional rows follow on from the erroneous row, and when I fix that row the rows following it no longer appear in the error output.

The Derived Column transform should giuve your error handling at a row level. One row that fails should not impact the following rows. This just sounds wrong, so there is either something unexpected you've missed when looking at what rows shoudl fail, or there is a bug. Can you reproduce this behaviour on a simple set of data that could be used to log a bug? Perhaps you could send me a sample package?|||

Darren - I've managed to reproduce this with a simple package which reads data from a file. Sent to you via email.

Cheers,

Nick

|||no longer a problem once upgraded to SP2. Thanks Darren.sql

Error OutPut in Custom Source Component

For the Custome source Component ErrorOutput, should I go for asynchronous / synchronous Output.

If i go for synchronous output

// Create the error output.
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection.New();
errorOutput.IsErrorOut = true;
errorOutput.Name = "ErrorOutput";
errorOutput.SynchronousInputID = What Id is required here;
errorOutput.ExclusionGroup = 1;

Is it the IDTSOutput90 InPut.ID / OutPut.ID which should be assigned.

Thanks Regards

Anil

The SynchronousInputID is the ID for which the output is synchronous with, so this would be the input ID. If you write an asynchronous component then obviously you do not need to set it.|||

Hi Darren

Since I am developing a Source component I will not have any InPut.ID ?

|||

Correct, by their nature source components are asynchronous, in fact they have no inputs at all. There is no input buffer, so they must create a new output buffer which is really the definition on an asynchronous component, one that does not share the same buffer between input and output.

See Books Online "Developing a Custom Source Component "

Also take look at Books Online for the "Using Error Outputs" topic, in particular "Redirecting a Row with Asynchronous Outputs"

|||

I had a look at the Books Online "Using Error Outputs" for Asynchronous, I have add all the code thats given in there.

The Error OUT & ERROR COLUMNS ErrorColum, ErrorCode are Getting Created,

In addation to that All columns in OUTPUT should also be created Under ERROR OUT. which is not hapenning. That whould mean that I need to create error output columns, In the similar manner as we create the OutPut Columns.

Any Idea as to how to create the error output columns.

|||

Yes. In a transform or destination I would override SetUsageType to do this, add both columns at the same time., For a source you can use MapOutputColumn.

Edit - Strike, that use InsertOutputColumnAt instead.

Error OutPut in Custom Source Component

For the Custome source Component ErrorOutput, should I go for asynchronous / synchronous Output.

If i go for synchronous output

// Create the error output.
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection.New();
errorOutput.IsErrorOut = true;
errorOutput.Name = "ErrorOutput";
errorOutput.SynchronousInputID = What Id is required here;
errorOutput.ExclusionGroup = 1;

Is it the IDTSOutput90 InPut.ID / OutPut.ID which should be assigned.

Thanks Regards

Anil

The SynchronousInputID is the ID for which the output is synchronous with, so this would be the input ID. If you write an asynchronous component then obviously you do not need to set it.|||

Hi Darren

Since I am developing a Source component I will not have any InPut.ID ?

|||

Correct, by their nature source components are asynchronous, in fact they have no inputs at all. There is no input buffer, so they must create a new output buffer which is really the definition on an asynchronous component, one that does not share the same buffer between input and output.

See Books Online "Developing a Custom Source Component "

Also take look at Books Online for the "Using Error Outputs" topic, in particular "Redirecting a Row with Asynchronous Outputs"

|||

I had a look at the Books Online "Using Error Outputs" for Asynchronous, I have add all the code thats given in there.

The Error OUT & ERROR COLUMNS ErrorColum, ErrorCode are Getting Created,

In addation to that All columns in OUTPUT should also be created Under ERROR OUT. which is not hapenning. That whould mean that I need to create error output columns, In the similar manner as we create the OutPut Columns.

Any Idea as to how to create the error output columns.

|||

Yes. In a transform or destination I would override SetUsageType to do this, add both columns at the same time., For a source you can use MapOutputColumn.

Edit - Strike, that use InsertOutputColumnAt instead.

Error Output for a Destination Transformation

I am developing a custom destination component and I have encountered a few areas where there seems to be a lack of helpful documentation and examples.
1. I have not been able to find any information on or examples of creating custom destinations with an error output. The OLE DB Destination has an error output so I investigated the input and error output properties in the advanced editor and found that the OLE DB Destination error output is synchronous with the input (its SynchronousInputID matches the input's ID) and has its ExclusionGroup value set to 1. Using this information, I modeled my error output after the OLE DB Destination.

ProvideComponentProperties:
AddErrorOutput(ERROR_OUTPUT_NAME, input.ID, 1);

ProcessInput:
int errorOutputID = -1;
int errorOutputIndex = -1;
GetErrorOutputInfo(ref errorOutputID, ref errorOutputIndex);
...
buffer.DirectErrorRow(errorOutputID, 0, errorOutputIndex);

Checking the input and error output properties in the advanced editor for my custom destination component I find the following:
Input
--
ID: 3515

Error Output

ExclusionGroup: 1
ID: 3516
IsErrorOut: True
SynchronousInputID: 3515

Shortly after I start my SSIS package and it encounters an error row, I get the following exception:
[My Destination Adapter 1 [3512]] Error: System.ArgumentException: Value does not fall within the expected range. at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer90.DirectErrorRow(Int32 hRow, Int32 lOutputID, Int32 lErrorCode, Int32 lErrorColumn) at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.DirectErrorRow(Int32 outputID, Int32 errorCode, Int32 errorColumn) at MyDestination.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

2. My custom destination component is used for writing a file with a fixed schema. I followed the means by which source component examples add their output columns, but applied this to my external metadata columns. In my Validate() I check if the ExternalMetadataColumnCollection.Count == 0 and return DTSValidationStatus.VS_NEEDSNEWMETADATA; to force a call to ReinitializeMetaData(). In ReinitializeMetaData() I call a method that creates the input's external metadata columns that reflect my external data source.

This works fine except every time I add my custom destination component to a SSIS Package and go to edit the component I am greeted with a dialog box that states: "The component is not in a valid state. ... Do you want the component to fix these errors automatically?" Pressing the Yes button, I assume, makes the call to ReinitializeMetaData() and I have my external metadata columns. Where is the correct place to add the external metadata columns so the user does not have to take this extra step every time they add my component to their package?

This sample component illustrates error outputs: http://www.microsoft.com/downloads/details.aspx?FamilyID=7952c866-807b-4715-80ba-498e0a16ab18&DisplayLang=en

There are some other component examples at: http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=ssis&DisplayLang=en

Donald

|||

Thanks for directing me to the example, that helped me figure out why I am getting the exception, but it would be helpful if someone could address my questions.

Should the error output on a destination component be synchronous?

In the example Donald referred to I noticed they were not using a zero value for the error code, which was different from the examples in the Wrox Professional SSIS book, the MSDN documentation for the DirectErrorRow Method ( http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.pipelinebuffer.directerrorrow.aspx ), and “Using Error Outputs” in SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms136009.aspx ) If the use of a zero value for the error code is the reason for the System.ArgumentException, why are the examples using a zero value? Is it documented anywhere that a zero value is an invalid argument to the DirectErrorRow Method?

Although it isn’t on the topic of error outputs, does anyone have any comments on question number 2 in my original post?

|||

There is an event which fires when someone makes a connection to your component OnOutputPathAttached you can put your code in there.

|||

Thank you for your reply.

However, I am not sure if this solves my issue 2.

Since I am writing a desitnation transformation, no regular output will be attached to the transformation, will the OnOutputPathAttached be called under this circumstance?

I could probably set up my ExternalMetdData at OnInputPathAttached, but it does not make a whole lot of sense to me since the ExternalMetdData does not depend in any way on the input.

I am thinking about putting it in ProvideComponentProperties, but if there is a better place for it based on the SSIS architecture, I would very much like to know about it.

Thanks.

Error opening VSA from Script component and Script Task

TITLE: Microsoft Visual Studio

Cannot show Visual Studio for Applications editor.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.DataFlowUI.SR&EvtID=CouldNotShowVsaIDE&LinkId=20476

ADDITIONAL INFORMATION:

Engine returned Unknown Error (Microsoft.VisualBasic.Vsa)

A project with the name 'ScriptComponent_972bfcb1f5ae462081f39130ba1d36b2' already exists.

BUTTONS:

OK

This happened immediately AFTER installing Visual Studio 2005. SQL Serve Workstation tools were already installed. I am running Windows XP SP2.

Anyway have any words of wisdon as to what the problem may be or how to investigate fix it?

Thanks

Jamie

Ignore this. Rebooting has solved it!!!

-Jamie

|||

Jamie,

I've got similar message which is persistent. I cannot open any script task anymore

Error message "Cannot show the editor for this task. Specified cast is not valid (Microsoft.VisualBasic.Vsa.DT)" pops up.

I even retried re-installing VS 2005. Did not resolved the issue.

Any idea?

Gulden

|||

Try to upgrade your machine with the cumulative hotfix package (build 2153) for SQL Server 2005.

Regards,
Yitzhak

|||

Yitzak,

Thank you for your answer.

I am getting "This machine does not have a product that matches this installation package"

on the first one in x86 group.

Do you have any idea?

This is my only chance.

I've re-installed SQL Server 2005 and Visual Studio 2005. Still cannot open the scripts.

Gulden

|||

Even betterjust exit out of VS2005 and go back in. That solved it for me.

Jeff

|||

In my case they had pushed DLLs in 600 people's banded machines in my company including vbscript.dll etc. by mistake. They resolved it by re-registering them

Gulden

|||

You need to install SP1 for MS SQL Server 2005 as a prerequisite before you will be able to upgrade your machine with the cumulative hotfix package (build 2153) for SQL Server 2005. SO it is a 2 steps process.

Regards,
Yitzhak

Error opening VSA from Script component and Script Task

TITLE: Microsoft Visual Studio

Cannot show Visual Studio for Applications editor.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.DataFlowUI.SR&EvtID=CouldNotShowVsaIDE&LinkId=20476

ADDITIONAL INFORMATION:

Engine returned Unknown Error (Microsoft.VisualBasic.Vsa)

A project with the name 'ScriptComponent_972bfcb1f5ae462081f39130ba1d36b2' already exists.

BUTTONS:

OK

This happened immediately AFTER installing Visual Studio 2005. SQL Serve Workstation tools were already installed. I am running Windows XP SP2.

Anyway have any words of wisdon as to what the problem may be or how to investigate fix it?

Thanks

Jamie

Ignore this. Rebooting has solved it!!!

-Jamie

|||

Jamie,

I've got similar message which is persistent. I cannot open any script task anymore

Error message "Cannot show the editor for this task. Specified cast is not valid (Microsoft.VisualBasic.Vsa.DT)" pops up.

I even retried re-installing VS 2005. Did not resolved the issue.

Any idea?

Gulden

|||

Try to upgrade your machine with the cumulative hotfix package (build 2153) for SQL Server 2005.

Regards,
Yitzhak

|||

Yitzak,

Thank you for your answer.

I am getting "This machine does not have a product that matches this installation package"

on the first one in x86 group.

Do you have any idea?

This is my only chance.

I've re-installed SQL Server 2005 and Visual Studio 2005. Still cannot open the scripts.

Gulden

|||

Even betterjust exit out of VS2005 and go back in. That solved it for me.

Jeff

|||

In my case they had pushed DLLs in 600 people's banded machines in my company including vbscript.dll etc. by mistake. They resolved it by re-registering them

Gulden

|||

You need to install SP1 for MS SQL Server 2005 as a prerequisite before you will be able to upgrade your machine with the cumulative hotfix package (build 2153) for SQL Server 2005. SO it is a 2 steps process.

Regards,
Yitzhak

Error opening VSA from Script component and Script Task

TITLE: Microsoft Visual Studio

Cannot show Visual Studio for Applications editor.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.DataFlowUI.SR&EvtID=CouldNotShowVsaIDE&LinkId=20476

ADDITIONAL INFORMATION:

Engine returned Unknown Error (Microsoft.VisualBasic.Vsa)

A project with the name 'ScriptComponent_972bfcb1f5ae462081f39130ba1d36b2' already exists.

BUTTONS:

OK

This happened immediately AFTER installing Visual Studio 2005. SQL Serve Workstation tools were already installed. I am running Windows XP SP2.

Anyway have any words of wisdon as to what the problem may be or how to investigate fix it?

Thanks

Jamie

Ignore this. Rebooting has solved it!!!

-Jamie

|||

Jamie,

I've got similar message which is persistent. I cannot open any script task anymore

Error message "Cannot show the editor for this task. Specified cast is not valid (Microsoft.VisualBasic.Vsa.DT)" pops up.

I even retried re-installing VS 2005. Did not resolved the issue.

Any idea?

Gulden

|||

Try to upgrade your machine with the cumulative hotfix package (build 2153) for SQL Server 2005.

Regards,
Yitzhak

|||

Yitzak,

Thank you for your answer.

I am getting "This machine does not have a product that matches this installation package"

on the first one in x86 group.

Do you have any idea?

This is my only chance.

I've re-installed SQL Server 2005 and Visual Studio 2005. Still cannot open the scripts.

Gulden

|||

Even betterjust exit out of VS2005 and go back in. That solved it for me.

Jeff

|||

In my case they had pushed DLLs in 600 people's banded machines in my company including vbscript.dll etc. by mistake. They resolved it by re-registering them

Gulden

|||

You need to install SP1 for MS SQL Server 2005 as a prerequisite before you will be able to upgrade your machine with the cumulative hotfix package (build 2153) for SQL Server 2005. SO it is a 2 steps process.

Regards,
Yitzhak

Error message: server component is not supported

I try to install Microsoft SQL Server 2000 Enterprise in my Windows 2000 Pro
machine.
When I run the setup, I clicked SQL Server 2000 Components | Install
Database Server
But it pops up an error message dialog saying
"Microsoft SQL Server 2000 Enterprise Edition server component is not
supported on this operating system. only client components will be available
for installation"
Any ideas? I can only install client, but not server'
Please help!You need a better OS to run Enterprise edition. You can run the developer's
edition on 2000.
"Matthew Louden" <mattloude@.hotmail.com> wrote in message
news:uN9f10YyDHA.556@.TK2MSFTNGP11.phx.gbl...
> I try to install Microsoft SQL Server 2000 Enterprise in my Windows 2000
Pro
> machine.
> When I run the setup, I clicked SQL Server 2000 Components | Install
> Database Server
> But it pops up an error message dialog saying
> "Microsoft SQL Server 2000 Enterprise Edition server component is not
> supported on this operating system. only client components will be
available
> for installation"
> Any ideas? I can only install client, but not server'
> Please help!
>
>
>|||You can't install SQL Server Enterprise Edition on Windows 2000 Pro. You
need Windows 2000 Server or Advanced Server. Alternatively, you can install
MSDE, Developer Edition, Personal Edition, or Evaluation Edition on Windows
2000 Pro.
"Matthew Louden" <mattloude@.hotmail.com> wrote in message
news:uN9f10YyDHA.556@.TK2MSFTNGP11.phx.gbl...
> I try to install Microsoft SQL Server 2000 Enterprise in my Windows 2000
Pro
> machine.
> When I run the setup, I clicked SQL Server 2000 Components | Install
> Database Server
> But it pops up an error message dialog saying
> "Microsoft SQL Server 2000 Enterprise Edition server component is not
> supported on this operating system. only client components will be
available
> for installation"
> Any ideas? I can only install client, but not server'
> Please help!
>
>
>|||How about Windows XP?
"Casey Vanlandingham" <cvanlan@.no.spam.iastate.edu> wrote in message
news:%23%23UkvDZyDHA.1088@.tk2msftngp13.phx.gbl...
> You can't install SQL Server Enterprise Edition on Windows 2000 Pro. You
> need Windows 2000 Server or Advanced Server. Alternatively, you can
install
> MSDE, Developer Edition, Personal Edition, or Evaluation Edition on
Windows
> 2000 Pro.
>
> "Matthew Louden" <mattloude@.hotmail.com> wrote in message
> news:uN9f10YyDHA.556@.TK2MSFTNGP11.phx.gbl...
> > I try to install Microsoft SQL Server 2000 Enterprise in my Windows 2000
> Pro
> > machine.
> >
> > When I run the setup, I clicked SQL Server 2000 Components | Install
> > Database Server
> >
> > But it pops up an error message dialog saying
> > "Microsoft SQL Server 2000 Enterprise Edition server component is not
> > supported on this operating system. only client components will be
> available
> > for installation"
> >
> > Any ideas? I can only install client, but not server'
> >
> > Please help!
> >
> >
> >
> >
> >
>|||The same considerations apply to XP as well.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Matthew Louden" <mattloude@.hotmail.com> wrote in message
news:O5gwcIbyDHA.2304@.TK2MSFTNGP12.phx.gbl...
> How about Windows XP?
>
> "Casey Vanlandingham" <cvanlan@.no.spam.iastate.edu> wrote in message
> news:%23%23UkvDZyDHA.1088@.tk2msftngp13.phx.gbl...
> > You can't install SQL Server Enterprise Edition on Windows 2000 Pro.
You
> > need Windows 2000 Server or Advanced Server. Alternatively, you can
> install
> > MSDE, Developer Edition, Personal Edition, or Evaluation Edition on
> Windows
> > 2000 Pro.
> >
> >
> > "Matthew Louden" <mattloude@.hotmail.com> wrote in message
> > news:uN9f10YyDHA.556@.TK2MSFTNGP11.phx.gbl...
> > > I try to install Microsoft SQL Server 2000 Enterprise in my Windows
2000
> > Pro
> > > machine.
> > >
> > > When I run the setup, I clicked SQL Server 2000 Components | Install
> > > Database Server
> > >
> > > But it pops up an error message dialog saying
> > > "Microsoft SQL Server 2000 Enterprise Edition server component is not
> > > supported on this operating system. only client components will be
> > available
> > > for installation"
> > >
> > > Any ideas? I can only install client, but not server'
> > >
> > > Please help!
> > >
> > >
> > >
> > >
> > >
> >
> >
>