June 17, 2009 at 2:10 pm
Hi,
I need to copy an excel file that is available on a sharepoint site to our sql server box and then import the data into our databases. Is there a simple way to copy the file from the site to our box? Thanks!
June 17, 2009 at 2:49 pm
Do you know the location of the file or do you want to attempt to pull it from the sharepoint api? If you can get the physical location of the file, yes, SSIS can import it. If you are trying to go through the Sharepoint API, you'll most likely end up writing your own source connection adapter.
June 17, 2009 at 3:29 pm
It depends on how your excel file is stored in sharepoint.
If its an item in a document library then it has its own URL, you should be able to just link to it from SSIS - But if its an attachment to another item, I think you'd need to write a .NET program to download it first (and possibly save to temp file on disk) as the first step of your SSIS package.
I guess this is a regular processing requirement and not just a one-off?
June 18, 2009 at 11:28 am
Thanks Guys! I don't know the physical location of the file. But I do have the URL. It looks like an item in the document library. So how do i link it in SSIS? Please help!
June 19, 2009 at 4:06 am
If you only have the URL to the file, then one method is writing a C# CLR procedure which accepts a URL, then goes to get the file, and either writes it to disk, or stores it in a table as a blob.
You CLR proc will need to use System.Net.WebRequest, and if the sharepoint site wants user credentials, then WebRequest can accept a set of credentials too - however it can be a bit unreliable, and you may end up having to add the SSIS Service user onto the sharepoint access list.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply