public class ben:harrell

September 18, 2007

SSIS Custom Component – ProvideComponentProperties vs ReInitializeMetadata

Filed under: .NET, Custom Source Component, database, Integration Servicees, SQL Server 2005, SSIS, Technology — benjamin harrell @ 9:47 am

 

I am currently working on a custom source component in SSIS that converts EBCDIC data to ASCII inline and one of the challenges I face is creating dynamic outputs and output columns based on the layout process of the source component.  Normally, when you want to additional outputs on your component you create them in by override ProvideComponentProperties like this:

public override void ProvideComponentProperties()
{
    // add the outputComponentMetaData.UsesDispositions = true;
   IDTSOutput90 output =     ComponentMetaData.OutputCollection.New();
   output.Name = “My New Output”;
   output.ExternalMetadataColumnCollection.IsUsed = true;

This works really well if all of your output information is available at design time (in the SSIS ui) but what happens if your dynamic outputs are determined at runtime?  ProvideComponentProperties is only called one time, when the component is added to designer surface.  In order to dynamically add outputs at a later point you must use ReInitializeMetaData which is called whenever Validate returns VS_NEEDSNEWMETADATA.

public override void ReinitializeMetaData()
{
   // add the output
   ComponentMetaData.UsesDispositions = true;
   IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
   output.Name = “My New Output”;
   output.ExternalMetadataColumnCollection.IsUsed = true;
 }
 

Note that I have not shown the additional work of adding columns to either of these scenarios, you will need to add that code yourself. 

July 14, 2007

SSIS Row Limits and DefaultBufferMaxRows (Part 2)

Filed under: .NET, Custom Source Component, database, Integration Servicees, SSIS — benjamin harrell @ 10:44 am

I believe the mystery is solved and I’m sad to say that (as usual) it was something silly but crucial.  In a custom source component you must create a new PipelineBuffer in order to write your rows out.  SSIS has provided a way for us to let the engine know when we are done adding rows with a simple method “SetEndOfRowset”.  The name says it all and even the MSDN documentation is clear that you must call this.  In addition, your error logs if you don’t call this method will say something like:

The PrimeOutput method on <your component> returned success, but did not report an end of the rowset. 

 You would think that this error message would be enough to warn any developer that they were missing a key line of code but sadly that wasn’t the case.  In my case I had actually allocated 2 buffers, 1 for data rows and 1 for error rows.  I set a breakpoint on my SetEndOfRowset call and watched it execute so I just knew this wasn’t my problem.  But I forgot the second buffer!  So just a note to all you brave souls commanding bits on the SSIS battlefield.  Call SetEndOfRowset for EACH buffer you allocate. Good Luck! 

Create a free website or blog at WordPress.com.