Perform a Write Back
You can perform a write back using Smart Integration Connector leveraging the defined credentials to the local gateway dataSource at the Smart Integration Connector Gateway. If the credentials have permission to insert, update, and/or delete records in a remote dataSource, a OneStream business rule could be leveraged to write-back, update, and/or delete data as needed to support a financial process.
Example: The following example shows how to insert rows and columns to a Smart Integration Connector SQL remote database. Other types of databases (ODBC and OLEDB) are not compatible with this example.
Copy
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 = "Northeast_HQ"
' SIC remote rule
Dim sicRemoteRule As String = "update_DB"
' 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
The Extensibility Rule above calls the following Smart Integration Function:
Copy
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 Namespace
