Custom SSIS Data Flow Component - Credit Card Number Validator (CCNV)

SQL Server Integration Services (SSIS) provides a wide range of out-of-box components to perform almost any ETL task. In addition to out-of-box components, the object model of Integration Services allows you to create re-usable custom components.  Different types of custom components can be developed, including:

• Custom tasks.
• Custom connection managers.   Connect to external data sources that are not currently supported.
• Custom log providers.    Log package events in formats that are not currently supported.
• Custom enumerators.   Support iteration over a set of objects or values formats that are not currently supported.
• Custom Data Flow Components.   Can be configured as sources, transformations, or destinations.

In this post, step-by-step instruction is provided to create a Custom SSIS Data Flow Component called Credit Card Number Validator (CCNV). This component validates Credit Card Numbers using Luhn Mod 10 Algorithm. There are two versions of this component available for download from CodePlex:

1. CCNV08, which is SQL Server 2008 version of Credit Card Number Validator – download
2. CCNV05, which is SQL Server 2005 version of Credit Card Number Validator – download

What is Credit Card Number Validator (CCNV)?

The Credit Card Number Validator takes in a String/Numeric input and performs a Synchronous, Non-Blocking transformation using Luhn Mod 10 algorithm and determines whether the input is a valid credit card number or not. The output is true if the input is a valid credit card number and false otherwise. The Credit Card Number Validator is a Class Library (a DLL) that will be loaded at design time by BIDS and at run time by the Integration Services runtime.

Figure 1 - Credit Card Number Validator

Step 1: Create a Class Library Project

Create a new project of type Class Library from VS.  I’m using C# for this project.

Step 2: Inherit from PipelineComponent Base Class

• Since Credit Card Number Validator is a Data Flow Component, it has to inherit PipelineComponent Base class and override its Design-time and Run-time methods.
• The DTSPipelineComponentAttribute is applied to this class to make it a Transformation type Data Flow Component

using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Pipeline; // Contains the managed data flow engine
using Microsoft.SqlServer.Dts.Pipeline.Wrapper; //Contains the primary interop assembly (PIA), or wrapper, for the native data flow engine.
using Microsoft.SqlServer.Dts.Runtime.Wrapper; //Contains the primary interop assembly (PIA), or wrapper, for the native run-time engine.
namespace BennyAustin.SQLServer.SSIS.Extensions
{
[Microsoft.SqlServer.Dts.Pipeline.DtsPipelineComponent(DisplayName = "CCNValidator", Description = "Credit Card Number validation using Luhn Mod 10 algorithm.", ComponentType = ComponentType.Transform)]
public class CCNValidator : PipelineComponent
{
}

Step 3: Design-time Methods

Design-time Methods is triggered by BIDS when the custom component is dragged from the SSIS toolbox and dropped in a Data Flow and connected to other components. Whenever a metadata change occurs the appropriate Design-Time method is triggered.

Override ProvideComponentProperties()

 The initialization of the component occurs in the ProvideComponentProperties method. This method is called by BIDS when the component is added to the data flow task for the first time, and is similar to a class constructor.

For the Credit Card Number Validator, the following tasks are performed in this method:

• Component information is set.
• An Input Object is added.
• An Output Object is added. The component is marked as a synchronous transformation by setting the SynchronousInputID property of the Output object to the ID of the Input.
• An Error Object is added

SQL Server 2008 Code

        public override void ProvideComponentProperties()
        {
            // Set component information
            ComponentMetaData.Name = "CCNValidator";
            ComponentMetaData.Description = "A SSIS Data Flow Transformation Component to Validate Credit Card Number Using Luhn Mod 10 algorithm.";
            ComponentMetaData.ContactInfo = "Benny Austin";

            // Reset the component.
            base.RemoveAllInputsOutputsAndCustomProperties();

            // Add input objects
            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;
         }

SQL Server 2005 Code

public override void ProvideComponentProperties()
{
// Set component information
ComponentMetaData.Name = "CCNValidator";
ComponentMetaData.Description = "A SSIS Data Flow Transformation Component to Validate Credit Card Number Using Luhn Mod 10 algorithm.";
ComponentMetaData.ContactInfo = "Benny Austin";
// Reset the component.
base.RemoveAllInputsOutputsAndCustomProperties();
// Add input objects
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "Input";
// Add output objects.
IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "Output";
output.SynchronousInputID = input.ID; //Synchronous transformation
//Add error objects
IDTSOutput90 errorOutput = ComponentMetaData.OutputCollection.New();
errorOutput.Name = "Error";
errorOutput.IsErrorOut = true;
}

Override Validate()

