SSIS interaction with Sharepoint
Have you tried UNCs? \\server\site\subsite\Shared Documents? You should be able to use the Foreach Loop container with the File Enumerator to loop over all the files. If you use the full path option, you'll get all the subfolder info you want. Use the File System Task to move files. Talk to me now on
May 22nd, 2012 11:52pm

Hi all Having an issue with interacting with Sharepoint via SSIS I have been asked to create a package that will "archive" files in a Sharepoitn document library. There is a doc lib called "Reports" which has a large number of sub folders Within each subfolder (eventually) there is a folder called "Current" and another called "Archive" the purpose of the routine is to loop through all the files in the "Current" folder and if the date of the file is more than x days old then move it to the "Archive" folder Pretty simple so far..... I have been looking at 3 methods for doing this but each seems to have a block attached to it: Method 1 - use the Sharepoint Source control from codeplex I can successfully implement this to get a full list of all files within the Reports doc library, however, it does not tell me which folder the file actually resides in - it returns the file name but the only full url it returns is a "redirect" url which doesn't tell me where the file is actually stored (i.e. it may well already have been placed into the archive folder. There seems to be a property of the file called EncodedAbsUrl which would give me the full path and therefore teh folder the file sits in but this doesn;t seem to be returned by the component - can it be? Method 2 - Use .NET script and xml queries to get the list of files I can semi successfully implement this - it uses the Lists.asmx web service and xml to get a list of files - there is a note in the code that it should recursively get all files in the doc lib but it only gets one folders worth of files: Dim queryOptionsDoc As New XmlDocument() Dim QueryOptions As XmlNode = AddXmlElement(queryOptionsDoc, "QueryOptions", "") AddXmlElement(QueryOptions, "Folder", documentLibraryName) AddXmlElement(QueryOptions, "IncludeMandatoryColumns", "TRUE") ' this element is the key to getting the full recusive list Dim node As XmlNode = AddXmlElement(QueryOptions, "ViewAttributes", "") AddXmlAttribute(node, "Scope", "Recursive") This does return the EncodedAbsUrl but doesn't get the full list of files (I can provide full code if required) Method 3 - Use the ASP.NET method of referencing sharepoint I have seen a number of pieces of code utilise the SPWeb and SPSite types but they require a reference to Imports Microsoft.Sharepoint which I do not have available. I have not got this method working at all - would any of the web services available allow me to make use of the SPWeb / SPSite objects? Something like: SPSite mysite = new SPSite("http://localhost") // replace localhost with the URL of your site SPWeb myweb = mysite.OpenWeb(); SPFolder mylibrary = myweb.Folders["Name of library"]; SPFileCollection files = mylibrary.Files; So I can either get all the files but without folder references (method 1), get only a small set of the files but with folder references (Method 2) or not get anything working at all (but if I can solve the root of the problem i.e. referencing Sharepoint itself I should be able to write the rest of the code easily). Any thoughts? Rgds Geoff ---------------------------------------------------------- Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.
Free Windows Admin Tool Kit Click here and download it now
May 23rd, 2012 8:11pm

Have you tried UNCs? \\server\site\subsite\Shared Documents? You should be able to use the Foreach Loop container with the File Enumerator to loop over all the files. If you use the full path option, you'll get all the subfolder info you want. Use the File System Task to move files. Talk to me now on
May 24th, 2012 12:10am

