Hi Winshuttle Community,
Wanted to post on here about a success we have had recently with archiving submitted Winshuttle Data Files on Foundation.
A little backstory:Our organization is currently on Foundation 12.1. We have been running on Foundation since August 2018 with roughly 45 active users between runners and developers. Some of the huge benefits for us moving from Connect to Foundation were:
1. Having a central repository for scripts and templates and
2. Users submitting their data templates to Foundation for review or for audit purposes
#2 was a huge win for our organization that was significantly lacking audit capabilities for scripts being run in our Production SAP environment. We now had the ability to accurately measure how many records were being processed through Winshuttle AND track down any specific changes that were made for any particular reason (e.g. Bob made a bunch of changes to the material master last month - did he do this through Winshuttle? Which materials did he update?). But along with this capability, came difficulties with storing all of those submitted data files in SharePoint.
As some of you may be aware, SharePoint has a native List View Threshold of 5000 items. Once this limit is exceeded, because of current Winshuttle Foundation architecture, the lists lock and you will be unable to access any files in that list/library. With 45 active users submitting roughly 800-900 data files per month, you can imagine how this would cause an issue. One solution that we used was increasing the List View Threshold to 20,000 which was a temporary fix, but can drastically decrease performance for searching and submitting additional files as that list size grows. Rather than continually increase this list view threshold, we decided to pursue an archiving solution.
The Solution: Archiving
In order to maintain optimal list performance, we decided to utilize an archiving strategy to move old submitted files into a separate library/folder (each library/folder has it's own List View Threshold) and maintain only the most recent 6 months of submitted data files in our "active" Data Files Library.
Because Winshuttle Data File libraries are custom SharePoint lists designed by Winshuttle for SharePoint integration with the Foundation architecture, we could not utilize standard SharePoint functionality for copying/archiving data files, like the "save list as template" functionality. To achieve this capability, we had to instead use a custom built PowerShell script executed on the Foundation SharePoint WFE server. I am attaching the instructions as well as the PowerShell script itself if anyone would like to utilize this for their own organization. It is written in such a way (I believe) that it will function for any organization so long as some of the initial parameters are set accordingly.
About the Script:The primary function of this script is to move files from one SharePoint list/library to another one within the same site collection. Theoretically, it should work across different site collections, so long as they are both housed on the same SharePoint WFE server, but I did not have a chance to test that as we only have the one site collection for our organization.
The script has the capability to look at either the full library level, or one level of folders deep, although it certainly could be adapted to go into as many nested folders as you would like. Our organization did not have a need to go more than one level deep, so the script currently does not account for that.
As indicated in the directions, for ease of copying, both source and destination libraries should NOT require checkout of files in order to be modified (this can be done in the 'versioning settings' for each library), but the script is written in such a way that it can handle if the destination library DOES require checkout in order to be modified.
Lastly, this script will copy certain metadata associated with the original source files, such as "Created By", "Created On", "Modified By", and "Modified On" but can in theory be adapted to include more metadata to suit your needs.
Final Thoughts:I'd like to give a HUGE thank you to the Winshuttle Support team (s/o Bret Harwood) as well as the Winshuttle Engineering team who worked with me to build a basic PowerShell script for this purpose. Because of their help, I was able to adapt and add onto the basic script to create the final version you see today. Since this was such a huge pain point for us, I thought I could share our story with the community so that perhaps it may be of some use to all of you.
Please reach out if you have any questions about the script, about our Foundation architecture, or would like to share your story as well!
------------------------------
Kyle Stengel | Data Solutions Engineer
Patterson Companies | 651-405-4866
------------------------------