05-21-2024 02:39 AM
Greetings OS Integrators,
Going with v8 and SIC for integrations, it is not an option to have 3. party assemblies added to the cloud environment. I am building a integration to push data from OS to an Azure Blob Storage location, and in this case I want to use some of the nice nuget packages (assemblies) that provides an easy way to do this, hereby authentication. I therefore want to use the following assemblies for my solution, that will run on SIC as a Smart function:
As such, I am able to compile my Smart Function against an existing SIC gateway, and able to call the function from within OneStream. But, issues lies in required other dependencies within my code-logic. So my questions are:
Are there any detailed information on how Smart Functions works/build/called? (whitepapers/docs)
Cheers, Frank
Solved! Go to Solution.
05-21-2024 04:43 AM
Hello,
have you considered using SFTP enabled access to the Azure BS? If you access to the BS via the public internet, you don't need SIC with this approach. The client must enable SFTP access for the blob storage and you can upload files using the WinSCP .net methods as you would do with any SFTP Server. if you require private network access, you need SIC but you don't need to use SIC BR as you can connect using the OneStream bound port from an Extender BR or Assembly BR (it's documented in SIC guide).
Inb my experience, if SFTP access for the Blob Storage is an option for your client, it simplifies a lot the integration architecture.
05-30-2024 04:22 AM
@franciscoamores - Appreciated. I get it about SIC for Database and it is working without any issues and same as API call because it does not require any DLL. I am having issues with the Azure BLOB as SFTP. Below is my code and I am getting an error. Not sure what I am doing wrong. Any Help would be appreciated.
SIC Rule
Public Shared Function RunOperation() As Byte()
Dim sftpHost As String = "localhost" ' Replace with your SFTP server address
Dim sftpPort As Integer = "20541"
Dim sftpUsername As String = "user" ' Replace with your SFTP username
Dim sftpPassword As String = "pass" ' Replace with your SFTP password
Dim remoteFilePath As String = "path"
Dim fileBuffer As Byte()
Dim tempFilePath As String = Path.GetTempFileName()
' Setup session options
Dim sessionOptions As New SessionOptions
With sessionOptions
.Protocol = Protocol.Sftp
.HostName = sftpHost
.PortNumber = sftpPort
.UserName = sftpUsername
.Password = sftpPassword
End With
Using session As New Session()
' Connect to the SFTP server
session.Open(sessionOptions)
' Setup transfer options
Dim transferOptions As New TransferOptions With {
.TransferMode = TransferMode.Binary
}
' Download the file to a temporary file
session.GetFiles(remoteFilePath, tempFilePath, False, Nothing)
' Read the temporary file into a memory buffer
fileBuffer = File.ReadAllBytes(tempFilePath)
' Delete the temporary file
File.Delete(tempFilePath)
' Disconnect from the SFTP server
session.Close()
End Using
Return fileBuffer
End Function
Extender Rule
Dim objGatewayDetails As GatewayDetails = BRApi.Utilities.GetGatewayConnectionInfo(si,"azureblob")
' Setup the objects to read Gateway Details from BRAPIs
Dim objRemoteRequestResultDto As RemoteRequestResultDto =BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "AzureBlobSFTP", Nothing, objGatewayDetails.GatewayName,"RunOperation","AzureBlobSFTP", True, 600)
If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.Success) Then
Dim bytesFromFile As Byte()
bytesFromFile = objRemoteRequestResultDto.ObjectResultValue
Dim groupFolderPath As String = BRAPi.Utilities.GetFileShareFolder(si,FileShareFolderTypes.BatchHarvest, api)
Using sw As StreamWriter = New StreamWriter(groupFolderPath &"\Azurefile.csv")
sw.Write(bytesFromFile)
sw.Close()
End Using
Else
brapi.ErrorLog.LogMessage(si, "No data received.")
End If
Error
Summary: Smart Integration Connector Gateway general error at: 'azureblob CompileCacheAndRunRemoteFunction ID: 893621a2-f32a-49a9-872a-2d2b7fe227cb'. SendRelayRequest.
05-21-2024 04:43 AM
Hello,
have you considered using SFTP enabled access to the Azure BS? If you access to the BS via the public internet, you don't need SIC with this approach. The client must enable SFTP access for the blob storage and you can upload files using the WinSCP .net methods as you would do with any SFTP Server. if you require private network access, you need SIC but you don't need to use SIC BR as you can connect using the OneStream bound port from an Extender BR or Assembly BR (it's documented in SIC guide).
Inb my experience, if SFTP access for the Blob Storage is an option for your client, it simplifies a lot the integration architecture.
05-21-2024 05:07 AM
hey Francisco,
thanks, was not aware you could setup BS with SFTP access, so will chase that option. Any experience in regards of large data-volume push/get with BS?
Cheers Frank
05-22-2024 10:35 AM
We did not have any issue. It was working fine. For larger files we are compressing the CSV files as GZ. In v8 you also have the Parquet assembly so you can generate parquet files as well.
05-30-2024 12:09 AM
@franciscoamores - This is helpful. But the SFTP option is only available for BLOB not for file share. my current integration is using Azure file share for loading the TB data load. do I need to go through SIC or I can use the HTTPS file share endpoint, or I need to use SIC, If I am not using SIC still, I have to provide the dll files to OS support and I want to avoid it because I have to do every time if dot net version is changing then the DLL has to be updated.
05-30-2024 01:56 AM
I feel like you've answered your own questions (I think it was question? Sorry if it wasn't).
In reality how often are you going to need to change DLLs? Probably only at the same time as doing a major OS upgrade and I'm not seeing clients rush to get those done either.
For the record, I've had no problems connecting to ADLS blobs and pulling them into OS via Azure's REST API. Granted the files weren't large.
05-30-2024 02:34 AM
As an extra thing to be aware of is, that third party assemblies are not possible to add to the OneStream Cloud instance from version 8. So if third party assemblies are required, then SIC is the only way to go.
05-30-2024 07:35 AM
Oh really. Thanks for the heads up, hadn't seen that noted anywhere.
05-30-2024 04:24 AM
@DanielWillis - I have integration runs without SIC, it downloads file from Azure File share using the Connection String, but it requires DLL to access the file share. Could you please provide me the API method ? Thanks
05-30-2024 04:02 AM
SIC is needed only if:
1) regardless the integration method you take, you need access via private network
2) you want to use 3rd party dll not available in the OS servers (there is a doc with the list of supported dlls in v8)
HTTPs via public internet does not require SIC so you are good to go.
05-30-2024 04:22 AM
@franciscoamores - Appreciated. I get it about SIC for Database and it is working without any issues and same as API call because it does not require any DLL. I am having issues with the Azure BLOB as SFTP. Below is my code and I am getting an error. Not sure what I am doing wrong. Any Help would be appreciated.
SIC Rule
Public Shared Function RunOperation() As Byte()
Dim sftpHost As String = "localhost" ' Replace with your SFTP server address
Dim sftpPort As Integer = "20541"
Dim sftpUsername As String = "user" ' Replace with your SFTP username
Dim sftpPassword As String = "pass" ' Replace with your SFTP password
Dim remoteFilePath As String = "path"
Dim fileBuffer As Byte()
Dim tempFilePath As String = Path.GetTempFileName()
' Setup session options
Dim sessionOptions As New SessionOptions
With sessionOptions
.Protocol = Protocol.Sftp
.HostName = sftpHost
.PortNumber = sftpPort
.UserName = sftpUsername
.Password = sftpPassword
End With
Using session As New Session()
' Connect to the SFTP server
session.Open(sessionOptions)
' Setup transfer options
Dim transferOptions As New TransferOptions With {
.TransferMode = TransferMode.Binary
}
' Download the file to a temporary file
session.GetFiles(remoteFilePath, tempFilePath, False, Nothing)
' Read the temporary file into a memory buffer
fileBuffer = File.ReadAllBytes(tempFilePath)
' Delete the temporary file
File.Delete(tempFilePath)
' Disconnect from the SFTP server
session.Close()
End Using
Return fileBuffer
End Function
Extender Rule
Dim objGatewayDetails As GatewayDetails = BRApi.Utilities.GetGatewayConnectionInfo(si,"azureblob")
' Setup the objects to read Gateway Details from BRAPIs
Dim objRemoteRequestResultDto As RemoteRequestResultDto =BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "AzureBlobSFTP", Nothing, objGatewayDetails.GatewayName,"RunOperation","AzureBlobSFTP", True, 600)
If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.Success) Then
Dim bytesFromFile As Byte()
bytesFromFile = objRemoteRequestResultDto.ObjectResultValue
Dim groupFolderPath As String = BRAPi.Utilities.GetFileShareFolder(si,FileShareFolderTypes.BatchHarvest, api)
Using sw As StreamWriter = New StreamWriter(groupFolderPath &"\Azurefile.csv")
sw.Write(bytesFromFile)
sw.Close()
End Using
Else
brapi.ErrorLog.LogMessage(si, "No data received.")
End If
Error
Summary: Smart Integration Connector Gateway general error at: 'azureblob CompileCacheAndRunRemoteFunction ID: 893621a2-f32a-49a9-872a-2d2b7fe227cb'. SendRelayRequest.
06-12-2024 02:29 PM
All - I was able to fix the issue. I was using incorrect gateway host. Now it is working from Azure file share to OS using SIC.
08-01-2024 04:46 PM
08-05-2024 03:55 PM
@GParreiras - Sure will send you.
08-05-2024 04:03 PM
@Krishna, awesome! Here is my LinkedIn if you want to send it through that channel:
https://www.linkedin.com/in/guilherme-parreiras/
Thanks
08-06-2024 02:17 PM
Are you going to connect through SIC or without SIC to Azure Storage ?
08-06-2024 02:20 PM
We will try without SIC. Not sure if it's possible to use File Storage with SIC; I guess only Blob Storage. However, any example would be appreciated.
Thanks.
08-06-2024 02:33 PM
1. You can connect to Azure File Share using SIC and I have implemented. We have to use memory stream for this approach.
2. If you are connecting without SIC the below is an example. You should create a support ticket to upload the DLL files. Once uploaded you have to ref the libraries. See below the Screenshot
Imports Azure.Storage
Imports Azure.Storage.Files
Imports Azure.Storage.Files.Shares
Imports Azure.Storage.Files.Shares.Models
Imports Azure.Storage.Blobs.Models
Imports Azure.Storage.Blobs
Dim HFL As String = BRApi.FileSystem.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest, api)
Dim connectionString As String = "AzureBlob COnnection String"
Dim shareName As String = "Name of the Share"
'Download the Latest File
Dim shares1 As ShareClient = New ShareClient(connectionString,shareName)
Dim directory1 As ShareDirectoryClient = shares1.GetDirectoryClient(FolderName)
Dim file1 As ShareFileClient = directory1.GetFileClient(fileName)
Dim download As ShareFileDownloadInfo = file1.Download()
'Create FileStream to Download the File to the Batch Harvest Folder
Dim fs As New FileStream(HFL & "\" & fileName, FIleMode.CreateNew)
download.Content.CopyTo(fs)
fs.Close
08-06-2024 02:58 PM
Than you @Krishna! Really appreciate your help!
07-26-2024 09:59 AM
Good Morning.. we are trying to export OS data to our Azure ADLS blob storage. This is a very timely and valuable conversation. If we like to pursue this method, how do we get the SFTP public key from OS? (we are using SAAS v8.2).