Errors not showing

Nov 1, 2010 at 11:07 PM

Currently if I have a BatchDestination command that errors - although the error shows in the execution results (hidden away) the compnent in the data flow tasks shows as Green. Surely it shouldn't - am I missing something?

Nov 3, 2010 at 2:36 AM

I've seen that. Basically, when custom components have a failure in PostExecute code (which is where the batch command is run) you get green on the component, but the Data Flow Task turns red. I think this a) a bug in SSIS or b) I shouldn't be throwing an error in PostExecute().

I'm checking on whether it's a bug, but in the meantime, you could modify the code to run the batch command in ProcessInput. I'll probably update the code to do that myself, but it will be a little while before I have time.

Nov 3, 2010 at 11:17 AM

Ah, maybe this is the reason why the table doesn't get dropped if there is an error in the sql?

I'd also like to the ability to have expressions in this component (so I can build the sql and table name from expressions), but the standard expressions tree doesn't exist. Is this something that can be added easily?

Nov 4, 2010 at 3:38 AM

Yes, if there's an error in the batch command, the DROP TABLE statement isn't run.

It should already support expressions on the Table Name and Batch Command properties. You have to select the Data Flow Task, and in the properties window, you can add an expression on the property labeled [BatchDestination].[BatchCommand] or [BatchDestination].[WorkingTable].


Nov 10, 2010 at 11:09 PM

Hi John, thanks for the replies. Yes you are right expressions do work, however you do get some funny messages initially and you can't leave the actual fields blank as they don't validate otherwise (so you have to fill in the default values with something. In the expressions view you briefly get

"The object does not support type information. This occurs when the runtime attempts to get the type information from an object to populate the Properties collection. The object must support type information."

it goes away after doing a couple of other things fortunately so not a major issue. I guess if the component handled expressions directly in the UI it would be able to do the refresh or whatever it is doing itself and you wouldn't need to populate the values with some dummy values to allow it to validate.

Another thing that I've found is that if you have the component receiving duplicate named columns in the input source the "Input columns" tab is empty. E.g. if you have a "Derived Column" Transformation that has created a column with the same name as another. Took me a while to work it out, but finally got to the bottom of it. Not sure what the correct behaviour would be but I guess you could provide text with the reason for being blank or provide the list with lineage ids so you can work out which one you actually want. Do you want me to create an issue/feature tracker bug for this?

This really is a great component apart from these minor issues. This along with the Kimball component has inspired me to build some custom SSIS components which I have a couple of ideas for. Thanks!