SSIS Script Component with Multiple Inputs

Question

I am looking for a way to create a script component within SSIS which will accept multiple inputs.

I need this so I can do a form of custom joining.

It seems really silly that a script can have multiple outputs but only one input, I'm sure I must be doing something wrong.

Any help?

Answer 1

try putting a Union All component before your script component, and rather than having the columns match up, add columns to the output so that each element coming in winds up in a unique column.

Keep in mind that at first the SSIS script component will process items row by row, so a custom joining mechanism will need to be done using some collections and capturing the event that fires after all the rows have been processed.

Please keep in mind that I'm assuming you're using a script component in the Data Flow and not the Control Flow, and that I'm assuming you're using SSIS 2005.

Answer 2

I"m currently experimenting with using SSIS variables to pass syncronization objects from one script component ot another. It's rather clumsey, but you can effectively use multiple script components to accept various inputs, and then use the System.Threading classes to sync passing values from one script component to another.

The hurdle is that each script is in it's own namespace and can't share classes with other scripts (unless you want to compile & deploy your own assembly with SSIS). What I'm currently doing is passing (over the shared variable) a reference to an object[], containing a reference to a ManualResetEvent, the SSIS PipelineBuffer, and an array of pipeline column indices.

This is enough to allow the receiving script to reconstruct the other script's input pipeline, pump it dry, then signal back that it's finished.

It's functional, though I'm currently looking for work-arounds to the fact that (it would seem) SSIS invokes "ProcessInput" twice during a script component's life time. If any of the geniuses here on SO have a solution to that, then I think we've pretty much got a [clumsey] solution to allowing multiple inputs to a single script component.

Any takers?

---- EDIT----

I've got this up and running - this trick is to use syncronization to prevent the multi-threaded invocation of ProcessInput from attempting to share th input buffer mulitple times. Below is a crude code sample of how I got this working:

Script Component 1: Shares it's input...

using System;
using System.Collections;
using System.Threading;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{


    System.Collections.Generic.List<object> shared = null;
    System.Threading.ManualResetEvent sync;

    public override void ProcessInput(int InputID, PipelineBuffer Buffer)
    {
        lock (this)
        {
            if (InputID == 82)
            {
                if (shared == null)
                {
                    shared = new System.Collections.Generic.List<object>();
                    sync = new System.Threading.ManualResetEvent(false);
                    shared.Add(sync);
                    shared.Add(Buffer);
                    shared.Add(GetColumnIndexes(InputID));

                    IDTSVariables100 vars = null;

                    this.VariableDispenser.LockOneForWrite("Test", ref vars);

                    vars[0].Value = shared;
                    vars.Unlock();
                    sync.WaitOne();
                    System.Windows.Forms.MessageBox.Show("Done");
                }
            }
        }
    }
}

... then Script Component 2 (which consumes Script Component 1's input)...

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    System.Threading.ManualResetEvent sync = null;
    InputXBuffer sharedBuffer = null;

    public override void Input0_ProcessInput(Input0Buffer Buffer)
    {
        lock (this)                 // Only 1 thread at a time
        {
            if (sharedBuffer == null)
            {
                object Test = null;
                while (Test == null)
                {
                    System.Threading.Thread.Sleep(100);
                    IDTSVariables100 vars = null;
                    this.VariableDispenser.LockOneForRead("Test", ref vars);
                    Test = vars[0].Value;
                    vars.Unlock();
                }

                var sharedList = Test as System.Collections.Generic.List<object>;

                if (sharedList != null)
                {
                    sync = sharedList[0] as System.Threading.ManualResetEvent;
                    var buffer = sharedList[1] as PipelineBuffer;
                    var bufferColumnIndexes = sharedList[2] as int[];
                    sharedBuffer = new InputXBuffer(buffer, bufferColumnIndexes);
                }
            }
       }

        while (sharedBuffer.NextRow())
        {
            // ... do stuff with Script Component 1's shared input here...
        }
        sync.Set();     // Signal script 1 that we're done
    }
}

The script's both share a read/write variable called "Test" - you can change the variable name to suite your needs. HOpefully the above serves as a working model for you to take this to the next level.

PS:- If you have the time & energy, writing a proper custom SSIS component really is the way to go for multi input scenarios.