SSIS and SharePoint Web Service – GetListItems

Recently working on SSIS to get SharePoint List Item. Using SSIS Script Component, we can call the SharePoint web service. Proxy class can be generated using Web Services Description Language Tool (Wsdl.exe)

Dim ListsService As Lists = New Lists() 'Web Service proxy class
ListsService.Url = "http://kelvinsharepointsite/_vti_bin/lists.asmx"
ListsService.Credentials = System.Net.CredentialCache.DefaultCredentials

'Create Query CAML
Dim xmlQueryDoc As New XmlDocument
Dim ndQuery As XmlNode = xmlQueryDoc.CreateNode(XmlNodeType.Element, "Query", "") 'Create <Query> node
Dim ndViewFields As XmlNode = xmlQueryDoc.CreateNode(XmlNodeType.Element, "ViewFields", "") 'Create <ViewFields> node
Dim ndQueryOptions As XmlNode = xmlQueryDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "") 'Create <QueryOptions> node

ndQuery.InnerXml = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>My Title Here</Value></Eq></Where>"
ndViewFields.InnerXml = "<FieldRef Name='Description'/>"
ndQueryOptions.InnerXml = ""
Dim listName As String = "My List Name Here"
Dim rowLimit As String = "1"

'Lists.GetListItems (listName, viewName, query, viewFields, rowLimit, queryOptions, webID)
Dim ndListItems As XmlNode = ListsService.GetListItems(listName, Nothing, ndQuery, ndViewFields, rowLimit, ndQueryOptions, Nothing)

Dim xmlResultDoc As New XmlDocument()
'For debug

Dim nsmgr As New XmlNamespaceManager(xmlResultDoc.NameTable)
nsmgr.AddNamespace("z", "#RowsetSchema")

Dim rows As XmlNodeList = ndListItems.SelectNodes("//z:row", nsmgr)
Dim value As String = rows(0).Attributes("ows_Description").Value
MsgBox("Returned value: " + value)

'For iteration multiple rows
'For Each xNode As XmlNode In rows
'    MsgBox(xNode.Attributes("ows_Description").Value)

Looping all columns in SSIS Script Component

Quite new to SSIS but working on an old SSIS 2005. Was attempting to make a foreach to get all column values from a row.

Dim column As IDTSInputColumn90
Dim pInfo As PropertyInfo
Dim rowType As Type = Row.GetType()

For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
            pInfo = rowType.GetProperty(column.Name)
            varColValue = pInfo.GetValue(Row, Nothing).ToString()

That didn’t actually works. Some column able to get the value, some don’t. Then I found out, the column.Name not the same with the property exposed by rowType.GetProperty.

column.Name Property exposed by rowType.GetProperty
SAP Company Code SAPCompanyCode
Cost Object (WBS/CC) CostObjectWBSCC

So, have to do some changes and the following codes works:

For Each pInfo In rowType.GetProperties()
            If pInfo.PropertyType Is GetType(String) Then
                varColValue = pInfo.GetValue(Row, Nothing).ToString()
            End If

This pretty useful if you wanna do something on all the columns with the same function. I have seen examples of changing all columns value to uppercase. (Derived Column component have to adjust all columns one by one)