SalesForce Data comparator

Introduction:

Salesforce is one of the widely used CRM cloud system. Most of the times SalesForce will be the end point for the data and front end browser for the End user. So it is very important to verify that the data that resides inside SalesForce should be in the desired format. To ensure that the data comparison must be required between the source system and the SalesForce database.

I will demonstrate you how can you perform the same by using .Net coding.

Pre-requisite:

Download the patner Web service from SalesForce website and add a reference to the .Net solution.

Code:

 

PrivateSubcompare(srcQueryAsString, sfdcQueryAsString)

DimstTimeAsDateTime = Date.Now()

DimdateColsAsNewList(OfString)()

DimedwExtraAsNewList(OfString)()

DimsfdcExtraAsNewList(OfString)()

DimnumberColsAsNewList(OfString)()

DimboolColsAsNewList(OfString)()

DimdetailMsgAsString = ""

DimshotMsgAsString = ""

Try

detailMsg = "---------------------------------------------------"&vbCrLf&"Summary"&vbCrLf&"Entity: "&[Global].sfdcObjectName&vbCrLf&"Filter applied: "&filterApplied&vbCrLf

 

sfdc = NewDataTable()

AppendTextBox("Comparision starts for " + objectName + "."&vbCr&vbLf&"Fetching EDW records....."&vbCr&vbLf)

 

src = da.GetDataTable(srcQuery, [Global].srcConString)

Ifsrc.Rows.Count = 0 Then

detailMsg = "No EDW records present in the specified condition."&vbCr&vbLf&"Please change the condition and try again."&vbCr&vbLf

AppendTextBox("No EDW records present in the specified condition."&vbCr&vbLf&"Please change the condition and try again."&vbCr&vbLf)

shotMsg = "No EDW records present in the specified condition."

Return

EndIf

AppendTextBox("EDW records fetched:" + src.Rows.Count.ToString() + vbCr&vbLf)

detailMsg = detailMsg&"Total Records read from EDW: "&src.Rows.Count.ToString() &vbCrLf

IfNotlogin() Then

MessageBox.Show("Unable to connect to SFDC.")

Return

EndIf

AppendTextBox("Loged in to SFDC."&vbCr&vbLf)

Dim des AsDescribeSObjectResult = binding.describeSObject([Global].sfdcObjectName)

Dim fields AsField() = des.fields

ForEach field AsFieldIn fields

Iffield.type.ToString() = "boolean"Then

boolCols.Add(field.name)

EndIf

Iffield.type.ToString() = "datetime"Then

dateCols.Add(field.name)

EndIf

Iffield.type.ToString() = "double"Then

numberCols.Add(field.name)

EndIf

Next

binding.QueryOptionsValue = NewQueryOptions()

binding.QueryOptionsValue.batchSize = 2000

binding.QueryOptionsValue.batchSizeSpecified = True

 

AppendTextBox("Fetching SalesForce records....."&vbCr&vbLf)

DimqrAsQueryResult = binding.query(sfdcQuery)

Ifqr.size = 0 Then

detailMsg = "No records present in SalesForce for the specified condition."&vbCr&vbLf&"Please change the condition and try again."&vbCr&vbLf

AppendTextBox("No records present in SalesForce for the specified condition."&vbCr&vbLf&"Please change the condition and try again."&vbCr&vbLf)

shotMsg = "No records present in SalesForce for the specified condition."

Return

EndIf

detailMsg = detailMsg&"Total Records read from SFDC: "&qr.size.ToString() &vbCrLf

Dim con AssObject = qr.records(0)

Dim done AsBoolean = False

Ifqr.size> 0 Then

 

 

ForiAsInteger = 0 Tocon.Any.GetUpperBound(0)

Dim t AsType = GetType([String])

IfdateCols.Contains(con.Any(i).LocalName) Then

                        t = GetType(DateTime)

EndIf

IfnumberCols.Contains(con.Any(i).LocalName) Then

                        t = GetType([Double])

EndIf

sfdc.Columns.Add(con.Any(i).LocalName, t)

Next

WhileNot done

ForiAsInteger = 0 Toqr.records.GetUpperBound(0)

con = qr.records(i)

DimdrAsDataRow = sfdc.NewRow()

For j AsInteger = 0 Tocon.Any.GetUpperBound(0)

