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