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