SSIS Custom Component – Surrogate Key Generator

I’m doing some housecleaning getting ready to upgrade my company laptop to Windows 7.  Yeah I know I’m a bit late, but they don’t “support” Windows 7 back at the home office in Dayton yet so I was a bit hesitant. So, screw it I’m upgrading anyway.  While cleaning up and getting ready for this, I found a few things to share – one being this custom SSIS component.   I was going to do a series on these, but I’m sort of not on board with them anymore after working on a large ETL project over the past year and a half or so.  On the one hand, It’s hard for me to do stuff this customized; other tools have these components already, and hopefully so will SSIS in the future.  On the other hand, though, they’re very powerful. Super-stealthy bad@ss ninja ETL types keep their hands off the keyboard, sort of hard to do in SSIS at this stage of the game, but no worries.  It’s a relatively new tool at 4 years old, and someday it will be right up there with the Informatica, Datastage, others.

Here’s a custom component to create a surrogate key without having to use IDENTITY property on a column.  Let me know if you have problems or can’t get it to work.  Use freely… and if you improve it please send back!

Thanks much,

Lee

--------------------

“I need to speak with you private-lyyyy, I found your resume on the printer. EVERYONE!  I FOUND YOUR RESUME ON THE PRINTER!…”

using System.Linq;
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper; 
using Microsoft.SqlServer.Dts.Runtime.Wrapper; 

namespace NamespaceSKGenerator
{
    [Microsoft.SqlServer.Dts.Pipeline.DtsPipelineComponent(DisplayName = "SurrogateKeyGenerator",
        Description = "SSIS Custom Component Surrogate Key Generator", ComponentType = ComponentType.Transform)]
    public class SurrogateKeyGenerator: PipelineComponent
    {
        #region Local Variables

        private int SurrogateKeyValue = 0;
        private object variable;
        private bool areInputColumnsValid = true;
        private int[] inputBufferColumnIndex;
        private int[] outputBufferColumnIndex;
        private IDTSVariables100 variables = null;

        #endregion Local Variables

        #region Design Time        
        
        public override void ProvideComponentProperties()
        {
            ComponentMetaData.Name = "SurrogateKeyGenerator";
            ComponentMetaData.Description = "SSIS Custom Component Surrogate Key Generator";
            ComponentMetaData.ContactInfo = "Lee Everest mailto:ab5sr@hotmail.com";
            
            // Reset the component.
            base.RemoveAllInputsOutputsAndCustomProperties();

            IDTSInput100 input = ComponentMetaData.InputCollection.New();
            input.Name = "Input";

            // Add output objects.
            IDTSOutput100 output = ComponentMetaData.OutputCollection.New();
            output.Name = "Output";
            output.SynchronousInputID = input.ID; //Synchronous transformation

            //Add error objects
            IDTSOutput100 errorOutput = ComponentMetaData.OutputCollection.New();
            errorOutput.Name = "Error";
            errorOutput.IsErrorOut = true;
        }

        //Design time - Metadata Validataor
        public override DTSValidationStatus Validate()
        {
            bool pbCancel = false;

            // Validate that there is only one input.
            if (ComponentMetaData.InputCollection.Count != 1)
            {
                ComponentMetaData.FireError(0, ComponentMetaData.Name, "Incorrect number of inputs.", "", 0, out pbCancel);
                return DTSValidationStatus.VS_ISCORRUPT;
            }

            // Validate number of outputs.
            if (ComponentMetaData.OutputCollection.Count != 2)
            {
                ComponentMetaData.FireError(0, ComponentMetaData.Name, "Incorrect number of outputs.", "", 0, out pbCancel);
                return DTSValidationStatus.VS_ISCORRUPT;
            }

            // Determine whether the metdada needs refresh
            IDTSInput100 input = ComponentMetaData.InputCollection[0];
            IDTSVirtualInput100 vInput = input.GetVirtualInput();

            bool cancel = false;

            foreach (IDTSInputColumn100 column in input.InputColumnCollection)
            {
                try
                {
                    IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(column.LineageID);
                }
                catch
                {
                    ComponentMetaData.FireError(0, ComponentMetaData.Name, "The input column " + column.IdentificationString + " does not match a column in the upstream component.", "", 0, out cancel);
                    areInputColumnsValid = false;
                    return DTSValidationStatus.VS_NEEDSNEWMETADATA;
                }

            }

            //validate input to be of type string/numeric only
            for (int x = 0; x < input.InputColumnCollection.Count; x++)
            {
                if (!(input.InputColumnCollection[x].DataType == DataType.DT_I4))
                {
                    ComponentMetaData.FireError(0, ComponentMetaData.Name, "Invalid Data Type specified for " + input.InputColumnCollection[x].Name
                                                        + ". Supported Data Types are DT_STR,DT_WSTR,DT_NUMERIC and DT_NUMERIC", "", 0, out pbCancel);
                    return DTSValidationStatus.VS_ISCORRUPT;
                }
            }

            //create corresponding output columns dynamically
            IDTSOutput100 output = ComponentMetaData.OutputCollection[0];

            foreach (IDTSInputColumn100 inputcolumn in input.InputColumnCollection)
            {
                bool IsExist = false;
                foreach (IDTSOutputColumn100 OutputColumn in output.OutputColumnCollection)
                {
                    if (OutputColumn.Name == "SurrogateKey_" + inputcolumn.Name)
                    {
                        IsExist = true;
                    }
                }

                if (!IsExist)
                {
                    IDTSOutputColumn100 outputcol = output.OutputColumnCollection.New();
                    outputcol.Name = "SurrogateKey_" + inputcolumn.Name;
                    outputcol.Description = "Surrogate Key " + inputcolumn.Name + " problem.";
                    outputcol.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0);
                }
            }

