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)
		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
			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("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
			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)
					objSQLBulkCopy.DestinationTableName = "Sample_Profit"
				End Using
			End Using
		End Using
	Catch ex As Exception
		Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
	End Try			
End Sub	

Leave a comment

Your email address will not be published. Required fields are marked *