So I first wrote about Alteryx last year, with the following post, when they invited me to speak at a local joint event with Tableau. If you’re unfamiliar with Alteryx, I suggest hitting the Google because it can make your life very easy.
At my company, we use a lot of MS SQL Server and all that it bundles. This includes SQL Server Integration Services. SSIS is a powerful ETL tool that we leverage to move large amounts of data between systems. But every time I launch Visual Studio and try to make a simple task, I inherently receive error messages that I have no idea what they mean. Yes, SSIS can do a great deal, but it feels like unless you have months and months of training, it is not a friendly and intuitive tool.
My task was to update tables in my SQL datamart that were once stored on a Lotus Notes database but now are in SharePoint as lists. I don’t know why I even opened Visual Studio to attempt this. But needless to say, it quickly frustrated me and then I thought, “Hmm, wonder if Alteryx can connect to SharePoint lists.” Sure enough, it can! I can’t stress how easy it was for me to build a module that would download the data from the each list and push it to my SQL datamart.
So here’s what I did. First, go to the Connectors tools and drag SharePoint List Input tool to your canvas.
Next, complete the Properties for the tool. The SharePoint URL is everything before the /Lists in your url. Input your User Name and Password. Then pick the List you want from the drop down.
I recommend sticking a Browse tool to check you’re correctly downloading what you need.
I then used the Select tool to only capture desired data, rename columns and change their data types. The Select tool is fantastic. It let’s you really narrow down what you need and how you need it. Have you ever done a Transformation task in SSIS? What a pain in the butt.
Finally, each list has an Output tool to my SQL datamart. I really enjoy the flexibility outputting to SQL provides. Notice how you can add Pre and Post Create SQL statements into your module to save you time elsewhere.
I repeated the process above six times, one for each list. Here’s what my module looks like when it was done. Clean and easy!
This was such a great quick win for me, I wanted to share with you all.