Export to Azure Blob (via SIC)

[FrankDK]

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:

• Azure.Storage.Blobs
• Azure.Storage.Common
• Azure.Identity
• Azure.Core

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:

1. Are Smart Functions compiled against .NET framework 4.8?
2. Looking at the assemblies already available within the SIC folder, I can see that both Azure.Core and Azure.Identity are there. Can I reference those by just using an import statement, or do I explicit need to add the reference by the full path (c:\program files\onstream software\onestream gateway\azure.core.dll)
3. Can I reference a different version of a specific assembly, even the assembly is already part of SIC, by adding the file to the "referenced assemblies"? Let's say I want to use an earlier version of Azure.Core.dll, but this is already part of SIC

Are there any detailed information on how Smart Functions works/build/called? (whitepapers/docs)

Cheers, Frank

[franciscoamores · Accepted solution]

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.

[franciscoamores · Accepted solution]

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.

[FrankDK]

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

[franciscoamores]

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.

[Krishna]

@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. 

[DanielWillis]

I feel like you've answered your own questions (I think it was question? Sorry if it wasn't).

• If you need a DLL and don't want to provide them to OneStream Support, then you need SIC
• If you need a DLL and are happy to go through OneStream support, you don't need SIC
• If you don't need a DLL and you can access the endpoint over the web, you don't need SIC

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.

[FrankDK]

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.

[DanielWillis]

Oh really. Thanks for the heads up, hadn't seen that noted anywhere.

[Krishna]

@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

[franciscoamores]

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.

[Krishna · Accepted solution]

@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.

[Krishna]

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.