Export from SSIS to Sharepoint list file using Visual Studio

KR_24 1 Reputation point
2022-02-11T16:00:54.637+00:00

I have a package that's pulling data from the SQL Server table and exporting it to Sharepoint List. As of now, my current solution has linked the SharePoint drive to my local machine and the package has the path to the excel file stored in my local. When I run this package manually on my local, it works perfectly. However, I get the following error when I deploy it on SSISDB Catalog -
173538-image.png

I understand it's because the shared SSIS server doesn't have access to my file stored locally. Is there any other alternative to export to a SharePoint list from SSIS?
So far, I've tried the following -

  1. Using OData connection - but realized this is only used for getting data from Sharepoint and not exporting into Sharepoint.
  2. Using the SPCRED Sharepoint Drivers -but either I am configuring the list URL incorrectly or it still doesn't seem to work. (any help on this would be appreciated).
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,648 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,454 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. ZoeHui-MSFT 33,051 Reputation points
    2022-02-14T09:53:59.197+00:00

    Hi @KR_24 ,

    As you said that the package runs well in SSDT but fail in SQL Server, please help to run the package in 32bit run-time for a try.

    174016-image.png

    You may also check below link to troubleshoot the issue.

    how-do-i-troubleshoot-ssis-packages-failed-execution-in-a-sql-agent-job

    I'd suggest that you may load the data to local folder first and then use SSIS Script Task to upload the file to Sharepoint.

    I'm not familiar with SP, check sharepoint-online-url to see if you could find the list URL.

    ssis-upload-file-to-sharepoint-document-library-revisited

    Uploading documents to WSS (Windows Sharepoint Services) using SSIS

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments