Forum Discussion

zwc's avatar
zwc
New Contributor
2 years ago

SIC gateway connection to export OS table to SQL server

Hi

Can anyone advice how to push OS data back to SQL server by using SIC gateway?

We setup the SIC to import data from SQL servers to OS successfully in SIC business rule, but no idea how to push back data from OS to SQL by the SIC.

Business rules created in Extensibility do not work like BRApi.Database.SaveCustomDataTable function.

Thanks

 

Data Integration
  • NicolasArgente's avatar
    NicolasArgente
    Valued Contributor
    2 years ago

    Hi zwc 
    Unfortunately, I do not have an SIC to test/check. I am not aware that some BRapi are not working anymore.
    Can you paste your rule here? Have you tried BRApi.Database.ExecuteActionQuery or ExcecuteSQL ?
    Thanks

  • zwc's avatar
    zwc
    New Contributor
    2 years ago

    Hi Nicholas

    Sorry, I made some confusion. Below is what I want to achieve.

    1. Extract data by fdx and copy to a datatable variable finalTable

    2. Tried to set up a connection in Extensibility BR to our company sql database through SIC (SIC = "DWH_AWS1DNA01")

    3. Save Data in finalTable to a Sql table dbo.OneStream_Table

    I have successfully put data in the finalTable, but cannot save data to sql table. The codes I used are

    Dim connectionString = "DWH_AWS1DNA01"    --SIC connector name set up to pull out data from sql
    Using dbConnExt As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, connectionString) 
    If Not finalTable Is Nothing Then 
    BRApi.Database.SaveCustomDataTable(si, connectionString, "Test_Datawarehouse.dbo.OneStream_Table", finalTable, True)
     
    End If
    End Using
     
    The company sql database is on AWS cloud, and the same SIC can pull out data from sql to OS by a sql query.  
    Thanks
    • mathieu_cartel's avatar
      mathieu_cartel
      New Contributor II
      2 years ago

      Hi zwc

      Here is the code I use and it works:

      Using dbConn As DbConnInfo = BRApi.Database.CreateDbConnInfo(si, DbLocation.External,"[[your SIC Connection name]]")
      '.CreateCustomExternalDbConnInfo(si, DbProviderType.Gateway, "GRDWDB")
      dt = BRApi.Database.ExecuteSql(dbConn, insertStatement, False)

      End Using

      The insertStatement variable is a string that contains an insert statement. But your problem, I believe, is the dbConnInfo.

      Cheers!

      • mathieu_cartel's avatar
        mathieu_cartel
        New Contributor II
        2 years ago

        I tried with SaveCustomDataTable and received a message saying it is not compatible with SIC. So for now, my best answer is with the insert statement which will only work with a small number of records