You might get to the bottom of this with some checks for null/empty objects. I added a few to see if it will help spot the error:
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Data.SqlClient
Imports OneStream.Shared.Common
Imports OneStreamGatewayService
Namespace OneStream.BusinessRule.SmartIntegrationFunction.SIC_Functions
Public Class MainClass
' Function to bulk copy a compressed data table to a SQL database table
' Pass in compressed data table, database table name and data source name
Public Shared Function RunOperation(dtCompress As CompressionResult, tablename As String, datasource As String) As String
If dtCompress Is Nothing Then Throw New ArgumentNullException(NameOf(dtCompress))
If String.IsNullOrWhiteSpace(tablename) Then Throw New ArgumentException("Table name cannot be null or empty.", NameOf(tablename))
If String.IsNullOrWhiteSpace(datasource) Then Throw New ArgumentException("Data source name cannot be null or empty.", NameOf(datasource))
' Get SQL connection string
Dim connString As String = APILibrary.GetRemoteDataSourceConnection(datasource)
If String.IsNullOrWhiteSpace(connString) Then Throw New InvalidOperationException("Failed to retrieve a valid connection string.")
' Inflate compressed datatable
Dim dt As DataTable = CompressionHelper.InflateJsonObject(Of DataTable)(New SessionInfo, dtCompress)
If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
Using sqlTargetConn As SqlConnection = New SqlConnection(connString)
sqlTargetConn.Open
Using bulkCopy = New SqlBulkCopy(sqlTargetConn)
bulkCopy.DestinationTableName = tablename
bulkCopy.BatchSize = 5000
bulkCopy.BulkCopyTimeout = 30
bulkCopy.WriteToServer(dt)
End Using
End Using
Else
Throw New Exception("Problem uncompressing data in SIC gateway")
End If
Return $"{dt.Rows.Count} rows bulk inserted into table {tablename}"
End Function
End Class
End NamespaceImports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Namespace OneStream.BusinessRule.Extender.SIC_BulkCopyExample
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
If si Is Nothing Then Throw New ArgumentNullException(NameOf(si))
Try
' SIC Gateway name
Dim sicGatewayName As String = "jl-db-achqa1-gateway"
If String.IsNullOrWhiteSpace(sicGatewayName) Then Throw New ArgumentException("SIC Gateway name cannot be null or empty.", NameOf(sicGatewayName))
' SIC remote rule
Dim sicRemoteRule As String = "SIC_Functions"
If String.IsNullOrWhiteSpace(sicRemoteRule) Then Throw New ArgumentException("SIC remote rule cannot be null or empty.", NameOf(sicRemoteRule))
' SIC remote rule function
Dim sicRemoteRuleFunction As String = "RunOperation"
If String.IsNullOrWhiteSpace(sicRemoteRuleFunction) Then Throw New ArgumentException("SIC remote rule function cannot be null or empty.", NameOf(sicRemoteRuleFunction))
' Create and populate DataTable
Dim dt As New DataTable()
dt.Columns.Add("Scenario", GetType(String))
dt.Columns.Add("Time", GetType(String))
dt.Columns.Add("Entity", GetType(String))
dt.Columns.Add("Account", GetType(String))
dt.Columns.Add("Amount", GetType(Double))
dt.Rows.Add("Actual", "2023M3", "Houston Heights", "Net Sales", 100.25)
dt.Rows.Add("Actual", "2023M3", "South Houston", "Net Sales", 1230.66)
If dt.Rows.Count = 0 Then Throw New InvalidOperationException("DataTable is empty.")
' Compress data table before passing into remote business rule
Dim dtCompress As CompressionResult = CompressionHelper.CompressJsonObject(Of DataTable)(si, dt, XFCompressionAlgorithm.DeflateStream)
If dtCompress Is Nothing Then Throw New InvalidOperationException("Failed to compress DataTable.")
Dim dtObj(2) As Object ' Create object to store arguments for remote business rule
dtObj(0) = dtCompress ' compressed datatable
dtObj(1) = "SIC_WriteBack" ' remote database table name
dtObj(2) = "RevenueMgmt" ' remote data source name
' Execute remote business rule to bulk copy to target table
Dim bulkRemoteResults As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, sicRemoteRule, dtObj, sicGatewayName, sicRemoteRuleFunction, String.Empty, False, 600)
' Get result status
If bulkRemoteResults.RemoteResultStatus <> RemoteMessageResultType.RunOperationReturnObject Then ' Check if successful
' Failed, do something
BRAPi.ErrorLog.LogMessage(si, "Failed with status:" & bulkRemoteResults.RemoteResultStatus.ToString)
End If
' Get returned message
Dim returnedMsg As String = CompressionHelper.InflateJsonObject(Of String)(si, bulkRemoteResults.resultDataCompressed)
BRAPi.ErrorLog.LogMessage(si, returnedMsg)
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
Thx Robb,
let me take a look!
