SQL SP to SSIS to SharePoint Online list

  • Brad Allison

    Hall of Fame

    Points: 3540

    Is there a way to take results of a stored procedure, use SSIS to move that data into a SharePoint online list?  I saw some videos and articles online about using an OData connector in SSIS, but when I try the connection in SSIS I get error messages.  Or what might be the best way to do this?

    Thanks for any suggestions

  • jonathan.crawford

    SSCertifiable

    Points: 6590

    There used to be a specific connection to SharePoint fo rSSIS, been a while since I tried to do that. Are you tryin gto create a list item for every record of data? Or just save the report with all results in it?

    You could use the SP to drive an SSRS report, and set up a subscription to save it to the SharePoint location, if you're just dropping the report off.

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • Brad Allison

    Hall of Fame

    Points: 3540

    Thanks for the reply.  I do see that there used to be a connector, but it is not there now.  I have a feeling that their new licensing model for the Power apps might have something to do with it, whereas they are driving me to Azure and not use a Gateway for on-prem data without paying for the premium service.  What I am trying to skate around is just that.  Ultimately I need to create a flow that runs a "stale dating" stored procedure against some data to determine if something is overdue and based on how severely overdue it is, notify the user, their manager, hr, etc...

    The main database resides on prem, but to run a stored procedure out of Flow it has to be a premium license.  Our SharePoint instance is online so if I can get that data moved into a list then I can use Flow to connect to it.  I am thinking I might need to develop a .Net script to run some .exe file, create a csv file and then get that loaded up into the SharePoint list, however as I type this I am thinking I might not need the SharePoint list and have Flow use the csv file as I don't think there is any premium requirement for it.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply