Upload Excel to Sharepoint library using SSIS

Question

Upload Excel to Sharepoint library using SSIS

sqlfriend

SSC Guru

Points: 52781
More actions
January 25, 2018 at 3:41 pm

#343430

I tried to use SSIS package to get data from a SQL database, then exported to an excel file.
Then copy the excel file to a sharepoint document library, and overwrite it if it exists.
I am using SQL server 2016, cannot find a good task to use to complete this. I am a SQL developer, not knowing much about .net or C#.
Any suggestions?
Thanks,

Viewing 8 posts - 1 through 7 (of 7 total)

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

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 1

use SSRS, set up the report and schedule it.

But, otherwise, you used to have to have a special file connection from codeplex to pull from/to SharePoint, not sure if that's still the case? https://msdn.microsoft.com/en-us/library/hh368261.aspx

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

KingswaySoft SSC Rookie Points: 38 More actions · Answer 2

If third-party tools are an option our solution can handle this quite easily. We have a Premium Excel Destination (as well as source) available in our SSIS Productivity Pack which supports our SharePoint Connection Manager. We also offer a Premium File Transfer Task (available in both SSIS Productivity Pack and our SharePoint toolkit) to assist with transferring of files which also supports our SharePoint Connection Manager.

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

Thanks!
At this time I am not interested to purchase any third party tool to accomplish this.

I tried to install the sharepoint adapter from codeplex site, I download the newest for SQL 2016.
But after install I cannot find the components in SSIS toolbox of Visual Studio 2017.
What should I do?

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 4

do you have an available SSRS server?

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

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

Yes, we have SSRS server.

But for this case, we only want to put the doc to sharepoint library.
It is used for our network department sharepoint library .
we pull some computer information from SQl server and export to excel and push to sharepoint document library.
Disaster recovery documents.

Thanks,

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 6

right, but that's basically exactly what an SSRS subscription does. run a report from SQL, export in some format, save it somewhere. Think you should use that avenue, since it's available. You can hide the report so users don't see it to run it, or put it in a subfolder that they don't have access to on the server.

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

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

Does the report subscription can push the excel file to a sharepoint document library?

We here use sharepoint a lot, we want this document to be pushed to sharepoint library as other disaster recovery documents.