topic Re: Scheduler & Sharepoint in Alteryx Server Discussions

Scheduler & Sharepoint

vscreame — Thu, 24 May 2018 01:14:14 GMT

I receive mutiple Excel files to my Outlook inbox every day that I then use within a few Alteryx workflows. I currently have these Excel files automatically moving from my Outlook inbox to a location within our company network, however this requires me to be logged in to my PC to function properly.

To get around having to keep my PC on, I've set up a Microsoft Flow to move these attachments to our Sharepoint Online site. I can connect to the files on Sharepoint when working in Designer via either the directory tool or the input tool, but when I open my workflow in Scheduler the connections are broken (Invalid Directory), and I cannot determine how to fix them.

Is Alteryx Scheduler able to read Excel files from Sharepoint? Any workarounds for this?

I've tried OneDrive and run into the same problem.

Re: Scheduler & Sharepoint

KevinP — Tue, 17 Oct 2017 22:23:31 GMT

@vscreame at this time we only support connections to SharePoint via the SharePoint List Input and SharePoint List Output connectors included with Designer. As such connections to SharePoint Document Libraries (files and folders) are not supported. This limited support is due to authentication issues that are outside of our control when accessing files resources hosted on SharePoint. These authentication issues typically do not effect manually run workflows, but will cause workflows run from Scheduler or Gallery to fail with an authentication, file/folder not found, or file/folder not accessible error. The root cause of the issue is due to SharePoint incorrectly prompting for credentials even though credentials are provided when accessing the UNC path for the file/folder. To clarify further Scheduler/Gallery runs the workflow as part of the service in session 0 which is a non-interactive session. As such when SharePoint requests credentials in this manner the authentication automatically fails because the credential prompt can't be rendered to the screen or responded to. Based on the information you provided this is the exact issue you are encountering, and the only supported solution to this issue is to store the files you are attempting to access on a standard Windows file share instead of using SharePoint to host the files.

Re: Scheduler & Sharepoint

casperkeilstrup — Wed, 07 Mar 2018 22:35:20 GMT

Really annoying that this is not possible yet! But Thanks for the good answer.

Re: Scheduler & Sharepoint

Jared_Durer — Mon, 20 Aug 2018 14:08:06 GMT

I am also running into this issue. I need the server to be able to pull the data from living excel documents on SharePoint on a scheduled basis. Is Alteryx any closer to including this capability?

Re: Scheduler & Sharepoint

Derangedvisions — Thu, 23 Aug 2018 19:17:44 GMT

Hi @Jared_Durer @vscreame @casperkeilstrup

We resolved this issue by mapping the Sharepoint drive on the VM that the Alteryx server is on. We then use SyncToy 2.1 to “Echo” the files from the networked drive to a folder on the VM.

So as an example:

Map the Sharepoint drive on the VM to X:/sharepoint/examplefilename
once you have installed SyncToy on the Virtual Machine THEN create a “New Folder Pair” in SyncToy

Left Folder:

X:/sharepoint/examplefilename

Right folder: (select somewhere the Alteryx Server can access)

D:\examplefilenameecho

Action: Echo

The Echo will duplicate the files from the Sharepoint drive to a location where the Alteryx Server can “see” it.

