How to extract OneStream data and load it to an external table – Part II

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