            //Remove redundant output columns that don't match input columns
            if (output.OutputColumnCollection.Count > input.InputColumnCollection.Count)
            {
                foreach (IDTSOutputColumn100 OutputColumn in output.OutputColumnCollection)
                {
                    Boolean IsRedundant = true;
                    foreach (IDTSInputColumn100 InputCoulmn in input.InputColumnCollection)
                    {
                        IsRedundant = OutputColumn.Name.Contains(InputCoulmn.Name) ? false : true;
                        if (!IsRedundant)
                            break;
                    }

                    if (IsRedundant)
                    {
                        output.OutputColumnCollection.RemoveObjectByID(OutputColumn.ID);
                    }
                }
            }

            return DTSValidationStatus.VS_ISVALID;
        }

        //Design Time - method to autocorrect VS_NEEDSNEWMETADATA error
        public override void ReinitializeMetaData()
        {
            if (!areInputColumnsValid)
            {
                IDTSInput100 input = ComponentMetaData.InputCollection[0];
                IDTSVirtualInput100 vInput = input.GetVirtualInput();

                foreach (IDTSInputColumn100 column in input.InputColumnCollection)
                {
                    IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(column.LineageID);

                    if (vColumn == null)
                        input.InputColumnCollection.RemoveObjectByID(column.ID);
                }
                areInputColumnsValid = true;
            }

        }

        //Override InsertOutputColumnAt to prevent addition of new column from Advanced Editor
        public override IDTSOutputColumn100 InsertOutputColumnAt(
             int outputID,
             int outputColumnIndex,
             string name,
             string description)
        {
            bool cancel = true;
            ComponentMetaData.FireError(0, ComponentMetaData.Name, "Output columns cannot be added to " + ComponentMetaData.Name, "", 0, out cancel);
            //bubble-up the error to VS
            throw new Exception("Output columns cannot be added to " + ComponentMetaData.Name, null);
        }

        #endregion Design Time

        #region Run Time
        
        public override void PreExecute()
        {
            SetExternalVariables();   

            IDTSInput100 input = ComponentMetaData.InputCollection[0];
            inputBufferColumnIndex = new int[input.InputColumnCollection.Count];

            for (int x = 0; x < input.InputColumnCollection.Count; x++)
            {
                IDTSInputColumn100 column = input.InputColumnCollection[x];
                inputBufferColumnIndex[x] = BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID);
            }

            IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
            outputBufferColumnIndex = new int[output.OutputColumnCollection.Count];

            for (int x = 0; x < output.OutputColumnCollection.Count; x++)
            {
                IDTSOutputColumn100 outcol = output.OutputColumnCollection[x];
                outputBufferColumnIndex[x] = BufferManager.FindColumnByLineageID(input.Buffer, outcol.LineageID);
            }
        }

        private void SetExternalVariables()
        {
            VariableDispenser.LockOneForRead("StartID", ref variables);
            variable = variables[0].Value;
            SurrogateKeyValue = Convert.ToInt32(variable);
        }

        public override void ProcessInput(int inputID, PipelineBuffer buffer)
        {
            if (!buffer.EndOfRowset)
            {
                while (buffer.NextRow())
                {
                    for (int x = 0; x < inputBufferColumnIndex.Length; x++)
                    {
                        DataType BufferColDataType;
                        BufferColDataType = buffer.GetColumnInfo(inputBufferColumnIndex[x]).DataType;

                        if (BufferColDataType == DataType.DT_I4 )
                                SurrogateKeyValue = IncrementSurrogateKey(SurrogateKeyValue);
                        
                        buffer.SetInt32(outputBufferColumnIndex[x],SurrogateKeyValue) ;
                    }
                }
            }
        }

        #endregion Run Time      
      
        #region Custom Code

        private int IncrementSurrogateKey(int key)
        {
            try
            {
                return (SurrogateKeyValue++);
            }
            catch (Exception e)
            {
                throw new ApplicationException(e.ToString());
            }
        }
        #endregion Custom Code
    }
}