This is probably an extremely rare circumstance, so I’m writing it down so I don’t forget how I did this.

I recently built a simple solution for my company called “Work & Win”. Basically it’s a slot machine game that allows one random employee per day to win a variety of pre-determined prizes. Initially, I set up the prizes table in the database so it could be managed through a web UI, which means the application reads from the database to determine which prize to show. However the business said they wanted it to instead be kept in an Excel file stored in SharePoint, and not in the database. Ugh.

So basically I needed to do several things:
1. Read the file in SharePoint
2. Copy the data from the file into the database
3. Do this on a regular basis in case of changes

I tried several options, including opening the file programmatically in Excel, copying the file to the local machine using WebDAV and xcopy, mapping a drive to the SharePoint library using WebDAV, having a scheduled task move the file around, use SSIS to try to consume the file, nothing worked. I finally created a two-step solution outside of SharePoint that did the trick:

First, I created a Windows Service to handle the movement of the file from SharePoint to the local server. Like all things SharePoint, the service needs to be created on and run inside of a SharePoint server. Attempting to reference the SharePoint libraries in a project outside of a SharePoint environment may appear to work, and it will compile, but when it runs you will get all sorts of bizarre errors with no guidance or help.

The service code looks like this:

using System;
using System.Diagnostics;
using System.ServiceProcess;
using Microsoft.SharePoint;
using System.IO;
using System.Timers;

namespace MyService
{
    public partial class Service1 : ServiceBase
    {
        public Timer timer = new Timer(3600000);
        public Service1()
        {
            InitializeComponent();
            
        }

        void timer_Tick(object sender, ElapsedEventArgs e)
        {
            GetWorksheet();
        }

        private void GetWorksheet()
        {
            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (SPSite site = new SPSite("http://<server>/<site>"))
                    {
                        site.AllowUnsafeUpdates = true;
                        using (SPWeb thisweb = site.OpenWeb())
                        {
                            SPList docs = thisweb.Lists["Shared Documents"];
                            SPQuery query = new SPQuery();
                            query.ViewFields = "<FieldRef Name='FileLeafRef' />";
                            query.Query = "<Where><Eq><FieldRef Name='FileLeafRef' /><Value Type='Text'>filename</Value></Eq></Where>";
                            SPListItemCollection collection = docs.GetItems(query);
                            SPFile file = collection[0].File;
                            byte[] byteArray = file.OpenBinary();
                            using (MemoryStream memStr = new MemoryStream())
                            {
                                memStr.Write(byteArray, 0, byteArray.Length);
                            }
                            File.WriteAllBytes(@"C:userspublicdocumentsfilename.xlsx", byteArray);
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                EventLog.WriteEntry("The service has encountered an error: " + ex.Message.ToString());
            }
        }

        protected override void OnStart(string[] args)
        {
            EventLog.WriteEntry("Service started successfully");
            timer.Enabled = true;
            timer.Interval = 3600000;
            timer.Elapsed += new ElapsedEventHandler(timer_Tick);
            GetWorksheet();
        }

        protected override void OnStop()
        {
            EventLog.WriteEntry("Service stopped successfully");
            timer.Enabled = false;
        }
    }
}

Basically, it connects to the SharePoint site, loads the desired library where the file is stored, performs a query to get the file, then copies it to a MemoryStream. The MemoryStream sends it to a byte array, so a File object can save the file locally to the server. The timer is so it gets a fresh copy every hour in case of changes. When setting this up, make sure the service account that will be running this not only has access to both the SharePoint site but also the path where you’re dropping the file, and set this account in the “ProjectInstaller” code behind file like so:

this.serviceProcessInstaller1.Account = System.ServiceProcess.ServiceAccount.User;
this.serviceProcessInstaller1.Password = "<password>";
this.serviceProcessInstaller1.Username = "domain\username";

Step one done.

The next step is to get the data into the database. I used an SSIS package to read the file from the server as an Excel connection & map the necessary fields to the database table. Nice & simple. Just make sure the account that will be running the SSIS package on the server has access to the file on the SharePoint server.

Step two done.

The result is the ability for the application to read a current list of prizes from an Excel spreadsheet maintained in SharePoint, using only a Windows service and an SSIS package.

Sure there are many ways to accomplish this, including programmatically downloading it from SharePoint, using a SharePoint Timer job, Workflow, etc., but this worked for me.