Microsoft Flow - Create Automation to grab files from Blackbaud SFTP?

Christine Gomez

Hi,


Given that there are still a lot of data points to be pulled in to the Power BI Connector, I was thinking of setting up some scheduled exports using Queue to have some of the missing data points dropped onto our Blackbaud SFTP and pulling them into Power BI. It looks like Power BI can't yet access files on SFTP, so I was thinking of trying to use Microsoft Flow to move the files from our Blackbaud SFTP into my OneDrive, where Power BI can then access them and refresh the reports for my end-users. 


I started building an automation in Flow but it errored out. I'm not sure if it's because I didn't add the credentials correctly to access our BB SFTP or if it's just erroring out on the source file path. The error messages mentions that the source file path needs to be 'http" or "https" but I'm not seeing in the knowledgebase any reference to a file path that begins with http or https. 


Is anyone able to get this type of flow to work?


Thanks!

Paul Sinclair

I am very keen to hear the answer to this.  I am currently using a script to pick up the files from SFTP and transferring to our internal network then using a personal gateway to update Power BI.  If this works though I think it would be better.

 

Keith Scott

 


Hi Christine, I have this working albeit with a .mdb file rather than csv but I've attached the connection and triggers/actions used for you. 

Ben Regier

This is a good idea that I hadn't considered before.


I have a basic question, though - are you saving these exports to your personal SFTP share (that you can access through your user profile), or are you using the Tenant File Share that comes up as an option in the file manager when you run an export?  At least in my case, those seem to be separate shares.


Thanks!

Christine Gomez

Keith Scott‍ - Thanks! Knowing that you were able to get the SFTP access to work helped me problem solve my setup and the automation.

Ben Regier‍ - I am currently exporting the files into the Tenant File Share folder. I was under the impression that this was the only location in the BB SFTP that Queue to save files to. 


I was able to get the below automation to work this morning, moving files from BB SFTP (tenant folder) to OneDrive.


 

Ben Regier

Oh sweet, this is cool.


For others following along, you can get the FTP info you need to make the connection by clicking the Database pancake menu in the upper right corner of your screen when you're in RE NXT web view.  Then select Database Options, and then go to the FTP tab.


I used that info to set up the connection in Power Automate, but after that, I still needed to set the Folder in the flow trigger.  I had a little trouble finding the appropriate path, and what I ended up doing was connecting to the FTP share using FileZilla, navigating to the FTP folder and then copying the path and pasting it into my flow trigger.  There's probably an easier way to do that.


So far it's working with manual exports so I just need to set up a scheduled export and test that as well.  This opens a lot of possibilities!

Ellen Smith

This is the kind of solution I have been searching for for a year! But I am not technically skilled enough to figure it out just yet! I have a set of exports and queries that power my BI reports and the connector won't replace them for many reasons.  Right now I queue overnight, download,  save over, refresh.  This is life changing if I can get my IT friends to help me get it done!! Thank you for being brilliant. 

Jarrell Dunson

Hey I know this is late, but can you help me get started (setup of SFTP from RE/NXT to Sharepoint? Also, can't get FileZilla to work)

Thanks

Jarrell

Ben Regier

Jarrell Dunson‍ How far in the process to you get before you run into issues? What sort of error messages are you seeing?

Jarrell Dunson

Ben

I have the ability to manually SFTP into our BB hosted site via FileZilla... by manuall entering info: files.blackbaudhosting.com (with no port number), login, password, and the default remote directory: /21813 ...

With MS Flow, I have been unsuccessful in connecting, whether I use a port number or noe (22, 990)... with the same parameters - I don't really see an error message, just a red bar (see attached) Flow.docx

Ben Regier

Jarrell Dunson‍, the username and host server address that you are using don't look right to me. It could be that these are the credentials to your personal file share, not the tenant file share.


To get the right login information, go to Database Options like so:



In the "FTP access account information" section of the page, it will give you the username, password, server address and port number that you need to connect.

Jarrell Dunson

Ben thanks, but I don't get those options... I only have one dropdown...screen.docx

Ben Regier

Jarrell Dunson‍, sounds like you don't have access to your tenant file share. You'd need to contact your database administrator to talk about your options.


Also, you can post a screenshot directly on the forum using the Image option, rather than embedding them in word documents and attaching those. Saves a few steps and is safer in terms of malware, etc.