C-Sharp Script to Derive HashValue for Multiple Columns

The below script help us to create hashvalue which can be used to compare records while inserting/updating rows in a table using SSIS packages.

In script task select the columns you want to consider for deriving hashvalue and copy-paste the below code:


/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline;
using System.Text;
using System.Security.Cryptography;

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

{

private PipelineBuffer inputBuffer;

public override void ProcessInput(int InputID, PipelineBuffer Buffer)

{

inputBuffer = Buffer;

base.ProcessInput(InputID, Buffer);

}



public override void Input0_ProcessInputRow(Input0Buffer Row)

{

var counter = 0;

var values = new StringBuilder();



//loop through input columns

for (counter = 0; counter < inputBuffer.ColumnCount; counter++)

{

object value;

value = inputBuffer[counter];

//add each column value to one big string

values.Append(value);

}

//set output column as results of hash method

Row.HashColumn = CreateHash(values.ToString());



base.Input0_ProcessInputRow(Row);

}



private string CreateHash(string data)

{

//get byte array of long data string

var dataToHash = (new UnicodeEncoding()).GetBytes(data);

//create hash provider and compute hash of byte array

var sha1 = new SHA1CryptoServiceProvider();

var hashedData = sha1.ComputeHash(dataToHash);

RNGCryptoServiceProvider.Create().GetBytes(dataToHash);

//convert results to hexadecimal string (SQL friendly format)

var result = BitConverter.ToString(hashedData).Replace("-", "");

return result;

}

}

C-Sharp Script to get IncrementalDate

The below script helps to get maximum date used for incremental load for the SSIS packages:

In below example, we have "TimeStarted" as a incremental date column. You need to create 2 string variables 'IncrementalDate' and 'NewIncrementalDate' in the package.

In script task set 'TimeStarted' (incrementaldate column) as Readonly variable and copy paste the below code and modify the highlighted text (in Yellow) as per the input column name. On executing the package 'NewIncrementalDate' variable will get updated with MaxIncremantal date.

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

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

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
      DateTime NewIncrementalDate;
      String strNewIncrementalDate;

    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */

        strNewIncrementalDate = Variables.IncrementalDate.ToString();
        NewIncrementalDate = DateTime.Parse(strNewIncrementalDate);
        //MessageBox.Show("Before: " + NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
              
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */

        /*NewMaxEventDate.AddMonths(-1);*/
        //MessageBox.Show("After: " + NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
        Variables.NewIncrementalDate = NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff");

    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
          Add your code here
        */
        try
        {
            if (!Row.TimeStarted_IsNull  && Row.TimeStarted  > NewIncrementalDate)
            {
                //MessageBox.Show("CreateDate: " + Row.createdate.ToString("yyyy-MM-dd hh:mm:ss.fff") + "; NewDate: "
                 //              + NewIncrementalDate.ToString("yyyy-MM-dd hh:mm:ss.fff"));
                NewIncrementalDate = Row.TimeStarted;
                //MessageBox.Show("NewDate: " + NewIncrementalDate.ToString("yyyy-MM-dd hh:mm:ss.fff"));


            }

            Row.etlcreateddate = Variables.ContainerStartTime;
            Row.etllastmodifieddate = Variables.ContainerStartTime;

        }
        catch (Exception ex)
        {
            throw new Exception("Error in ExtractTestSample script.  Message: " + ex.Message);
        }
    }

}

Define Default Value to Date Fields in C Sharp

The below codes populate textboxes (IDs = StartDatePr and EndDatePr) on page load with default values:

protected void Page_Load(object sender, EventArgs e)

{if (!IsPostBack)

{

StartDatePr.Text = DateTime.Now.AddDays(-30).ToString("MM/dd/yyyy");

EndDatePr.Text = DateTime.Now.ToString("MM/dd/yyyy");

}

}