Forum Discussion

Bella_Yu's avatar
Bella_Yu
New Contributor II
5 days ago

SaveCustomDataTable Gives Error

I want to load OneStream application data to external database. And there is an existing connection to the target external database. But it gives me below error, could you please help? No idea what is wrong.

BTW, "FDH_DEV" is SIC external database connection.

 

'Build a data table with one test line
Dim TestTbl As New DataTable
TestTbl.Columns.Add("Tmt", Type.GetType("System.String"))
TestTbl.Columns.Add("SEnt", Type.GetType("System.String"))
TestTbl.Columns.Add("TEnt", Type.GetType("System.String"))
TestTbl.Columns.Add("SAct", Type.GetType("System.String"))
TestTbl.Columns.Add("SActD", Type.GetType("System.String"))
TestTbl.Columns.Add("TAct", Type.GetType("System.String"))
TestTbl.Columns.Add("TActD", Type.GetType("System.String"))
TestTbl.Columns.Add("SFlw", Type.GetType("System.String"))
TestTbl.Columns.Add("TFlw", Type.GetType("System.String"))
TestTbl.Columns.Add("SIc", Type.GetType("System.String"))
TestTbl.Columns.Add("TIc", Type.GetType("System.String"))
TestTbl.Columns.Add("SU1", Type.GetType("System.String"))
TestTbl.Columns.Add("TU1", Type.GetType("System.String"))
TestTbl.Columns.Add("SU2", Type.GetType("System.String"))
TestTbl.Columns.Add("TU2", Type.GetType("System.String"))
TestTbl.Columns.Add("SU3", Type.GetType("System.String"))
TestTbl.Columns.Add("TU3", Type.GetType("System.String"))
TestTbl.Columns.Add("SU4", Type.GetType("System.String"))
TestTbl.Columns.Add("TU4", Type.GetType("System.String"))
TestTbl.Columns.Add("SU5", Type.GetType("System.String"))
TestTbl.Columns.Add("TU5", Type.GetType("System.String"))
TestTbl.Columns.Add("SU6", Type.GetType("System.String"))
TestTbl.Columns.Add("TU6", Type.GetType("System.String"))
TestTbl.Columns.Add("SU7", Type.GetType("System.String"))
TestTbl.Columns.Add("TU7", Type.GetType("System.String"))
TestTbl.Columns.Add("SU8", Type.GetType("System.String"))
TestTbl.Columns.Add("TU8", Type.GetType("System.String"))
TestTbl.Columns.Add("SAmt", Type.GetType("System.Decimal"))
TestTbl.Columns.Add("TAmt", Type.GetType("System.Decimal"))
TestTbl.Rows.Add("Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test",100,100)


BRApi.Database.SaveCustomDataTable(si, "FDH_DEV", "ods.ONESTREAM_EXTRACT_Src2TgtDashboard", TestTbl, True)

 

Connectors
XFBR
  • MarcR's avatar
    MarcR
    VIP
    5 days ago

    the second field is the dblocation, not sure what "FDH_DEV" is but this should be "App" for the application server. 

    I prefer to create the connection for in a using statement (so it is also actively closed when you are done with it):

    Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
'do your thing here
end using

     

    • Bella_Yu's avatar
      Bella_Yu
      New Contributor II
      5 days ago

       CreateApplicationDbConnInfo is used when loading to application database, I want to load to external database. "FDH_DEV" is SIC external database connection.

      As for the second db location field, I tried database name too, not working.😭 

      • MarcR's avatar
        MarcR
        VIP
        5 days ago

        You can also use BRApi.Database.CreateExternalDbConnInfo (assuming a connection to the external database is already configured in your OneStream environment).

        For the parameter DBlocation in SaveCustomDataTable you can use Framework, Application or External

         

  • sameburn's avatar
    sameburn
    Contributor III
    5 days ago

    Hi Bella_Yu 

    This BRApi is an internal OnesStream function, so will not work for an external table, so this error is expected behaviour 

    To save to an external table you'll need a different approach as per MarcR suggestion you can open a connection to the external database that way and use native SQL commands eg something like INSERT INTO or BULK INSERT, etc (assuming external target database is SQL)

    Hope this helps

    Sam