IfdateCols.Contains(con.Any(j).LocalName) Then

Ifcon.Any(j).InnerText = ""Then

dr(j) = "01-01-1900"

Else

dr(j) = Convert.ToDateTime(con.Any(j).InnerText)

EndIf

ElseIfnumberCols.Contains(con.Any(j).LocalName) Then

Ifcon.Any(j).InnerText = ""Then

dr(j) = 0

Else

dr(j) = Convert.ToDouble(con.Any(j).InnerText)

EndIf

ElseIfboolCols.Contains(con.Any(j).LocalName) Then

Ifcon.Any(j).InnerText = ""Then

dr(j) = 0

Else

dr(j) = Convert.ToBoolean(con.Any(j).InnerText)

EndIf

Else

dr(j) = con.Any(j).InnerText

EndIf

Next

sfdc.Rows.Add(dr)

Next

Ifqr.doneThen

done = True

Else

qr = binding.queryMore(qr.queryLocator)

 

EndIf

EndWhile

Else

MessageBox.Show("No records found.")

EndIf

AppendTextBox("SalesForce records fetched:" + qr.size.ToString() + vbCr&vbLf)

 

Dim ds AsNewDataSet()

ds.Tables.Add(src)

ds.Tables.Add(sfdc)

ForEachdataRowAsDataRowInds.Tables(0).Rows

Dim where AsString = "[" + ds.Tables(1).Columns(0).ColumnName + "]='" + dataRow(0).ToString() + "'"

DimdrLAsDataRow() = ds.Tables(1).[Select](where)

 

If (drL.Length = 0) Then

edwExtra.Add(dataRow(0).ToString())

 

EndIf

Next

ForEach s AsStringInedwExtra

Dim where AsString = "[" + ds.Tables(0).Columns(0).ColumnName + "]='" + s + "'"

DimdrLAsDataRow() = ds.Tables(0).[Select](where)

ds.Tables(0).Rows.Remove(drL(0))

Next

ForEachdataRowAsDataRowInds.Tables(1).Rows

Dim where AsString = "[" + ds.Tables(0).Columns(0).ColumnName + "]='" + dataRow(0).ToString() + "'"

DimdrLAsDataRow() = ds.Tables(0).[Select](where)

If (drL.Length = 0) Then

sfdcExtra.Add(dataRow(0).ToString())

 

EndIf

Next

ForEach s AsStringInsfdcExtra

Dim where AsString = "[" + ds.Tables(1).Columns(0).ColumnName + "]='" + s + "'"

DimdrLAsDataRow() = ds.Tables(1).[Select](where)

ds.Tables(1).Rows.Remove(drL(0))

Next

DimdataRow_relatedAsDataRow = ds.Tables(0).NewRow()

DimsrcColAsDataColumn = ds.Tables(0).Columns(0)

DimsfdcColAsDataColumn = ds.Tables(1).Columns(0)

DimdataRelationAsNewDataRelation("EquiJoin", srcCol, sfdcCol)

Try

ds.Relations.Add(dataRelation)

Catch e AsException

MessageBox.Show("SFDC is having extra records.")

EndTry

DimkeyMatchedAsInt32 = 0

DimrecsSyncedAsInt32 = 0

Dim flag AsBoolean = True

 

 

 

ForEachdataRowAsDataRowInds.Tables(1).Rows

dataRow_related = dataRow.GetParentRow("EquiJoin")

keyMatched += 1

ForiAsInteger = 0 Tocon.Any.GetUpperBound(0)

IfdateCols.Contains(sfdc.Columns(i).ColumnName) Then

IfTimeZoneInfo.ConvertTimeToUtc(Convert.ToDateTime(dataRow(i))).ToString() <>Convert.ToDateTime(dataRow_related(i)).ToString() Then

addMismatch(objectName, src.Columns(0).ColumnName, dataRow(0).ToString(), src.Columns(i).ColumnName, dataRow_related(i).ToString(), sfdc.Columns(i).ColumnName, _

TimeZoneInfo.ConvertTimeToUtc(Convert.ToDateTime(dataRow(i))).ToString())

flag = False

EndIf

ElseIfnumberCols.Contains(sfdc.Columns(i).ColumnName) Then

Dim d AsDouble

IfdataRow_related(i).ToString() = ""Then

                            d = 0