 Validate() method verifies that the component is correctly configured in BIDS. This method is triggered every time metadata change occurs. For e.g. when a new input column is added, when an existing input column is removed, when the input/output path changes.

For the Credit Card Number Validator, the following tasks are performed in this method:

• The number of Inputs and Outputs is checked and exception is raised if required.
• Determines whether a metadata refresh is required and raises exception if required.
• Validates the data type of input columns. Accepts only String/Numeric inputs raises exception for any other data type.
• For every input column, automatically creates a corresponding output column of type Boolean.  For e.g. if the input column is called Col1, the output column will be automatically created with name IsValid CCN Col1 which is of type Boolean.
• Removes redundant output columns that don’t match input columns. This is required when a previously used input column has been removed.

SQL Server 2008 Code

        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_STR ||
                    input.InputColumnCollection[x].DataType == DataType.DT_WSTR ||
                    input.InputColumnCollection[x].DataType == DataType.DT_DECIMAL ||
                    input.InputColumnCollection[x].DataType == DataType.DT_NUMERIC) )
                {
                    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 == "IsValidCCN " + inputcolumn.Name)
                    {
                        IsExist = true;
                    }
                }

                if (!IsExist)
                {
                    IDTSOutputColumn100 outputcol = output.OutputColumnCollection.New();
                    outputcol.Name = "IsValidCCN " + inputcolumn.Name;
                    outputcol.Description = "Indicates whether " + inputcolumn.Name + " is a Valid Credit Card Number";
                    outputcol.SetDataTypeProperties(DataType.DT_BOOL, 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;
        }

SQL Server 2005 Code

        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
            IDTSInput90 input = ComponentMetaData.InputCollection[0];
            IDTSVirtualInput90 vInput = input.GetVirtualInput();

            bool cancel = false;

            foreach (IDTSInputColumn90 column in input.InputColumnCollection)
            {
                try
                {
                    IDTSVirtualInputColumn90 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_STR ||
                    input.InputColumnCollection[x].DataType == DataType.DT_WSTR ||
                    input.InputColumnCollection[x].DataType == DataType.DT_DECIMAL ||
                    input.InputColumnCollection[x].DataType == DataType.DT_NUMERIC) )
                {
                    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
            IDTSOutput90 output = ComponentMetaData.OutputCollection[0];

            foreach (IDTSInputColumn90 inputcolumn in input.InputColumnCollection)
            {
                bool IsExist = false;
                foreach (IDTSOutputColumn90 OutputColumn in output.OutputColumnCollection)
                {
                    if (OutputColumn.Name == "IsValidCCN " + inputcolumn.Name)
                    {
                        IsExist = true;
                    }
                }

                if (!IsExist)
                {
                    IDTSOutputColumn90 outputcol = output.OutputColumnCollection.New();
                    outputcol.Name = "IsValidCCN " + inputcolumn.Name;
                    outputcol.Description = "Indicates whether " + inputcolumn.Name + " is a Valid Credit Card Number";
                    outputcol.SetDataTypeProperties(DataType.DT_BOOL, 0, 0, 0, 0);
                }
            }

            //Remove redundant output columns that don't match input columns
            if (output.OutputColumnCollection.Count > input.InputColumnCollection.Count)
            {
                foreach (IDTSOutputColumn90 OutputColumn in output.OutputColumnCollection)
                {
                    Boolean IsRedundant =true;
                    foreach(IDTSInputColumn90 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;
        }

Override ReinitializeMetaData()

  The ReinitializeMetaData () method is called in response to the component returning VSNEEDSNEWMETADATA from Validate(). In this method, the code to auto correct the metadata errors is implemented.

SQL Server 2008 Code

        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;
            }

        }

SQL Server 2005 Code

        public override void ReinitializeMetaData()
        {
            if (!areInputColumnsValid)
            {
                IDTSInput90 input = ComponentMetaData.InputCollection[0];
                IDTSVirtualInput90 vInput = input.GetVirtualInput();

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

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

        }

Override InsertOutputColumnAt()

 The InsertOutputColumnAt() is overridden to disallow package developers to add Output Columns to Credit Card Number Validator from Advanced Editor. This is required because the output columns are automatically created in the overridden Validate () method.

SQL Server 2008 Code

       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);
            return null;
        }

SQL Server 2005 Code

       public override IDTSOutputColumn90 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);
            return null;
        }

 Step 4: Build and Deploy Custom Component

With the Design-time Methods complete, it’s a good time to build and deploy the Credit Card Number Validator component. At this point the component’s functionality is not available, because the Run-time methods are yet to be implemented. Still this provides an opportunity to test the component’s interfaces. Steps to Build and Deploy Custom SSIS Component.

