cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Extensibility Rules error

Go to solution
vpidala
New Contributor II

3 weeks ago - last edited 3 weeks ago

I have the existing BR rule to export the region hierarchy which is working for last one year without any issues and I am trying to reuse the BR rule for country hierarchy export purpose. I copied & pasted the BR rules, and I am getting the error below while compling. Can you suggest what are all the possibilities to check these issues?

 

1) Error at line 313: Type 'SessionOptions' is not defined.

2) Error at line 315: 'Protocol' is not declared. It may be inaccessible due to its protection level.

3) Error at line 324: Type 'Session' is not defined.

4) Error at line 329: Type 'PGPLib' is not defined.

5) Error at line 338: Type 'TransferOptions' is not defined.

6) Error at line 339: Type 'TransferOperationResult' is not defined.

7) Error at line 340: 'TransferMode' is not declared. It may be inaccessible due to its protection level.

😎 Warning at line 18: Namespace or type specified in the Imports 'WinScp' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.

9) Warning at line 19: Namespace or type specified in the Imports 'DidiSoft.Pgp' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.

10) Warning at line 20: Namespace or type specified in the Imports 'DidiSoft.Pgp.Exceptions' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.

Labels:
0 Kudos
1 ACCEPTED SOLUTION

Go to solution
aformenti
Contributor II

3 weeks ago

Hi @vpidala ,

Looks like you missing some Custom Imports within the New Business Rule you have created. Look at the original rule here and copy/paste them into the new one. Example:

aformenti_0-1718112550129.png

Also, you might be missing Referenced Assemblies, example:

aformenti_1-1718112605562.png

 

 

 

 

 

 

 

View solution in original post

0 Kudos
10 REPLIES 10

Go to solution
aformenti
Contributor II

3 weeks ago

Hi @vpidala ,

Looks like you missing some Custom Imports within the New Business Rule you have created. Look at the original rule here and copy/paste them into the new one. Example:

aformenti_0-1718112550129.png

Also, you might be missing Referenced Assemblies, example:

aformenti_1-1718112605562.png

 

 

 

 

 

 

 

0 Kudos

Go to solution
vpidala
New Contributor II
In response to aformenti

2 weeks ago - last edited 2 weeks ago

@aformenti while execute the rule using the execute extender, I am getting followig error:

1) Unable to execute Business Rule 'Export_Country_Hierarchy'.  2) Invalid object name 'Country'.

I couldnot able to figure out whats happening really. I have rule using the sqlA1.append, etc?

 

0 Kudos

Go to solution
aformenti
Contributor II
In response to vpidala

2 weeks ago

HI @vpidala,

It could be a lot of things. If you share the business rule you trying to run I could have a look.

Alternatively, if what you trying to do is export the hierarchy in a Parent/Child format in Excel (or CSV), I have recently posted the following post that could help you:

https://community.onestreamsoftware.com/t5/Rules/Create-CSV-with-Parent-Child-relationships-across-E...

 

 

0 Kudos

Go to solution
vpidala
New Contributor II
In response to aformenti

2 weeks ago 

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Linq
Imports Microsoft.VisualBasic
Imports System.Windows.Forms
Imports OneStream.Shared.Common
Imports OneStream.Shared.MTf
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Database
Imports OneStream.Stage.Engine
Imports OneStream.Stage.Database
Imports OneStream.Finance.Engine
Imports OneStream.Finance.Database
Imports WinScp
Imports DidiSoft.Pgp
Imports DidiSoft.Pgp.Exceptions
'Imports System.Text.RegularExpressions