Else

                            d = Convert.ToDouble(dataRow_related(i))

EndIf

IfConvert.ToDouble(dataRow(i)) <> d Then

addMismatch(objectName, src.Columns(0).ColumnName, dataRow(0).ToString(), src.Columns(i).ColumnName, dataRow_related(i).ToString(), sfdc.Columns(i).ColumnName, _

dataRow(i).ToString())

flag = False

EndIf

ElseIfboolCols.Contains(sfdc.Columns(i).ColumnName) Then

Dim d AsBoolean

IfdataRow_related(i).ToString() = ""Then

                            d = False

Else

                            d = Convert.ToBoolean(dataRow_related(i))

EndIf

IfConvert.ToBoolean(dataRow(i)) <> d Then

addMismatch(objectName, src.Columns(0).ColumnName, dataRow(0).ToString(), src.Columns(i).ColumnName, dataRow_related(i).ToString(), sfdc.Columns(i).ColumnName, _

dataRow(i).ToString())

flag = False

EndIf

Else

IfdataRow(i).ToString() <>dataRow_related(i).ToString() Then

addMismatch(objectName, src.Columns(0).ColumnName, dataRow(0).ToString(), src.Columns(i).ColumnName, dataRow_related(i).ToString(), sfdc.Columns(i).ColumnName, _

dataRow(i).ToString())

flag = False

EndIf

EndIf

Next

If (flag) Then

recsSynced += 1

EndIf

flag = True

Next

detailMsg = detailMsg&"Total Keys Matched between EDW and SFDC: "&keyMatched.ToString() &vbCrLf&"Total Records Synched: "&recsSynced.ToString() &vbCrLf

DimedTimeAsDateTime = Date.Now()

Dim TS AsTimeSpan = edTime - stTime

 

Dim hour AsInteger = TS.Hours

DimminsAsInteger = TS.Minutes

DimsecsAsInteger = TS.Seconds

DimtimeDiffAsString = ((hour.ToString("00") &":") + mins.ToString("00") &":") + secs.ToString("00")

detailMsg = detailMsg&"Start Time: "&stTime.ToString() &vbCrLf&"End Time: "&edTime.ToString() &vbCrLf&"Run Time: "&timeDiff&vbCrLf&"---------------------------------------------------"&vbCrLf

 

textColor = "BLUE"

AppendTextBox(detailMsg)

 

Ifmismatch.Rows.Count> 0 Then

ForiAsInteger = 0 Tomismatch.Rows.Count - 1

'textLen = RichTextBox1.TextLength

textColor = "RED"

AppendTextBox("Key not matched : "&mismatch.Rows(i)(2).ToString() &" ,EDW "&mismatch.Rows(i)(3).ToString() &" : "&mismatch.Rows(i)(4).ToString() &" ,SFDC "&mismatch.Rows(i)(5).ToString() &" : "&mismatch.Rows(i)(6).ToString() + vbCr&vbLf)

Next

'ea.Excelwriter(mismatch, "Mismatch.xlsx", [Global].outputexcelPath)

shotMsg = "Data Consistency Check: Failed"

Else

shotMsg = "Data Consistency Check: Passed"

EndIf

AppendTextBox(vbCrLf)

DimedwExtrapkAsString = "Primary key of EDW Extra Records"&vbCrLf

DimsfdcExtrapkAsString = "Primary key of SalesForce Extra Records"&vbCrLf

 

ForEach s AsStringInedwExtra

edwExtrapk = edwExtrapk& s &vbCrLf

Next

 

ForEach s AsStringInsfdcExtra

 

sfdcExtrapk = sfdcExtrapk& s &vbCrLf

Next

 

IfedwExtra.Count> 0 Then

shotMsg = "Data Consistency Check: Failed"

AppendTextBox(edwExtrapk + vbCr&vbLf)

EndIf

 

IfsfdcExtra.Count> 0 Then

shotMsg = "Data Consistency Check: Failed"

AppendTextBox(sfdcExtrapk + vbCr&vbLf)

EndIf

 

textColor = "BLACK"

Catch e AsException

MessageBox.Show("An unexpected error has occurred: " + e.Message + vbLf + e.StackTrace)

shotMsg = "Data Consistency Check completed with errors."

detailMsg = "Data Consistency Check completed with errors."

Finally

MessageBox.Show(shotMsg)

textLen = 0

mismatch.Clear()

EndTry

EndSub

Splitting a Flat files to small files using SSIS Package

Introduction:

Its a common scenario when you have a huge flat file in GB containing millions of records and you want to split it into small files. There are different ways of doing the same. I will demonstrate you how you can spit the file by using SSIS.

Method-1: By using For each loop, Conditional split and Row Number

Control Flow

 

 As you can see there are two Data Flow Tasks, one for loop container and two flat file connection managers are present.

 

Data Flow Task 1: DFT To get Source Count

 

This data flow is just to get the record count of the Big flat file. It has a Flat file source that reads data from the Big flat file and counts the row by using a row count transformation.

 

Variables

 

 

These are 4 variables:

1. Counter: Used as the Counter in for loop
2. endCounter: Upper bound of the counter in for loop
3. reCount: Store the record count of the Big flat file
4. splitCount: Count of record to be present in each split files

For Loop Container

 

This the condition placed inside for loop container.

 

Data Flow Task 2: Data File Splitter

 

As you can see it fetches the data from the Big flat file and passes it through a Script Component which attach a row number to each of the records.

 

This is the code present inside the Script component:

 

 

Once the row number is attached a conditional split transformation spits the file according to the row number. Following is the condition present inside the conditional split transformation:

 

 

 

After the file get spitted for the first cut it is being saved by a flat file destination. For each cut the flat file connection string changes dynamically and the flat file is saved with a different name. Following is the expression written inside the flat file destination for the Connection string property.

 

Pro

Simple easy to develop.

 

Con

Takes a lot of time.

Method-2: By using Script Component

 Control Flow

 

 Here as you can see there is only one data flow and inside that one flat file source is present. This flat file source fetches all the records from the file and passes it to a script component. The Script component splits the file into multiple flat files. Following code is present inside that:

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.IO;

#endregion

 

 [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    string copiedAddressFile;

    private StreamWriter textWriter;

    private string columnDelimiter = ",";

    private string filepath = @"C:\Work Area\Innovation\Flatfile spit on a fly\test.txt";

    private string[] columns;

 

 

    public override void PreExecute()

    {

       

        IDTSInput100 input = ComponentMetaData.InputCollection[0];

        columns = new string[input.InputColumnCollection.Count];

        for (int i = 0; i < input.InputColumnCollection.Count; i++)

        columns[i] = input.InputColumnCollection[i].Name;

    }

 

    public override void PostExecute()

    {

       

    }

 

   

    public override void AcquireConnections(object Transaction)

    {

       

        

    }

 

    public override void ProcessInput(int InputID, Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer buffer)

    {

        while (buffer.NextRow())

        {

            if (buffer[columns.Length-1].ToString() == "1")

        {

            filepath = @"C:\Work Area\Innovation\Flatfile spit on a fly\test" + Convert.ToString(Convert.ToInt32(buffer[columns.Length - 1]) - 1) + ".txt";

            textWriter = new StreamWriter(filepath, false);

            for (int i = 0; i < columns.Length; i++)

            {

                textWriter.Write(columns[i]);

                if (i != columns.Length - 1)

                    textWriter.Write(columnDelimiter);

            }

            textWriter.WriteLine();

        }

            if (Convert.ToInt32(buffer[columns.Length - 1]) % 2000000 == 1)

        {

            textWriter.Close();

            filepath = @"C:\Work Area\Innovation\Flatfile spit on a fly\test" + Convert.ToString(Convert.ToInt32(buffer[columns.Length - 1]) - 1) + ".txt";

            textWriter = new StreamWriter(filepath, false);

            for (int i = 0; i < columns.Length; i++)

            {

                textWriter.Write(columns[i]);

                if (i != columns.Length - 1)

                    textWriter.Write(columnDelimiter);

            }

            textWriter.WriteLine();

        }

 

            for (int j = 0; j < columns.Length; j++)

            {

                textWriter.Write(buffer[j]);

                if (j != columns.Length-1)

                    textWriter.Write(columnDelimiter);

                else

                    textWriter.WriteLine();

            }

 

            if (buffer[columns.Length - 1].ToString() == "10000000000000")

            textWriter.Close();

        }

    }

}

Pros

Faster to split the file.