Step 5: Debug and Troubleshoot Design-time Errors

Should you get any Design-time errors, debug and fix Design-time methods of Credit Card Number Validator before coding the Run-time methods. Debugging Custom SSIS Components.

Step 6: Run-time Methods

Run-time methods are called during the execution of the Data Flow. The run-time methods provide functionality to the Custom Components

Override PreExecute()

 PreExecute() method is once per component during the Data flow execution. It’s a good place to add preparatory code. For the Credit Card Number Validator, this method is a good place to earmark the Input and Output Columns from the several other columns that exists in the buffer (contributed from other components in the Data Flow).

IMPORTANT It is important to note that since Credit Card Number Validator is a Synchronous transformation component, there is no output buffer. Rather the Output columns are part of the same Input buffer.

SQL Server 2008 Code

        public override void PreExecute()
        {
            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];
                //This is the key - synchronous output does not appear in output buffer, but in input buffer
                outputBufferColumnIndex[x] = BufferManager.FindColumnByLineageID(input.Buffer,outcol.LineageID);
            }

        }

SQL Server 2005 Code

        public override void PreExecute()
        {
            IDTSInput90 input = ComponentMetaData.InputCollection[0];
            inputBufferColumnIndex = new int[input.InputColumnCollection.Count];

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

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

            for (int x = 0; x < output.OutputColumnCollection.Count; x++)
            {
                IDTSOutputColumn90 outcol = output.OutputColumnCollection[x];
                //This is the key - synchronous output does not appear in output buffer, but in input buffer
                outputBufferColumnIndex[x] = BufferManager.FindColumnByLineageID(input.Buffer,outcol.LineageID);
            }

        }

 Override ProcessInput()

 ProcessInput is called repeatedly as the data flow task receives full buffers from the upstream components. This is the method where the functionality of the Credit Card Number Validator component is implemented. In this method, the input column is validated using Luhn Mod 10 Algorithm.

Code for Luhn Mod 10 Algorithm

        public bool IsCreditCardValid(string cardNumber)
        {
            const string allowed = "0123456789";
            int i;

            StringBuilder cleanNumber = new StringBuilder();
            for (i = 0; i < cardNumber.Length; i++)
            {
                if (allowed.IndexOf(cardNumber.Substring(i, 1)) >= 0)
                    cleanNumber.Append(cardNumber.Substring(i, 1));
            }
            if (cleanNumber.Length < 13 || cleanNumber.Length > 16)
                return false;

            for (i = cleanNumber.Length + 1; i <= 16; i++)
                cleanNumber.Insert(0, "0");

            int multiplier, digit, sum, total = 0;
            string number = cleanNumber.ToString();

            for (i = 1; i <= 16; i++)
            {
                multiplier = 1 + (i % 2);
                digit = int.Parse(number.Substring(i - 1, 1));
                sum = digit * multiplier;
                if (sum > 9)
                    sum -= 9;
                total += sum;
            }
            return (total % 10 == 0);
        }

The Boolean value returned by the IsCreditCardValid() method is used to overwrite the Buffer Columns that correspond to Output Columns.

SQL Server 2008/2005 Code

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

                            BufferColDataType = buffer.GetColumnInfo(inputBufferColumnIndex[x]).DataType;

                            if (BufferColDataType == DataType.DT_STR ||
                                BufferColDataType == DataType.DT_WSTR)
                            {
                                IsValid = IsCreditCardValid(buffer.GetString(inputBufferColumnIndex[x]));
                            }
                            else if (BufferColDataType == DataType.DT_NUMERIC ||
                                    BufferColDataType == DataType.DT_DECIMAL)
                            {

                                IsValid = IsCreditCardValid(buffer.GetDecimal(inputBufferColumnIndex[x]).ToString());
                            }

                            buffer.SetBoolean(outputBufferColumnIndex[x], IsValid);
                         }
                    }
                }

            }

Step 4: Build and Deploy Custom Component

With the Run-time Methods complete, the Credit Card Number Validator component is now ready to use.  Build and Deploy the Class Library. Steps to Build and Deploy Custom SSIS Component.

Create a new SSIS package to test the Credit Card Number Validator component.

Step 5: Debug and Troubleshoot Design-time Errors

Should you get any Run-time errors, follow this post to troubleshoot and fix the errors. Debugging Custom SSIS Components.

Well, that completes the step-by-step instruction to develop the Credit Card Number Validator. I hope you found this useful.

Benny Austin

About these ads