Hello, Can anyone give me a quick roadmap of how to go about uploading an Excel document to a Sharepoint document library from SSIS? I want SSIS to grab the data from SQL, create the excel document locally, then upload to Sharepoint library. Thank you! Dana

There is no built-in functionality in SSIS to do this. How would you do it in a stand-alone .NET application? If you have VB.NET code that works in a Windows Forms application, that same code should work (perhaps with minor modification) in the Script Task in SSIS.

There is a good article about interacting with SharePoint and SSIS here. Although the author was deleting and refreshing lists, I think the fundamentals are pretty similar, and his code may have a function to do, what you need.

Thanks to both of you for your responses!

I was finally able to figure this out. It's a combination of SSIS, SharePoint's web service (list.asmx), and a custom DLL to call the web service. You can't use a web service as a project reference inVS2005 for SSIS. So you need to create a custom DLL that interacts with sharepoint web service for you. Once you have that, GAC it, then leverage SSIS Script Tasks to write the code you need to take the items from the Excel file, save the items to a temp variable (type=object=data table), then batch upload the data table rows into the SharePoint list using the UpdateListItems (within the custom DLL you build). Kind of short explaination, but it can be done....

I am trying to accomplish something very similar, except we need to upload a document into a document library. Does anyone have some sample code for how to make this work? thanks!

Here is a sample powershell script that could easily be adapted:http://www.benherman.com/ftp/copy-sp.ps1It uses FrontPage RPCs because I had a lot of trouble getting the webdav client to work (despite playing with patches, registry updates, and the webclient service).I hope this helps.a

I found it easier in many cases to reverse engineer the sharepoint list data structure (WSS 2.0 - the last free version!) and just connect SSIS to the sharepiont sql server to do my imports. But then again, I'm more ofa db developer and not as fluent with the web as you guys seem to be. SharePoint is a sweetlittle stand-alone web infrastructure when you think about it, comes out of the box withsecurity, user administration, a whole suite of collaborative tools, and interoperability throughweb services and data layer abstractions. I don'tdevelop a single page, and I'm off and running. If you are in version WSS 2.0 and would like some pointers on the data access route, let me know and I'd be glad to help.

lkimble,If your still listening I am interested in your approach.

Ikimble,I'm on MOSS 2007. You are actually creating new list items via SQL? Aren't there a bunch of columns set by the result of the object model or the webservice that you need to factor in?I now need to do this and was thinking of an SSIS package calling a web service or script task using the object model. But If I could do it in SQL I would. Your t-sql code would be valuable.Thanks.

Hey lkimble. I'm very interested in your approach, being a SQL Server developer as well. ANYONE BEEN ABLE TO GET A HOLD OF lkimble? toddmcdaniel AT yahooDOTcom