Namespace OneStream.BusinessRule.Extender.Export_Country_Hierarchy
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
			Try
				Dim sFTPFolder As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "MT_Param_Application_sFTP_Folder" )
				Dim sFTPHost As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "MT_Param_sFTP_Host" )
				Dim sFTPPort As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "MT_Param_sFTP_Port" )
				Dim sFTPUsr As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "MT_Param_sFTP_Usr" )
				Dim sFTPPwd As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "MT_Param_sFTP_Pwd" )
				Dim sFTPHostKey As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si, False, "MT_Param_sFTP_HostKey" )
				
				Dim selectionChangedTaskResult As New XFSelectionChangedTaskResult()

				'Get WFCluster Criteria Values to determine which Workflow, Scenario, and WFTime we are working in
			    Dim profileName As String = BRApi.Workflow.Metadata.GetProfile(si, si.WorkfloMTlusterPk.ProfileKey).Name
			    Dim scenarioName As String = ScenarioDimHelper.GetNameFromId(si, si.WorkfloMTlusterPk.ScenarioKey)
			    Dim timeName As String = TimeDimHelper.GetNameFromId(si.WorkfloMTlusterPk.TimeKey)
				'brapi.ErrorLog.LogMessage(si, "profileName = " & profileName & "")
				'brapi.ErrorLog.LogMessage(si, "scenarioName = " & scenarioName & "")
				'brapi.ErrorLog.LogMessage(si, "timeName = " & timeName & "")

				Dim sqlA1 As New Text.StringBuilder 				
					sqlA1.Append("Delete " & Chr(13) & "")
					sqlA1.Append("FROM " & Chr(13) & "")
					sqlA1.Append("[Country] " & Chr(13) & "")
					
				'Process the query
					Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
					Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, sqlA1.ToString, False)
						BRApi.Database.SaveCustomDataTable(si, "App", "Country", dt, False)   
					End Using
					End Using

				Dim sqlA2 As New Text.StringBuilder 		
					

					
					sqlA2.Append("--Declare a table variable, PTWithActivity, to store the record set for Personnel Transfer Activity " & Chr(13) & "") 
					sqlA2.Append("--The datatype defined is from the table which the field is queried from " & Chr(13) & "") 
					sqlA2.Append("DECLARE @SQL01 VARCHAR(MAX);" & Chr(13) & "") 
					sqlA2.Append("DECLARE @SQL02 VARCHAR(MAX);" & Chr(13) & "") 
					sqlA2.Append("DECLARE @DimensionName01 AS VARCHAR(500);" & Chr(13) & "") 
					sqlA2.Append("DECLARE @DimensionName02 AS VARCHAR(500);" & Chr(13) & "") 
					sqlA2.Append("SET @DimensionName01 = 'Entities';" & Chr(13) & "") 
					sqlA2.Append("SET @DimensionName02 = 'Entities'; " & Chr(13) & "") 
					sqlA2.Append("DECLARE @HierarchyName01 AS VARCHAR(500); " & Chr(13) & "") 
					sqlA2.Append("DECLARE @HierarchyName02 AS VARCHAR(500); " & Chr(13) & "") 
					sqlA2.Append("SET @HierarchyName01 = 'CDW_ENTY'; " & Chr(13) & "") 
					sqlA2.Append("SET @HierarchyName02 = 'Mgt_Hierarchy'; " & Chr(13) & "") 
					sqlA2.Append("DECLARE @ListOfAccounts AS VARCHAR(MAX); " & Chr(13) & "") 
					sqlA2.Append("DECLARE @ListOfEntities AS VARCHAR(MAX); " & Chr(13) & "") 
					sqlA2.Append("WITH VAR " & Chr(13) & "") 
					sqlA2.Append("( " & Chr(13) & "") 
					sqlA2.Append("DimensionName01 " & Chr(13) & "") 
					sqlA2.Append(",DimensionName02 " & Chr(13) & "") 
					sqlA2.Append(",HierarchyName01 " & Chr(13) & "") 
					sqlA2.Append(",HierarchyName02 " & Chr(13) & "") 
					sqlA2.Append(") " & Chr(13) & "") 
					sqlA2.Append("AS " & Chr(13) & "") 
					sqlA2.Append("( " & Chr(13) & "") 
					sqlA2.Append("SELECT " & Chr(13) & "") 
					sqlA2.Append("@DimensionName01 AS DimensionName01 " & Chr(13) & "") 
					sqlA2.Append(",@DimensionName02 AS DimensionName02 " & Chr(13) & "") 
					sqlA2.Append(",@HierarchyName01 AS HierarchyName01 " & Chr(13) & "") 
					sqlA2.Append(",@HierarchyName02 AS HierarchyName02 " & Chr(13) & "") 
					sqlA2.Append(") " & Chr(13) & "")
					'--SELECT * FROM VAR
					sqlA2.Append(",VW_DIMPAR AS " & Chr(13) & "") 
					sqlA2.Append("( " & Chr(13) & "") 
					sqlA2.Append("SELECT " & Chr(13) & "") 
					sqlA2.Append("D.Name DimensionName " & Chr(13) & "") 
					sqlA2.Append(",R.DimTypeId " & Chr(13) & "") 
					sqlA2.Append(",R.DimId " & Chr(13) & "") 
					sqlA2.Append(",R.ParentId ParentID " & Chr(13) & "") 
					sqlA2.Append(",P.Name ChildName " & Chr(13) & "") 
					sqlA2.Append(",P.Description ChildDescription " & Chr(13) & "") 
					sqlA2.Append(",P.[DisplayMemberGroupUniqueID] " & Chr(13) & "") 
					sqlA2.Append(",P.[ReadDataGroupUniqueID] " & Chr(13) & "")
					sqlA2.Append(",P.[ReadDataGroupUniqueID2] " & Chr(13) & "") 
					sqlA2.Append(",P.[ReadWriteDataGroupUniqueID] " & Chr(13) & "") 
					sqlA2.Append(",P.[ReadWriteDataGroupUniqueID2] " & Chr(13) & "")
					sqlA2.Append(",R.ChildId MemberID " & Chr(13) & "") 
					sqlA2.Append(",R.SiblingSortOrder " & Chr(13) & "") 
					sqlA2.Append(",D.InheritedDimId " & Chr(13) & "")
					sqlA2.Append(",D.DimMemberSourceTypeId " & Chr(13) & "") 
					sqlA2.Append("FROM " & Chr(13) & "") 
					sqlA2.Append("[Member] P " & Chr(13) & "")
					sqlA2.Append("INNER JOIN [Relationship] R " & Chr(13) & "") 
					sqlA2.Append("ON P.DimTypeId = R.DimTypeId " & Chr(13) & "") 
					sqlA2.Append("AND P.DimId = R.DimId " & Chr(13) & "")
					sqlA2.Append("AND P.MemberId = R.ChildId " & Chr(13) & "") 
					sqlA2.Append("INNER JOIN [Dim] D " & Chr(13) & "") 
					sqlA2.Append("ON P.DimTypeId = D.DimTypeId " & Chr(13) & "")
					'--AND P.DimId = D.DimId --Comment out for extensible dimensionality
					sqlA2.Append("WHERE " & Chr(13) & "") 
					sqlA2.Append("D.Name = (SELECT DimensionName01 FROM VAR) " & Chr(13) & "") 
					sqlA2.Append("OR D.Name IN (SELECT DimensionName02 FROM VAR) " & Chr(13) & "")
					sqlA2.Append(") " & Chr(13) & "") 
					'--SELECT * FROM VW_DIMPAR ORDER BY DimensionName, ParentId
					'--SELECT DimensionName, COUNT(*) RC FROM VW_DIMPAR GROUP BY DimensionName 
					'--SELECT DimensionName, ChildName, COUNT(*) RC FROM VW_DIMPAR GROUP BY DimensionName, ChildName HAVING COUNT(*) >1 --Duplicates would be due to member being in more than one dimension
					sqlA2.Append(",VW_DIMPARCLD0 AS " & Chr(13) & "")
					sqlA2.Append("( " & Chr(13) & "") 
					sqlA2.Append("SELECT " & Chr(13) & "") 
					sqlA2.Append("P.DimensionName " & Chr(13) & "")
					sqlA2.Append(",P.DimTypeId " & Chr(13) & "") 
					sqlA2.Append(",P.DimId " & Chr(13) & "")
					sqlA2.Append(",P.ParentId ParentId " & Chr(13) & "") 
					sqlA2.Append(",CASE " & Chr(13) & "") 
					sqlA2.Append("WHEN M.Name IS NULL THEN P.DimensionName " & Chr(13) & "")
					sqlA2.Append("ELSE M.Name " & Chr(13) & "") 
					sqlA2.Append("END ParentName " & Chr(13) & "")
					sqlA2.Append(",CASE " & Chr(13) & "") 
					sqlA2.Append("WHEN M.Description IS NULL THEN P.DimensionName " & Chr(13) & "") 
					sqlA2.Append("ELSE M.Description " & Chr(13) & "")
					sqlA2.Append("END ParentDescription " & Chr(13) & "") 
					sqlA2.Append(",P.ChildName " & Chr(13) & "") 
					sqlA2.Append(",P.ChildDescription ChildDescription " & Chr(13) & "")
					sqlA2.Append(",P.MemberId ChildId " & Chr(13) & "") 
					sqlA2.Append(",P.SiblingSortOrder ChildSortOrder " & Chr(13) & "")
					sqlA2.Append(",P.[DisplayMemberGroupUniqueID] " & Chr(13) & "") 
					sqlA2.Append(",P.[ReadDataGroupUniqueID] " & Chr(13) & "") 
					sqlA2.Append(",P.[ReadDataGroupUniqueID2] " & Chr(13) & "")
					sqlA2.Append(",P.[ReadWriteDataGroupUniqueID] " & Chr(13) & "") 
					sqlA2.Append(",P.[ReadWriteDataGroupUniqueID2] " & Chr(13) & "")
					sqlA2.Append("FROM " & Chr(13) & "") 
					sqlA2.Append("VW_DIMPAR P " & Chr(13) & "") 
					sqlA2.Append("LEFT OUTER JOIN [dbo].[Member] M " & Chr(13) & "")
					sqlA2.Append("ON M.DimTypeId = P.DimTypeId " & Chr(13) & "") 
					sqlA2.Append("AND M.DimId = P.DimId " & Chr(13) & "")
					sqlA2.Append("AND M.MemberId = P.ParentId " & Chr(13) & "") 
					sqlA2.Append(") " & Chr(13) & "") 
					'--SELECT * FROM VW_DIMPARCLD0 WHERE ChildName = 'BW195'
					'--SELECT DimensionName, COUNT(*) RC FROM VW_DIMPARCLD0 GROUP BY DimensionName 

					sqlA2.Append(",VW_DIMPARCLD AS " & Chr(13) & "")
					sqlA2.Append("( " & Chr(13) & "") 
					sqlA2.Append("SELECT " & Chr(13) & "")
					sqlA2.Append("DimensionName " & Chr(13) & "") 
					sqlA2.Append(",DimTypeId " & Chr(13) & "")
					sqlA2.Append(",DimId " & Chr(13) & "") 
					sqlA2.Append(",ParentId " & Chr(13) & "") 
					sqlA2.Append(",ParentName " & Chr(13) & "")
					sqlA2.Append(",ChildName " & Chr(13) & "") 
					sqlA2.Append(",ChildDescription " & Chr(13) & "")
					sqlA2.Append(",ChildID " & Chr(13) & "") 
					sqlA2.Append(",ChildSortOrder " & Chr(13) & "")
					sqlA2.Append(",CAST(ParentName + ' -> ' + ChildName AS NVARCHAR(500)) AS Lineage " & Chr(13) & "") 
					sqlA2.Append(",[DisplayMemberGroupUniqueID] " & Chr(13) & "") 
					sqlA2.Append(",[ReadDataGroupUniqueID] " & Chr(13) & "")
					sqlA2.Append(",[ReadDataGroupUniqueID2] " & Chr(13) & "")
					sqlA2.Append(",[ReadWriteDataGroupUniqueID] " & Chr(13) & "") 
					sqlA2.Append(",[ReadWriteDataGroupUniqueID2] " & Chr(13) & "") 
					sqlA2.Append("FROM " & Chr(13) & "")
					sqlA2.Append("VW_DIMPARCLD0 " & Chr(13) & "")
					sqlA2.Append(") " & Chr(13) & "") 
					'--SELECT * FROM VW_DIMPARCLD
					sqlA2.Append(",DimensionStructure AS " & Chr(13) & "") 
					sqlA2.Append("( " & Chr(13) & "")
					sqlA2.Append("SELECT " & Chr(13) & "")
					sqlA2.Append("DimensionName " & Chr(13) & "") 
					sqlA2.Append(",DimTypeId " & Chr(13) & "") 
					sqlA2.Append(",DimId " & Chr(13) & "")
					sqlA2.Append(",ParentId " & Chr(13) & "")
					sqlA2.Append(",ParentName " & Chr(13) & "") 
					sqlA2.Append(",ChildSortOrder ParentSortOrder " & Chr(13) & "") 
					sqlA2.Append(",ChildId " & Chr(13) & "")
					sqlA2.Append(",ChildName " & Chr(13) & "") 
					sqlA2.Append(",ChildDescription " & Chr(13) & "")
					sqlA2.Append(",ChildSortOrder " & Chr(13) & "") 
					sqlA2.Append(",0 AS Generation " & Chr(13) & "")
					sqlA2.Append(",Lineage " & Chr(13) & "") 
					sqlA2.Append(",[DisplayMemberGroupUniqueID] " & Chr(13) & "")
					sqlA2.Append(",[ReadDataGroupUniqueID] " & Chr(13) & "") 
					sqlA2.Append(",[ReadDataGroupUniqueID2] " & Chr(13) & "")
					sqlA2.Append(",[ReadWriteDataGroupUniqueID] " & Chr(13) & "") 
					sqlA2.Append(",[ReadWriteDataGroupUniqueID2] " & Chr(13) & "")
					sqlA2.Append("FROM " & Chr(13) & "") 
					sqlA2.Append("VW_DIMPARCLD " & Chr(13) & "")
					sqlA2.Append("WHERE " & Chr(13) & "") 
					sqlA2.Append("( " & Chr(13) & "")
					sqlA2.Append("ParentName = (SELECT DimensionName01 FROM VAR) " & Chr(13) & "") 
					sqlA2.Append("OR ParentName = (SELECT DimensionName02 FROM VAR) " & Chr(13) & "")
					sqlA2.Append(") " & Chr(13) & "") 
					sqlA2.Append("AND " & Chr(13) & "")
					sqlA2.Append("( " & Chr(13) & "")
					sqlA2.Append("ChildName = (SELECT HierarchyName01 FROM VAR) " & Chr(13) & "")
					sqlA2.Append("OR ChildName = (SELECT HierarchyName02 FROM VAR) " & Chr(13) & "") 
					sqlA2.Append(") " & Chr(13) & "")
					'--)SELECT * FROM DimensionStructure 
					sqlA2.Append("UNION ALL " & Chr(13) & "")
					sqlA2.Append("SELECT " & Chr(13) & "") 
					sqlA2.Append("[ParCld].[DimensionName] " & Chr(13) & "")
					sqlA2.Append(",[ParCld].[DimTypeId] " & Chr(13) & "")
					sqlA2.Append(",[ParCld].[DimId] " & Chr(13) & "") 
					sqlA2.Append(",[ParCld].ParentId " & Chr(13) & "")
					sqlA2.Append(",[ParCld].ParentName " & Chr(13) & "")
					sqlA2.Append(",[DS].ChildSortOrder ParentSortOrder " & Chr(13) & "") 
					sqlA2.Append(",[ParCld].ChildId " & Chr(13) & "")
					sqlA2.Append(",[ParCld].ChildName " & Chr(13) & "")
					sqlA2.Append(",[ParCld].ChildDescription " & Chr(13) & "") 
					sqlA2.Append(",[ParCld].ChildSortOrder " & Chr(13) & "")
					sqlA2.Append(",[DS].Generation + 1 AS Generation " & Chr(13) & "")
					sqlA2.Append(",CAST([DS].[Lineage] + ' -> ' + [ParCld].ChildName  AS NVARCHAR(500)) " & Chr(13) & "") 
					sqlA2.Append(",[ParCld].[DisplayMemberGroupUniqueID] " & Chr(13) & "")
					sqlA2.Append(",[ParCld].[ReadDataGroupUniqueID] " & Chr(13) & "")
					sqlA2.Append(",[ParCld].[ReadDataGroupUniqueID2] " & Chr(13) & "") 
					sqlA2.Append(",[ParCld].[ReadWriteDataGroupUniqueID] " & Chr(13) & "")
					sqlA2.Append(",[ParCld].[ReadWriteDataGroupUniqueID2] " & Chr(13) & "")
					sqlA2.Append("FROM " & Chr(13) & "")
					sqlA2.Append("VW_DIMPARCLD [ParCld] " & Chr(13) & "") 
					sqlA2.Append("INNER JOIN DimensionStructure AS [DS] " & Chr(13) & "")
					sqlA2.Append("ON [DS].[DimTypeId] = [ParCld].[DimTypeId] " & Chr(13) & "")
					'--AND [DS].[DimId] = [ParCld].[DimId]
					sqlA2.Append("AND [DS].[ChildId] = [ParCld].[ParentId] " & Chr(13) & "")
					sqlA2.Append(") " & Chr(13) & "") 
					'--SELECT [DimensionName], COUNT(*) RC FROM DimensionStructure GROUP BY [DimensionName]
					'sqlA2.Append("SELECT ParentName,ChildName,ChildDescription FROM DimensionStructure WHERE Lineage LIKE '%Mgt_Hierarchy%' ORDER BY DimensionName, generation " & Chr(13) & "")
					
					'Add Column Names
					sqlA2.Append("--Insert the record set for Personnel Transfer Activity " & Chr(13) & "")
					sqlA2.Append("INSERT INTO Country " & Chr(13) & "")
					sqlA2.Append("SELECT " & Chr(13) & "")
					sqlA2.Append("[DimensionName] AS [HierarchyName] " & Chr(13) & "")
					sqlA2.Append(",[DimTypeId] AS [HierarchyTypeID] " & Chr(13) & "") 
					sqlA2.Append(",[DimId] AS [HierarchyID] " & Chr(13) & "")
					sqlA2.Append(",[ParentID] " & Chr(13) & "")
					sqlA2.Append(",[ParentName] " & Chr(13) & "")
					sqlA2.Append(",[ParentSortOrder]" & Chr(13) & "")
					sqlA2.Append(",[ChildID] " & Chr(13) & "")
					sqlA2.Append(",[ChildName] " & Chr(13) & "")
					sqlA2.Append(",[ChildDescription] " & Chr(13) & "")
					sqlA2.Append(",[ChildSortOrder] " & Chr(13) & "")
					sqlA2.Append(",[Generation] " & Chr(13) & "")
					sqlA2.Append(",[Lineage] " & Chr(13) & "")
					sqlA2.Append(",' ' AS [Parent] " & Chr(13) & "")
					sqlA2.Append("FROM " & Chr(13) & "")
					sqlA2.Append("[DimensionStructure]" & Chr(13) & "")
					sqlA2.Append("WHERE " & Chr(13) & "")
					sqlA2.Append("Lineage Like '%CDW_ENTY -> Country_Hierarchy%'" & Chr(13) & "")				
					'sqlA2.Append("Lineage LIKE 'Country_Hierarchy'" & Chr(13) & "")
					'brapi.ErrorLog.LogMessage(si, "sqlA2=" & sqlA2.tostring & "")

