This is the SIC code
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
' --------------------------------------------------------------------------------------
---------
' Get SQL connection string
Dim connString As String = APILibrary.GetRemoteDataSourceConnection(datasource)
' Inflate compressed datatable
Dim dt As DataTable = CompressionHelper.InflateJsonObject(Of DataTable)
(New SessionInfo,dtCompress)
If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
' Check data table has been created and is populated
' Create sql connection to DWH
Using sqlTargetConn As SqlConnection = New SqlConnection(connString)
sqlTargetConn.Open ' Open connection
Using bulkCopy = New SqlBulkCopy(sqlTargetConn)
bulkCopy.DestinationTableName = tableName ' DWH table
bulkCopy.BatchSize = 5000
bulkCopy.BulkCopyTimeout = 30
bulkCopy.WriteToServer(dt) ' Bulk copy data table to database table
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 NamespaceThis is the Extensibility code
Imports 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
Try
' SIC Gateway name
Dim sicGatewayName As String = "jl-db-achqa1-gateway"
' SIC remote rule
Dim sicRemoteRule As String = "SIC_Functions"
' SIC remote rule function
Dim sicRemoteRuleFunction As String = "RunOperation"
' 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)
' Compress data table before passing into remote business rule
Dim dtCompress As CompressionResult = CompressionHelper.CompressJsonObject
(Of DataTable)(si, dt, XFCompressionAlgorithm.DeflateStream)
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
Above is the original code, I update it this way:
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.JH_SIC_Test
Public Class MainClass
Public Shared Function RunOperation(datasource As String) As CompressionResult
' -----------------------------------------------------------------------------------------------
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)
Dim dataSet As New DataSet()
Dim dtCompress As CompressionResult = CompressionHelper.CompressJsonObject(Of DataTable)(New SessionInfo, dt, XFCompressionAlgorithm.DeflateStream)
Return dtCompress
End Function
End Class
End Namespace
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
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.JH_EXT_Test
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
'Set Parameters
Dim sicGatewayName As String = "gateway" '
Dim sicRemoteRule As String = "JH_SIC_Test"
Dim sicRemoteRuleFunction As String = "RunOperation"
Dim dtObj(0) As Object ' Create object to store arguments for remote business rule
dtObj(0) = "OLEDB"
' Execute remote business rule
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
MsgLog.AddLine("Status: " & bulkRemoteResults.RemoteResultStatus.ToString)
End If
' Get returned message
Dim returnedMsg As DataTable = CompressionHelper.InflateJsonObject(Of DataTable)(si,bulkRemoteResults.resultDataCompressed)
Dim result As New Text.StringBuilder
For Each row As DataRow In returnedMsg.Rows
result.AppendLine( row("customer_id").ToString() & "," & row("name").ToString() & "," & row("address").ToString() & "," & row("website").ToString() & "," & row("credit_limit").ToString())
Next
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace