In this post, we will look at how to extract data from OneStream and load it to an external table by choosing specific columns from the extracted file
I’m going to repurpose the rule from How to extract OneStream data and load it to an external table
We will cover how to save a DataTable to a relational table (SQL Server), use SQLBulkCopy, and add Column Mappings for SQLBulkCopy.
Here is how it can be done.
Here is the code that I wrote during the video.
Private Sub LoadOSDataToTable (ByVal si As SessionInfo) Try Dim fileName As String = BRApi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.ApplicationIncoming, Nothing).Replace("Incoming", String.Empty) & "\Datamanagement\Export\" & StringHelper.RemoveInvalidNameCharacters(si.UserName, False, False) & "\BI_DATA\Profit.csv" Dim neededColumns As New Dictionary(Of String, String) From {{"Geography","Entity"},{"Scenario", "Scenario"}, {"Currency", "Cons"},{"Activity", "Flow"},{"Time", "Time"},{"Account", "Account"},{"Products", "UD1"}, {"Amount", "Amount"},{"Annotation", "Annotation"},{"Assumptions", "Assumptions"}} Using fileReader As New FileIO.TextFieldParser(fileName) fileReader.TextFieldType = FileIO.FieldType.Delimited fileReader.SetDelimiters(",") Dim header As String() = fileReader.ReadFields() Dim neededIndices As New List(Of Integer) Dim dt As New DataTable() For Each neededColumn In neededColumns neededIndices.Add(Array.IndexOf(header, neededColumn.Value)) dt.Columns.Add(neededColumn.Key) Next dt.Columns.Add("LastUpdatedBy").DefaultValue = si.UserName dt.Columns.Add("LastUpdatedDate",GetType(DateTime)).DefaultValue = DateTime.Now While Not fileReader.EndOfData Dim fields As String() = fileReader.ReadFields() Dim tableFields As New List(Of Object) For Each neededIndex In neededIndices tableFields.Add(fields(neededIndex)) Next dt.Rows.Add(tableFields.ToArray) End While Using dbExtConn As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, "External Tables") BRApi.Database.ExecuteActionQuery(dbExtConn, "DELETE FROM Sample_Profit", True, True) 'BRApi.Database.SaveCustomDataTable(si, "External Tables", "Sample_Profit", dt, True) Using objSQLBulkCopy As New SqlBulkCopy(dbExtConn.ConnectionString) For Each dc As DataColumn In dt.Columns objSQLBulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName) Next objSQLBulkCopy.DestinationTableName = "Sample_Profit" objSQLBulkCopy.WriteToServer(dt) End Using End Using End Using Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Sub