'				'Process the query
'					Dim folderpath As String = BRApi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest,Nothing)
					Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
					Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, sqlA2.ToString, False)
						BRApi.Database.SaveCustomDataTable(si, "App", "Country", dt, False)
'					
					End Using
					End Using
					
					
				Dim folderpath As String = BRApi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest,Nothing)
				Dim dbConn As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
				Dim dtMGMT As DataTable = BRAPi.Database.ExecuteSqlUsingReader(dbConn, "SELECT ParentName As ParentName, ChildName As ChildName, ChildDescription As ChildDescription FROM Country ORDER BY Generation ASC", False)
				If Directory.Exists(folderpath) Then
						Using readFile As StreamWriter = File.CreateText(folderpath & "\Country_Hierarchy" & ".txt")
							Dim icolCount As Integer = dtMGMT.Columns.Count

							For i As Integer = 0 To icolCount - 1
								If Not Convert.IsDBNull(i) Then
									readFile.Write(dtMGMT.Columns(i))
								End If
								If i < icolCount -1 Then
									readFile.Write("|")
								End If
							Next
							
								readFile.Write(readFile.NewLine)
							
							For Each dr As DataRow In dtMGMT.Rows
								For i As Integer = 0 To icolCount - 1
									If Not Convert.IsDBNull(dr(i)) Then
										readFile.Write(dr(i).ToString())
									End If
									If i < icolCount -1 Then
										readFile.Write("|")
									End If
								Next
									readFile.Write("|")	'FAPP-2556 - added by hadi rahal 3/11/2022
									readFile.Write(readFile.NewLine)
							Next
							readFile.Close()
						End Using
					End If
					
				Dim blnRemoveFiles As Boolean = False
				Dim strHarvestPath As String = BRApi.FileSystem.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest,Nothing)
				'BRApi.ErrorLog.LogMessage(si, "Harvest Path: " + strHarvestPath, Nothing)		
				
				Dim sessionOptions As New SessionOptions
				With sessionOptions
					.Protocol = Protocol.Sftp
					.HostName = sFTPHost
					.portNumber = sFTPPort 
					.UserName = sFTPUsr 
					.Password = sFTPPwd 
					.SshHostKeyFingerprint = sFTPHostKey
				End With
							
				
				Using session As New Session
					session.Open(sessionOptions)
					
					
					Dim sourceFiles() As String = IO.Directory.GetFiles(strHarvestPath)
					Dim pgp As New PGPLib()
					Dim batchFolder As String = BRApi.FileSystem.GetFileShareFolder(si, FileShareFolderTypes.Batch,Nothing)
					Dim publicKeyLocation As String = batchFolder & "\0x2C4F9010-pub.asc"
					For Each file In sourceFiles
						'If Path.GetExtension(file) = ".txt" Then
						If Path.GetFileName(file) = "Country_Hierarchy.txt" Then
							Dim strOutputFile As String = strHarvestPath & "\" & "Country_Hierarchy.txt" &".pgp"
							pgp.EncryptFile(file, publicKeyLocation,strOutputFile)
							'BRAPI.ErrorLog.LogMessage(si, file)
							Dim transferOptions As New TransferOptions
							Dim transferResult As TransferOperationResult
							transferOptions.TransferMode = TransferMode.Automatic
							transferResult = session.PutFiles(strOutputFile, "/Output/" & sFTPFolder & "/Metadata/*", blnRemoveFiles, transferOptions)
							transferResult.Check()					
							
						End If
						Next
					
					session.Close()
				End Using

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

This code is perfectly working for region export. so I am reusing it for Country export. error is:1) Unable to execute Business Rule 'Export_Country_Hierarchy'.  2) Invalid object name 'Country'.

0 Kudos

Go to solution
aformenti
Contributor II
In response to vpidala

2 weeks ago

Hi @vpidala ,

Looks like this rule is using a Custom Table Table: 

aformenti_0-1718201449686.png

Do you have this table created within your environment? You can check here towards the end where all the Custom Tables are:

 
 

Snag_19c2137f.png

 You will there likely find the Region Table from the other dimension.

 

 

 

0 Kudos

Go to solution
vpidala
New Contributor II
In response to aformenti

2 weeks ago

Thank you @aformenti . I checked and the table does not exist under the application db. I need to create a one, so I am trying to find out the database and schema details to request DB team create one. May I know how to find the database name & Schema that the existing tables are connected? also, Do I need to create a table only or structure of the table (column names as per the BR, etc)? any suggestions please?

0 Kudos

Go to solution
vpidala
New Contributor II
In response to vpidala

2 weeks ago - last edited 2 weeks ago

Any suggestions on this, I need to create a table to execute the BR rule. I installed the Table data manager and created a table, but the table name is coming as "XF_NameoftheTable" instead of Just table name. Are there any suggestions on this, I need some guidance would really appreciate. 

0 Kudos

Go to solution
aformenti
Contributor II
In response to vpidala

2 weeks ago

HI @vpidala,

Sorry missed your replay. The table is under the Application Database. Yes, you would need to add the required columns with the corresponding types. The easiest way for you to do that would be using Table Data Manager (TDM):

aformenti_0-1718635377921.png

You can download it from Market Place:

https://solutionexchange.onestream.com/dashboard/home/browse

 

 

0 Kudos

Go to solution
vpidala
New Contributor II
In response to aformenti

2 weeks ago

Thank you @aformenti I did install the TDM, and I Created a table name, but the table name is coming with XFC_Tablename. But I need the table name without the XFC_. I don't see the rename option of the table. I have my other tables which was created by some other developer with appropriate names. Any suggestions please?

Go to solution
aformenti
Contributor II
In response to vpidala

2 weeks ago

You can either modify your Business Rule to reference the Table Name with XFC_Country here instead:

aformenti_0-1718640680546.png

Or if not, you will need to create a new Extender Business Rule and run SQL once to create the table with the name you want it. 

0 Kudos
Please sign in! vpidala
Community Resources
Training and Documentation
Additional Resources