Create a bat file to run it(to create a bat file- open notepad and type the following code (or change it to wherever SyncToy is saved)

cd "C:\Program Files\SyncToy 2.1\"

SyncToyCmd.exe –R

Save this as a “.bat” file
Use Task Scheduler (on your server- it's a standard application on the server) to schedule how often you want your bat file to “echo”.

Task Scheduler setup:

Triggers: set up how often you want it to run

e.g. Daily, at 11A.M, Check the box repeat task every 5 minutes for a duration of Indefinitely. Make sure the Enable box is also checked

Actions: select “Start a Program”

Program/Script: select the location you saved the bat file

I hope that makes sense? it's all we could come up with as we use Sharepoint for everything and don't use lists at all. if anyone comes up with anything better- let me know!

so basically when building workflows- i connect to the "D:\examplefilenameecho" (the right folder) which has a duplicate of the files we need in it, and then the scheduler is able to locate them as well.

Re: Scheduler & Sharepoint

Jared_Durer — Fri, 24 Aug 2018 12:44:19 GMT

Thank you DerangedVisions!

It will take some jumping through hoops with our IT department to try this, but your solution looks very promising!

Re: Scheduler & Sharepoint

Jared_Durer — Mon, 17 Sep 2018 15:44:05 GMT

We had SyncToy installed, but it didn't play nicely with our server's Window's install.

However, using the same thought process, we discovered Robocopy (robust copy). It copies the files over to a location on the network that is accessible to both the local machine and the server, and is already built into Windows. All you need to do is set up an event to run before your workflow begins to call the Robocopy command. You don't need a task scheduler set up because it will trigger anytime the workflow is ran... and the workflow can be scheduled on the Gallery.

There is a downside I have not been able to work out yet. Every time I run a workflow with a robocopy command, it is coming back with an error although the copy actually completed successfully. I have done some googling and found that Robocopy doesn't always return a zero for a successful completion, I have not found a way to suppress these false errors in Alteryx yet.

Here are the steps I did to get this to work.

Step 1: Click on an open area on the canvas of your workflow.

Step 2: Click on the "Events" tab on the "Workflow Configuration" window.

Step 3: Check the box to "Enable Events"

Step 4: Click "Add"

Step 5: Set the "Run Event When:" drop down box to "Before Run"

Step 6: In the "Command" textbox, enter "robocopy"

Step 7: In the "Command Arguments (Optional):" textbox, put in the following command, adjusting it to use your sharepoint site and the location you want it set to. (Leave the quotes in)

"\\sharepoint.<your-company>.com\sites\<Folder Path>" "\\<network path>\SharePoint_Replication" /MIR /fft /xo /dcopy:T /XA:H /R:1 /W:1 /V /NP /TEE /XX /LOG:"\\<network path>\SyncLog.txt"

Step 8: Set the timeout to a larger number it took my system about 30 seconds to run the command.

When you next run your workflow, the robocopy command should copy the directory from sharepoint to the network connection before the workflow begins. All you need to do then is have your workflow point to the copy that was just made and use it as an input.

Here is what each of the switch parameters do in Robocopy:

/MIR - (Mirror command) creates a replica of the source folder in the specified destination folder

/fft - FAT File Times (2-second granularity) - This command prevents different file systems from being classified as newer (even when the files aren't newer)

/XO - Exclude older files

/DCOPY:T - copy folder timestamps

/XA:H - Ignore hidden files

/R:1 - Sets the number of retries on failed copies

/W:1 - Wait 1 between retries

/V - Verbose output

/NP - Don't show the progress of operation

/TEE - Output to console window and log file.

/XX - Exclude extra files present in destination folder but not in source. This will prevent deletion of existing files in destination folder.

/LOG:"<file path>\<file name>" - Output status to a LOG file. Overwrites any existing logs.

All of these robocopy parameters (and more) can be found here: https://sumtips.com/how-to/robocopy-command-line-usage-examples-and-switches/

Re: Scheduler & Sharepoint

aoxomoxoa — Fri, 12 Oct 2018 01:49:32 GMT

hello

I would like to stress the need for the ability to write directly to Sharepoint file locations as mentioned above,

without needing a workaround which may or may not require IT approval and deployment.

I am able to write successfully using Designer, but not Server (or scheduler)

Previously, I was told to get IT to open a port or something, but after investigation it appears to be exactly this issue.

I am not interested in Sharepoint Lists which I realize do work in terms of authentication, and for now am not looking to write to some other shared location ..

I need to actually output the .xlsx or .csv types to Sharepoint.

I have also tried ways of calling batch scripts within workflows to produce the files on a network share and then move them, but same underlying issue once I try to reference the Sharepoint location.

Hoping we will see this in a future release, and relatively soon ?

Exploring the robocopy method described above, now .. not optimal, but perhaps as a workaround

Re: Scheduler & Sharepoint

mths_diniz — Sat, 13 Oct 2018 23:39:19 GMT

@Jared_Durer
What should I put on the second network path? Any path on the VM that runs the alteryx server or it can be a mapped network driver mapped in my pc ?

Re: Scheduler & Sharepoint

Derangedvisions — Mon, 29 Oct 2018 18:36:19 GMT

@Jared_Durer @vscreame @aoxomoxoa @casperkeilstrup

We've actually figured a better way to solve this issue- by using the UNC path as your input/output file location.

to find the UNC path:

go to the "properties" section of your mapped Sharepoint drive (right click mapped drive--> properties) in the description field it actually contains the UNC path eg. \\sharepoint\DavWWWroot\Sites\yoursitename\foldername\insertyourfilenamehere

by using this as an input (or output) (it will connect to the Sharepoint site directly)

After testing it I have been able to read/write to Sharepoint - eliminating the need for Synctoy or any other workarounds- to use scheduler you must input run as credentials in the workflow for the schedule to work.

Re: Scheduler & Sharepoint

T_Willins — Fri, 08 Feb 2019 18:41:31 GMT

@Derangedvisions

Do you have a sample workflow you can share? I can get the UNC path to work with SharePoint for Developer run workflows, but I am having issues with getting the workflows to run on Server.

Re: Scheduler & Sharepoint

Derangedvisions — Fri, 08 Feb 2019 22:47:43 GMT

Not at this moment. Are you getting a specific error?

Make sure to add the sharepoint credentials when saving the workflow.
So make sure the credentials are added on the gallery admin page to be used by the workflow.

Re: Scheduler & Sharepoint

JerseyJack — Tue, 02 Apr 2019 14:48:48 GMT

Hi T_Willins did you ever manage to get your Server Workflow able to access a Share Point Document library directly?

We are a heavy Share Point shop so this is a deal breaker for us regarding the Server version and thus the entire product

Re: Scheduler & Sharepoint

JerseyJack — Tue, 02 Apr 2019 14:51:12 GMT

Dear KevinP,

Is it still the case that Alteryx server Workflows cannot access a Share Point Document library directly?

If it can't, is this something that has been pursued with Microsoft?

We are a heavy Share Point shop so this is a deal breaker for us regarding the Server version and thus the entire product

Thanks

Re: Scheduler & Sharepoint

Jared_Durer — Tue, 02 Apr 2019 17:27:39 GMT

@JerseyJack I have made several additions to my work around since I came up with my original approach to copying files out of SharePoint and it is a fairly polished procedure at this point at my company.

Alteryx cannot read and write files directly from SharePoint because it has trouble authenticating.... however, Alteryx can trigger events that can move files out of SharePoint into a location your local computer and the Alteryx server can access.

To do this, you need to use events in your workflow to run tools that are built into Windows. The tools used are called Robocopy (short for robust copy) and net (short for network).

Here is a step by step guide: (Please forgive the redactions... I need to keep my companies file structure secret for security purposes)

1. Identify the SharePoint directory of the file(s) you need to get and copy the URL into notepad

2. Change the url to a format that will work in windows explorer

3. Find a network location that both your local machine and you Alteryx server can access and create a folder there. I usually include SharePoint replication in the folder name.

4. Next... we need to use the network tool to create a temporary mappings to both the SharePoint network address location and the replication network address location. We will do that through Alteryx using the Net command.

4A. Go into Alteryx and click on an open spot in your canvas and select the "Events" tab on the left pane.

4B. Click the "Add" dropdown and select "Run Command..."

4C. In the "Run Event When" drop down, select "Before Run"

4D. In the "Command:" textbox enter "net"

4E. In the "Command Arguments [Optional]" textbox enter: "use v: "\\sharepoint.[your company site].com\[SharePoint network address location]" /persistent:no"

What this will do is temporarily map a network drive to your sharepoint directory (called v:... change it to another letter if v: is in use already). I have found that this step is necessary because it touches the network location... without it, you will sometimes get a network path not found error.

Before we go further though, I usually put in another event to remove the mapping we just created after the workflow is completed.

4F. Repeat step 4A and 4B

4G. In the "Run Event When" drop down, select "After Run"

4H. In the "Command:" textbox enter "net"

4I. In the "Command Arguments [Optional]" textbox enter: "use v: /delete"

5: Repeat all of the steps in step 4 to map a second drive to the replication network address location (I usually use w: for the drive name).

6. Next go to the replication network address location and create a text file in notepad and name it "Robocopy_Batch.bat" (you can call it whatever you want, but this is how I will be referencing it below). Be sure to click the "Save as type:" dropdown and select "All Files"

7. After you have created your batch file, you will need to right click on it and click edit it from this point onward to make any changes. On the first line of the file enter:

" robocopy v:\ w:\ "[your file name with extension]" /MIR /fft /xo /dcopy:T /XA:H /R:1 /W:1 /V /NP /TEE /XX /LOG:"w:\SyncLog.txt" "

This will take a mirror copy of the specified file out of the SharePoint drive (mapped to v:) and save it to the SharePoint replication network address (mapped to w:). It will also create a log file called SyncLog.txt and place it in the SharePoint replication network address

8. On the 2nd line enter: "EXIT /B"

This will force the batch file to exit without errors. If you do the robocopy command directly in Alteryx (as specified in my earlier post)... it will work, but you will get an error... even when it runs successfully... this prevents that error.

9. Save and close the batch file and go back into Alteryx and navigate back to the events tab.

10. Click on the "Add" dropdown and select "Run Command..."

11. In the "Run Event When" drop down, select "Before Run"

12. In the "Command:" text box type: W:\Robocopy_Batch.bat and click ok

12. Reorder your events to make sure the before run net events go before the robocopy event and the after run net events run after the robocopy event.

13. Run the workflow to replicate the files to the replication SharePoint network location.

14. Use the file that is inserted into the replication SharePoint network location for your workflow.

Your done!

You can repeat this entire process in reverse to write files manipulated in Alteryx back to SharePoint if you need to.

This process took a lot of trial and error, but I have been finding myself using it often because there are a lot of cases where tracking and project management files are kept on SharePoint and this allows access to them.

I hope this helps!

Jared

Re: Scheduler & Sharepoint

JerseyJack — Wed, 03 Apr 2019 08:39:56 GMT

Jared,

Many thanks for taking the time to share this detailed & excellent guide. I was aware that a cludgy Robocopy (versed in Robocopy) and re-map based workaround was possible but it's good to hear that someone is using it regularly in the wild and it's tried and tested.

It has some downsides of course. As this isn't checking out the file from Sharepoint there is a window of time, while the file is being used outside of Sharepoint and read/updated, during which a race condition exists. If used for automating critical processes there is a risk there. However, some of that could be mitigated through training etc but not completely removed.

My suspicion is that other non-Sharepoint (I'm trying to avoid using Nintex as building on-top of an already complex platform like Sharepoint seems like asking for trouble) workflow/bpm/ETL are all going to face this issue but I'm still appraising them.

Thanks again Jared

Jack

Re: Scheduler & Sharepoint

Jared_Durer — Thu, 04 Apr 2019 13:07:35 GMT

You are correct. If someone makes a change to the version in SharePoint during the timeframe that the workflow is actually running, there is a risk that the change saved on SharePoint will be overwritten by the copy inserted by Robocopy.

I mitigate that risk my scheduling my workflows to run outside of work hours. The odds of someone updating the spreadsheet on Sunday between 2:00 AM and 2:02 AM is pretty remote. Informing the end users that the possibility exists is another mitigating factor.

I appreciate your pointing that out. I forgot to mention it in the walkthrough.

Jared

Re: Scheduler & Sharepoint

AndrewK — Wed, 29 May 2019 11:28:10 GMT

I can get this to work when I run my workflows locally but when I try to run them using the server it kicks back with an error. Has anyone been successful using UNC file paths to publish to SharePoint using the server?

Re: Scheduler & Sharepoint

T_Willins — Wed, 29 May 2019 16:33:56 GMT

Hi @AndrewK

I have unsuccessfully tried to use a UNC path on Alteryx Server. I believe this is because the UNC path in Developer is using your desktop, which is not the same as the server's "desktop" unless the server is added to your SharePoint site. Still working on a good solution.

Re: Scheduler & Sharepoint

hashiniparam — Thu, 01 Aug 2019 10:26:43 GMT

I tried the same approach, it is wrking in Alteryx Designer but it is not working in server. My UNC path has is something like \\domain.sharepoint.com@SSL\DavWWWRoot\sites\Folder\filename.xlsx. Can you please tell me if the @SSL causing any issues. I have tried prooviding the Alteryx server access to the sharepoint folder as well. Thanks in advance.