Hi Todd - sorry but that's not going to work for me - I've been told by the team that maintain Sharepoint here that they do not want to expose the UNC path for the server for this kind of work - apparently it's not reliable if run from server (I have no knowledge to back up or dispute this claim) So I'm back to being able to get a full list but without the folder names or getting the folder names but not a full list If anyone knows xml querying, maybe they can point me in the right direction - here is the code that gets the folder names but only gets files from 1 folder in teh doc lib: Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.Web.Services Imports System.Xml Imports System.Web Imports System.Collections.Generic Imports ST_a000535194da43509d40f00cf1a82fdf.vbproj.Site Imports ST_a000535194da43509d40f00cf1a82fdf.vbproj.Lists1 Imports System.Text Imports System.Net <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _ <System.CLSCompliantAttribute(False)> _ Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum Public Sub Main() Dim siteUrl As String = "https://base/site/url" Dim documentLibraryName As String = "Reports" Dim wsList As New Lists1.Lists() wsList.Credentials = System.Net.CredentialCache.DefaultCredentials wsList.Url = siteUrl & "/_vti_bin/lists.asmx" ' get a list of all top level lists Dim allLists As XmlNode = wsList.GetListCollection() ' load into an XML document so we can use XPath to query content Dim allListsDoc As New XmlDocument() allListsDoc.LoadXml(allLists.OuterXml) allListsDoc.Save("c:\allListsDoc.xml") Dim ns As New XmlNamespaceManager(allListsDoc.NameTable) ns.AddNamespace("d", allLists.NamespaceURI) ' now get the GUID of the document library we are looking for Dim dlNode As XmlNode = allListsDoc.SelectSingleNode("/d:Lists/d:List[@Title='" & documentLibraryName & "']", ns) ' obtain the GUID for the document library and the webID Dim documentLibraryGUID As String = dlNode.Attributes("ID").Value Dim webId As String = dlNode.Attributes("WebId").Value ' create ViewFields CAML Dim viewFieldsDoc As New XmlDocument() Dim ViewFields As XmlNode = AddXmlElement(viewFieldsDoc, "ViewFields", "") AddFieldRef(ViewFields, "GUID") AddFieldRef(ViewFields, "ContentType") AddFieldRef(ViewFields, "BaseName") AddFieldRef(ViewFields, "Modified") AddFieldRef(ViewFields, "EncodedAbsUrl") viewFieldsDoc.Save("c:\viewFields.xml") ' create QueryOptions CAML Dim queryOptionsDoc As New XmlDocument() Dim QueryOptions As XmlNode = AddXmlElement(queryOptionsDoc, "QueryOptions", "") AddXmlElement(QueryOptions, "Folder", documentLibraryName) AddXmlElement(QueryOptions, "IncludeMandatoryColumns", "TRUE") ' this element is the key to getting the full recusive list Dim node As XmlNode = AddXmlElement(QueryOptions, "ViewAttributes", "") AddXmlAttribute(node, "Scope", "Recursive") queryOptionsDoc.Save("c:\queryOptions.xml") ' for debug ' obtain the list of items in the document library Dim listContent As XmlNode = wsList.GetListItems(documentLibraryGUID, Nothing, Nothing, ViewFields, Nothing, QueryOptions, _ webId) Dim xmlResultsDoc As New XmlDocument() xmlResultsDoc.LoadXml(listContent.OuterXml) ns = New XmlNamespaceManager(xmlResultsDoc.NameTable) ns.AddNamespace("z", "#RowsetSchema") xmlResultsDoc.Save("c:\listContent.xml") Dim rows As XmlNodeList = xmlResultsDoc.SelectNodes("//z:row", ns) For Each row As XmlNode In rows MsgBox(row.Attributes("ows_ContentType").Value & " " & row.Attributes("ows_GUID").Value & " :: " & row.Attributes("ows_BaseName").Value) Next End Sub Public Shared Function AddXmlElement(ByVal parent As XmlNode, ByVal elementName As String, ByVal elementValue As String) As XmlNode Dim element As XmlNode = parent.AppendChild(parent.OwnerDocument.CreateNode(XmlNodeType.Element, elementName, "")) If elementValue <> "" Then element.InnerText = elementValue End If Return (element) End Function Public Shared Function AddXmlElement(ByVal parent As XmlDocument, ByVal elementName As String, ByVal elementValue As String) As XmlNode Dim element As XmlNode = parent.AppendChild(parent.CreateNode(XmlNodeType.Element, elementName, "")) If elementValue <> "" Then element.InnerText = elementValue End If Return (element) End Function Public Shared Function AddXmlAttribute(ByVal element As XmlNode, ByVal attrName As String, ByVal attrValue As String) As XmlNode Dim attr As XmlNode = element.Attributes.Append(DirectCast(element.OwnerDocument.CreateNode(XmlNodeType.Attribute, attrName, ""), XmlAttribute)) If attrValue <> "" Then attr.Value = attrValue End If Return (attr) End Function Public Shared Sub AddFieldRef(ByVal viewFields As XmlNode, ByVal fieldName As String) Dim fieldRef As XmlNode = AddXmlElement(viewFields, "FieldRef", "") AddXmlAttribute(fieldRef, "Name", fieldName) End Sub End Class Rgds Geoff ---------------------------------------------------------- Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.
Free Windows Admin Tool Kit Click here and download it now
May 28th, 2012 1:02am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics