Access denied by Business Data Connectivity

Creating external content type and external list is rather straight forward. But then, I came across this “Access denied by Business Data Connectivity” today. I got this error even I am using the site collection admin account.

The solution is to configure the permission on the BCS Entity. Found the solution from this site:

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)

Copy Store Procedures to another database

A simple way to copy store procedures to another database in bulk in SQL Server Management Studio 2005.

  1. Right click database > Tasks > Generate Scripts
  2. In Script Wizard choose Object Type, Select Store Procedures
  3. Next, choose the Store Procedures
  4. Next, in Output Option, select your preferred output option for the script
  5. Run the script in another database. Done.

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)


XML data type encoding

I attempt to insert a xml in string format to a SQL2005 xml data type column. I receive the error: “XML parsing: line 1, character 39, unable to switch the encoding”. Explicitly specifiy the encoding in UTF-8 does not work out as XML data is stored using Unicode (UTF-16). Removing the encoding property in the xml header solve the issue.

<?xml version=”1.0″ encoding=”UTF-8″ ?>

SQL Server 2005 encodes XML data using Unicode (UTF-16). Data in a column of type xml is stored in an internal format as large binary objects (BLOBs) in order to support XML model characteristics, such as document order and recursive structures. Therefore, XML data retrieved from the server comes out in UTF-16; if you want a different encoding for data retrieval, your application must perform the necessary conversion on the retrieved UTF-16 data

Have a read on the International Features is MS SQL 2005 and Limitations of the xml Data Type

Reference: MSDN