Forum Discussion
Solved
Bulk Load DataTable into External Database Table
I call a data adapter and store results in DataTable in below snippets. How can I bulk load OneStream data in this DataTable to external database table? External database connection is ready and working in below method.
Using dbConnApp As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, externalConnectinoName)
brapi.Database.ExecuteSql(dbConnApp, sqlQuery, False)
Using ds As DataSet = brapi.Dashboards.Process.GetAdoDataSetForAdapter(si, isSystemLevel, workspaceID, adapterName, resultsTableName, dctVars)
If ds.Tables.Count > 0 Then ' If there are >0 tables in the results
Using dt As DataTable = ds.Tables(0).Copy()
'Remove unsed columns
dt.Columns.Remove("Cube")
dt.Columns.Remove("TransformationRuleProfile")
For Each Col As DataColumn In dt.Columns
BRApi.ErrorLog.LogMessage(si, Col.ColumnName)
Next
Return dt
End Using
End If
End Using
3 Replies
Bella_Yu - Here is an example in C# but you need to tweak. This will understand the code.
// DataTable Variable string tm = "TEMPDB"; //string WFP = |WFProfile|; // Create a new SqlConnection for source database DbConnInfo SrcExtConn = BRApi.Database.CreateExternalDbConnInfo(si,SrcConn); // Creating the data table DataTable dataTable = new DataTable(tm); // Executing the SQL from the source table dataTable = BRApi.Database.ExecuteSql(SrcExtConn,sql,false); //Add WFScenario,WFProfile,WFTime to Data Table DataColumn WFProfileName = new DataColumn("WFProfileName",typeof(string)); WFProfileName.DefaultValue = BRApi.Workflow.Metadata.GetProfile(si,si.WorkflowClusterPk).Name; //WFProfileName.DefaultValue = "PLP_US.PeoplePlanning_Employee_Register"; dataTable.Columns.Add(WFProfileName); DataColumn WFT = new DataColumn("WFT",typeof(string)); WFT.DefaultValue = BRApi.Finance.Time.GetNameFromId(si,si.WorkflowClusterPk.TimeKey); dataTable.Columns.Add(WFT); DataColumn WFS = new DataColumn("WFS",typeof(string)); WFS.DefaultValue = "PLP_Budget"; dataTable.Columns.Add(WFS); // Establishing the connection to the Application DB DbConnInfo TgtExtConn = BRApi.Database.CreateApplicationDbConnInfo(si); // Convert the connection to String string FtgtExtConn = TgtExtConn.ConnectionString; // Establishing the Dest Connection using (SqlConnection connection = new SqlConnection(FtgtExtConn)) { connection.Open(); // Create the reader object to read the data from data table. using (DataTableReader reader = dataTable.CreateDataReader()) { // Create sqlbulkcopy object to copy the data from source to target using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { // Set the destination table name bulkCopy.DestinationTableName = desttable; // Map the DataTable columns to the destination table columns bulkCopy.ColumnMappings.Add("RegisterID", "RegisterID"); bulkCopy.ColumnMappings.Add("RegisterIDInstance", "RegisterIDInstance"); bulkCopy.ColumnMappings.Add("WFProfileName", "WFProfileName"); bulkCopy.ColumnMappings.Add("WFS", "WFScenarioName"); bulkCopy.ColumnMappings.Add("WFT", "WFTimeName"); // Copy the data from the DataTable to the destination table bulkCopy.WriteToServer(reader); } } }Thanks, I'll give it a try!
