Conversion from string "ZHqDViYVjgd0xziMpqIoIw==" to type 'Integer' is not valid error in MD5 hash implementation via SSIS

• Slick Johnson

  SSC Enthusiast

  Points: 124

  More actions

  April 11, 2017 at 4:00 pm

  #320383

  I am implementing this MD5 Hash guide to update our ETL processes on our development server: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/69766/ . When I run my package it fails at the Script component stage. Below you will find the VB code I have used from the link above:

  Public Shared Function CreateHash(ByVal data As String) As String
  Dim dataToHash As Byte() = (New UnicodeEncoding()).GetBytes(data)
  Dim md5 As MD5 = New MD5CryptoServiceProvider()
  Dim hashedData As Byte() = md5.ComputeHash(dataToHash)
  RNGCryptoServiceProvider.Create().GetBytes(dataToHash)
  Dim s As String = Convert.ToBase64String(hashedData, Base64FormattingOptions.None)
  Return s
  End Function

  Public Overrides Sub InputSource_ProcessInputRow(ByVal Row As InputSourceBuffer)
  Dim counter As Integer = 0
  Dim values As New StringBuilder

  For counter = 0 To inputBuffer.ColumnCount - 1
  Dim value As Object
  value = inputBuffer.Item(counter)
  values.Append(value)
  Next
  Row.HashValue = CreateHash(values.ToString())
  End Sub

  As for the structure of the data I am feeding into this script. It is a simple OLEDB connection connected to a stage table.  The tables have about 218 columns:
  
( STAGE TABLE
Attribute 1 int not null,
Attribute 2 varchar(5) not null,
Attribute 3 date  not null,
Attribute 4- 218 numeric(18,0)
)

  ---I have 1 test record in my stage table: (1,'A','2016-2-2', the rest are null)

  When I run my SSIS package I receive the following Script Component: Runetime Error:

  Conversion from string "ZHqDViYVjgd0xziMpqIoIw==" to type 'Integer' is not valid.

     at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value)
     at .ScriptMain.InputSource_ProcessInputRow(InputSourceBuffer Row)
     at .UserComponent.InputSource_ProcessInput(InputSourceBuffer Buffer)
     at .UserComponent.ProcessInput(Int32 InputID, String InputName, PipelineBuffer Buffer, OutputNameMap OutputMap)
     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.ProcessInput(Int32 InputID, PipelineBuffer buffer)
     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

  The following errors within the progress tab:

  ----1-----
  [Assigns a record hash value [2]] Error: System.InvalidCastException: Conversion from string "ZHqDViYVjgd0xziMpqIoIw==" to type 'Integer' is not valid. ---> System.FormatException: Input string was not in a correct format.
     at Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value, NumberFormatInfo NumberFormat)
     at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value)
     --- End of inner exception stack trace ---
     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

  ----2----
  [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Assigns a record hash value" (2) failed with error code 0x80004002 while processing input "InputSource" (13). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

  Please let me know why this process is failing? As of now my only thought is because the code does not account for NULL values within the DB.