Can we copy OS internal table data to external DB using SIC?

[jkim]

Hello,

Our environment is OS Cloud 7.4 version.

I am trying to explore if it is possible to copy OS internal App table data to external DB thru SIC? 

I know there is way to extract OS app table data and save to 'External' DB table with  "BRApi.Database.SaveCustomDataTable" command passing "External' DB location as a parameter. 

We currently extract/read from "Custom" DB connection thru SIC into OS Cloud no problem, but now I want to data to go out bound from OS DB to SIC "Custom" DB.

Thank you.

[FrankDK]

If you already have a connection to an external DB, from where you can read (Select) data, I don't see why you then not should be able to push data (INSERT/UPDATE). A quick test could be done creating an extender business rule and do something like:

'' Code to get custom table data
Dim dt As DataTable = BRApi.Database.GetCustomDataTable(si, "Application", "some-name-of-a-table")
'' Code to write that data back to some matching datatable on an external database
Brapi.Database.SaveCustomDataTable(si, "some-external-db-connection-name",dt,True)
						

[jkim]

Thank you, Frank.

I know you can do that for External DB directly connected to Cloud Environment, but I was asking External DB(or Custom DB) connected thru Smart Integration Connector (SIC).   

[FrankDK]

From a technical perspective, SIC adds a layer of abstraction to the communication between OS and external system, but from a .NET perspective there should not be a difference, as long as the Gateway configuration allows write-back. That being said, I just read that the specific .SaveCustomDataTable is NOT supported, so you would need a different approach, but you have a lot of options, eg, create INSERT/UPDATE sql statement. More info on SIC can be found here: SIC Info 

[jkim]

I saw below section in SIC V8 document page 50, but it is just too high level and no specific detailed explanation.

Does anyone have some example or more detailed steps?

Examples Using Smart Integration
Connector To Perform a Write Back
You can perform a write back using Smart Integration Connector. You can write back to anything
you desire.
1. Specify a Database Gateway Connection.
2. Create an extensibility rule under Application > Business Rules > Extensibility Rules to write
back to the local gateway server. For this example, SIC_BulkCopyExample is used.
3. Specify the name of the Smart Integration Function you're using. In this example, "SIC_
Functions" is used.
4. Set variables as you need per your business rule.
5. Ensure your business rule is connecting to the correct database in your OneStream Local
Gateway Configuration.
Smart Integration Connector Guide 50

[mathieu_cartel]

Hi jkim,

I just did that today. I'm not using the Remote BR, which probably explains the poor performances! But it's a first step:

' my own function that creates an insert statement string from a dataTable variable dt.

Dim insertStatement As String = CreateInsertStatement(si, dt)
BRApi.ErrorLog.LogError(si, XFErrorLevel.Information, insertStatement)

Using dbConn As DbConnInfo = BRApi.Database.CreateDbConnInfo(si, DbLocation.External,"YOUR SIC CONNECTION NAME")
dt = BRApi.Database.ExecuteSql(dbConn, insertStatement, False)
End Using

Fairly straight forward. Test with a small number of records and increase...