SSIS (SQL Server Integration Services) is an ETL tool, provided by Microsoft, for effectively managing data loading in enterprise-level Data warehouses. It provides various types of source and destination adapters for synchronizing data among heterogeneous data sources. Many inbuilt transformation components are also available for data manipulation and data streaming in the Dataflow task, although sometimes these transformations seem to be insufficient in transforming data as per business requirements. In such cases, .Net code can be integrated in SSIS using Scripts Component. The challenge comes when you require such functionality in many SSIS packages, because then managing and manipulation of .Net code becomes difficult. SSIS Custom Component written in .Net comes in handy for developers to overcome such situations.
BIDS (Business Intelligence Development Studio) is an integrated platform for the design and development of SSIS package and Custom components. This article demonstrates the development of Custom component INITCAP, the integration of custom component in BIDS, and the usage of this component in an SSIS package. As the name suggests, “INTICAP” can be used in an SSIS package for manipulation of text data in “Proper Case.”
Let’s start with this interesting journey.
This example is using SQL Server 2008 R2 and .Net 3.5 framework on 64-bit windows environment. If you have a different environment, setting a system of executable and referencing libraries will be changed accordingly. The solution is also available on GitHub here.
Step One: Create New SSIS Project
Using BIDS, create a new class library project “SSISCustomComponents” using C# or your preferable language and add the following references in the project. The default location for these references will be C;/Program Files (x86)/Microsoft SQL Server/100/SDK/Assemblies
Step Two: Add New Class File
Add “INITCAP.cs” class file in the project. Inherit this class from the PipelineComponent class and assign the required attributes value using DtsPipelineComponent as given here:
using System; using System.Linq; using System.Text.RegularExpressions; using Microsoft.SqlServer.Dts.Pipeline; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.Globalization; using System.Threading; namespace SSISCustomComponents { [DtsPipelineComponent(DisplayName = "INITCAP" , Description = "INITCAP Component for Dataflow Task- Developed by Tejpal Baghel" , ComponentType = ComponentType.Transform , IconResource="Icon.ico")] public class INITCAP : PipelineComponent { } }
Step Three: ProvideComponentProperties Method
This method will execute when this component is added to a data flow task as design time. The inputs, outputs, and custom properties of this component are defined by this method. Exceptional text processing can be controlled using the “EnableTextProcessing” property, set to either true or false. If this property value is set to true, then exceptional text processing will be applied as per values assigned to “UpperCase,” “LowerCase,” and “MixedCase” custom properties.
public override void ProvideComponentProperties() { ComponentMetaData.Name = "INITCAP"; ComponentMetaData.Description = "INITCAP Component for Dataflow Task- Developed by Tejpal Baghel"; ComponentMetaData.ContactInfo = "Tejpal.Baghel@3PillarGlobal.com"; RemoveAllInputsOutputsAndCustomProperties(); IDTSInput100 inputObject = ComponentMetaData.InputCollection.New(); inputObject.Name = "InputToINITCAP"; inputObject.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent; IDTSOutput100 outputObject = ComponentMetaData.OutputCollection.New(); outputObject.Name = "OutputFromINITCAP"; outputObject.SynchronousInputID = inputObject.ID; //Synchronous Transform IDTSCustomProperty100 TextProcessing = ComponentMetaData.CustomPropertyCollection.New(); TextProcessing.Name = "EnableTextProcessing"; TextProcessing.Description = "Enable Text Processing"; TextProcessing.Value = "True"; IDTSCustomProperty100 AlwaysUpperCase = ComponentMetaData.CustomPropertyCollection.New(); AlwaysUpperCase.Name = "UpperCase"; AlwaysUpperCase.Description = "Always Upper Case"; AlwaysUpperCase.Value = "IND,US,UK"; IDTSCustomProperty100 AlwaysLowerCase = ComponentMetaData.CustomPropertyCollection.New(); AlwaysLowerCase.Name = "LowerCase"; AlwaysLowerCase.Description = "Always Lower Case"; AlwaysLowerCase.Value = "in,out,for,and,or,not,yes,no"; IDTSCustomProperty100 MixedCaseInput = ComponentMetaData.CustomPropertyCollection.New(); MixedCaseInput.Name = "MixedCase"; MixedCaseInput.Description = "Mixed Case"; MixedCaseInput.Value = "Po Box|PO Box,hod|HoD"; IDTSCustomProperty100 ColumnsPrefix = ComponentMetaData.CustomPropertyCollection.New(); ColumnsPrefix.Name = "ColumnsPrefix"; ColumnsPrefix.Description = "ColumnsPrefix"; ColumnsPrefix.Value = "INITCAP_"; AddErrorOutput("ErrorFromINITCAP", inputObject.ID, outputObject.ExclusionGroup); }
Part A: Custom Logger Method
This is a user-defined centralized and reusable method for fire error/exceptions entry in a log pipeline.
private void Logger(string MessageText) { bool cancel = false; this.ComponentMetaData.FireError(0, this.ComponentMetaData.Name, MessageText, "", 0, out cancel); }
Part Four: Validate Method
The Validate method will execute repeatedly when a component is edited for verification of the component’s configuration in SSIS designer at design time. DTSValidationStatus enum provides information of configuration status.
Enum Member Name | Value | Description |
VS_ISVALID | 0 | Component is correctly configured and ready for execution |
VS_ISBROKEN | 1 | Component has an error that can be rectified by editing the component in the designer |
VS_NEEDSNEWMETADATA | 2 | Indicates that an error exists in the ComponentMetaData |
VS_ISCORRUPT | 4 | Indicates that the component has discovered errors that should only occur if the ComponentMetaSData property has been modified directly |
Two options will be performed in this method:
public override DTSValidationStatus Validate() { IDTSInput100 input = ComponentMetaData.InputCollection[0]; string errorMsg = "Wrong datatype specified for {0}. It accepts DT_STR and DT_WSTR"; for (int x = 0; x < input.InputColumnCollection.Count; x++) { if (!(input.InputColumnCollection[x].DataType == DataType.DT_STR || input.InputColumnCollection[x].DataType == DataType.DT_WSTR)) { Logger(String.Format(errorMsg, input.InputColumnCollection[x].Name)); return DTSValidationStatus.VS_ISCORRUPT; } } IDTSOutput100 output = ComponentMetaData.OutputCollection[0]; foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection) { bool IsPresent = false; foreach (IDTSOutputColumn100 outputColumn in output.OutputColumnCollection) { if (outputColumn.Name == ComponentMetaData.CustomPropertyCollection["ColumnsPrefix"].Value.ToString() + inputColumn.Name) { IsPresent = true; } } if (!IsPresent) { IDTSOutputColumn100 outputcol = output.OutputColumnCollection.New(); outputcol.Name = ComponentMetaData.CustomPropertyCollection["ColumnsPrefix"].Value.ToString() + inputColumn.Name; outputcol.Description = String.Format("{0} contains valid data", inputColumn.Name); outputcol.SetDataTypeProperties(inputColumn.DataType,inputColumn.Length, 0, 0,inputColumn.CodePage); } } return DTSValidationStatus.VS_ISVALID; }
Step Five: ReinitializeMetaData Method
The ReinitializeMetaData method will execute in response to the component returning VS_NEEDSNEWMETADATA from the Validate method.
public override void ReinitializeMetaData() { ComponentMetaData.RemoveInvalidInputColumns(); ReinitializeMetaData(); }
Step Six: InsertOutputColumnAt Method
This prevents users from adding a new output column using the advanced editor. This is done because the component has already added an output column in the Validate method.
public override IDTSOutputColumn100 InsertOutputColumnAt(int outputID,int outputColumnIndex,string name,string description) { Logger(string.Format("Fail to add output column name to {0} ", ComponentMetaData.Name)); throw new Exception(string.Format("Fail to add output column name to {0} ", ComponentMetaData.Name), null); }
Step Seven: PreExecute Method
This executes once at runtime prior to starting the execution.
public override void PreExecute() { IDTSInput100 input = ComponentMetaData.InputCollection[0]; inputBufferColindex = new int[input.InputColumnCollection.Count]; Enumerable .Range(0, input.InputColumnCollection.Count) .ToList() .ForEach(i => { IDTSInputColumn100 inputCol = input.InputColumnCollection[i]; inputBufferColindex[i] = BufferManager .FindColumnByLineageID(input.Buffer, inputCol.LineageID); }); IDTSOutput100 output = ComponentMetaData.OutputCollection[0]; outputBufferColindex = new int[output.OutputColumnCollection.Count]; Enumerable .Range(0, input.InputColumnCollection.Count) .ToList() .ForEach(i => { IDTSOutputColumn100 outputCol = output.OutputColumnCollection[i]; outputBufferColindex[i] = BufferManager .FindColumnByLineageID(input.Buffer, outputCol.LineageID); }); }
Step Eight: ProcessInput Method
This is the most usable method; it will execute repeatedly as the data flow task receives full buffers from the upstream components. Because this component is synchronous, the incoming rows will be added to the output stream at the same time. The method for “Proper Case” text processing will execute for every row in this method. The EndOfRowset property will be used for the identify end of the input data stream.
public override void ProcessInput(int inputID, PipelineBuffer buffer) { IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID); if (!buffer.EndOfRowset) { while (buffer.NextRow()) { for (int x = 0; x < inputBufferColindex.Length; x++) { string ProperCaseData = ""; try { ProperCaseData = InitCap_ProcessText(buffer.GetString(inputBufferColindex[x])); buffer.SetString(outputBufferColindex[x], ProperCaseData); } catch (Exception ex) { //buffer.DirectErrorRow(outputBufferColindex[x], -1, inputBufferColindex[x]); } } } } }
Step Nine: InitCap_ProcessText Method
This is a user-defined method for text processing. Incoming text data will process and prepare the output text in “Proper Case” as required. This function executes another function, “Exceptional_TextProcessing
,” for handling exceptional cases during text processing, but only if the component property “EnableTextProcessing” value is set to true.
private string InitCap_ProcessText(string InputText) { string ProperCaseText = InputText.ToString(); CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture; TextInfo textInfo = cultureInfo.TextInfo; ProperCaseText = textInfo.ToTitleCase(ProperCaseText.ToLower()); if (Convert.ToBoolean(ComponentMetaData.CustomPropertyCollection["EnableTextProcessing"].Value.ToString()) == true) { ProperCaseText = Exceptional_TextProcessing(ProperCaseText); } return ProperCaseText.Trim(); } private string Exceptional_TextProcessing(string ProperCaseText) { CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture; TextInfo textInfo = cultureInfo.TextInfo; string[] ExceptionalCase; string[] ExceptionalCase_Mixed; string TempString; ProperCaseText = " " + ProperCaseText + " "; // tranformation exceptions: words to be lowercased ExceptionalCase = textInfo.ToTitleCase(ComponentMetaData.CustomPropertyCollection["LowerCase"].Value.ToString().ToLower()).Split(','); foreach (string myValue in ExceptionalCase) { TempString = " " + myValue.Trim() + " "; if (ProperCaseText.Contains(myValue)) { ProperCaseText = ProperCaseText.Replace(TempString, TempString.ToLower()); } } // tranformation exceptions: words to be UPPERCASED ExceptionalCase = textInfo.ToTitleCase(ComponentMetaData.CustomPropertyCollection["UpperCase"].Value.ToString().ToLower()).Split(','); foreach (string myValue in ExceptionalCase) { TempString = " " + myValue.Trim() + " "; if (ProperCaseText.Contains(TempString)) { ProperCaseText = ProperCaseText.Replace(TempString, TempString.ToUpper()); } } // tranformation exceptions: words to be Mixed-CASE (business specific) ExceptionalCase = ComponentMetaData.CustomPropertyCollection["MixedCase"].Value.ToString().Split(','); foreach (string myValue in ExceptionalCase) { ExceptionalCase_Mixed = myValue.Split('|'); TempString = " " + textInfo.ToTitleCase(ExceptionalCase_Mixed[0].ToLower()) + " "; if (ProperCaseText.Contains(TempString)) { ProperCaseText = ProperCaseText.Replace(TempString, ExceptionalCase_Mixed[1].ToString()); } } return ProperCaseText.Trim(); }
Step Ten: Deployment
Below are the steps for the deployment of custom SSIS components
Step Eleven: Demo Using SSIS
The steps for using custom components in the SSIS package are given below. The sample package is designed for generating output text data in “Proper Case” (INITCAP).
This article demonstrates all of the steps for the development, configuration, and usability of a solution-oriented custom component written in .Net for an enterprise-level SSIS solution. Although this is a basic example to start with, the SSIS custom component can also be implemented for business-centric requirements.