Sharepoint BCS (Business Connectivity Services) configuration for SQL Server

Recently we have a request for SharePoint data display, but the data source is SQL Server.So the we leveraged BCS (Business Connectivity Services) to implement this task.

There are several authentication options when configuring an External Content Type to point to a SQL server list (tables, views etc).

Connect with User’s Identity

Connect with Impersonated Windows Identity

Connect with Impersonated Custom Identity

Normally we should choose “Connect with Impersonated Windows Identity”, because the final permission can be controlled at Sharepoint list level. The alias account credentials are kept in the Secure Store Service Application.

I.  SharePoint Central Admin

  1. Central Admin > Manage Service Application. Create a new Secure Store Service Application and edit it. The msdn details step can be referenced from https://technet.microsoft.com/en-CA/library/ee806866.aspx
  2. Create a new target application using the Group type.
  3. Once the Target Application has been created open the associated drop down list and select Set Credentials and enter the Username/Password of the AD alias account.

II. SharePoint Designer

  1. Create new External Content Type
  2. Create new connection and use the Connect with Impersonated Windows Identity option and enter the Secure Store Application ID you just created in Central Admin above
  3. Create a Read List and Read Item operation at the very least
  4. Save the External Content Type
  5. Open up the options menu of the newly created ECT to create an External List

III. SharePoint Central Admin

  1. Once you are finished with the previous step in SharePoint designer it automatically creates your External Content Type
  2. From the Business Data Connectivity Service Application find the new ECT, select the box next to it, and go to Set Object Permissions located on the command ribbon.
  3. Select a user or group with access to the ECT and grant at least Execute permissions to it.
  4. Select Set Metadata Store Permissions located on the command ribbon

The rest steps can be referenced by Connecting to SQL Server Using the External Content Type Feature. Basically it just use Sharepoint designer to connect to the External content type, and create a list to reference the ECT. Please note that all tables/views from SQL Server side need to map the primary key (identity key), and a Sharepoint site needs to be created first for designer to connect with.

There is another article can be referenced as “SharePoint 2010 using BCS with SQL Server database”

Advertisements

Sharepoint 2013 Sites collection, sites and view

Site Collection

in SharePoint is basically a collection of SharePoint sites that share common features like Content types, Templates, Site columns, permissions, Web Parts etc.

SharePoint Site

basically is a website. SharePoint allows us to create websites of specific types like Personal site, a Team Site, a social media site, a blogs or a Wiki Site etc.

As a SharePoint Site Collection share common features, it facilitates for better administration of our websites. It can be understood by taking a practical example. For example, in my software company, we have multiple departments (Software Development, Finance, Sales, Support etc.). All using SharePoint for different purposes like Document Sharing, Team Collaboration, Blogging and much more. For better administration, we have created a SharePoint Site Collection for each department containing all of its websites. It can be illustrated with the help of a structural diagram as below:
Site Vs Site Collection

From an architecture standpoint, all the content of a site collection must be stored in a single content database. You cannot have a site collection’s content spread out across multiple content databases. Content databases scale with your infrastructure capacity so site collections can share a content database. A site collection can exist in only one content database, but one content database can host the content for multiple site collections. Similarly, any given SharePoint 2013 site can only exist in one site collection, but a site collection can host a multitude of sites. A site cannot exist outside of a site collection.

SharePoint View

With views, you can use filters and styles to show in different ways the information available in your lists and libraries. There’s always a default view activated in your list or library. The automatic default view selected is normally “all items”, but normally got changed by a view contains some meaningful columns instead.

View types in SharePoint

SharePoint view types

Standard view

Standard view type in SharePoint

Calendar View

The calendar in SharePoint was basically a list with a disguise. You can decide which column you want to display on your calendar for a week, a month, a day and so on.

Calendar view type

This also mean that I can turn and list with any information I want into a calendar. As long as I have a date and time column.

Datasheet view

Datasheet view type in SharePoint

Gantt view

Gantt chart picture from wikipedia

The concept of a Gantt chart is similar to a calendar since it’s based on the dates. It shows the progress of different elements in time. It would be very useful for a list of tasks related to a specific project with deadlines for example.

Styles for the Standard view

View styles

Boxed

Boxed Style for SharePoint views

Newsletter

Newsletter style

Shaded

Shaded style

Create and modify the view

In the ribbon at the top under list/library–> “Manage Views”

Manage Views settings

You can modify the view you are currently using by clicking on “Modify View”. Then the same settings you had when you created the view will appear and you will be able to change them. I used this option to change the style of my list. This way, I didn’t have to create a new view for each one, I just had to go back in the view settings and change the style. The view could be public or private as needed.

Avoid Folders

Create a folder sometimes is much easier to manage the documents and also along the same concepts of Windows files system. But when you create a folder, it becomes its own list of items or documents. Therefore, if you create a filter in your library to see, for example, all the documents of 2014, the filter won’t get the elements inside your folders.

My challenge

Recently, we migrated from Sharepoint 2007 to Sharepoint 2013. There are some SSRS reports based on those Sharepoint list. After the migration, there is one report only can display part of the data on the Sharepoint list. he issue is due to new folders created on the list. So as mentioned in last paragraph, those records inside the folder won’t be able to find by SSRS sharepoint connection interface. So the easy solution is change the default view to “All Items”, in this way, SSRS interface has been able to see all the items of the list.

Reason: The SSRS report pulls data only from your “All Items” view in your SharePoint list so make sure “All Items” view is the default view in your list. Even though there is an option to specify the view name in the query builder but that doesn’t work when your are using Web Service for connecting to the SharePoint List.  Also make sure all the columns are present in your list view which you want to show on the reports. One final thing, in the list view setting change the page size from 30 to number of items you are expecting in your report

Import Sharepoint picture library images into SQL Server database by SSIS

We have a request to synchronize a particular customer list on SharePoint with the SQL Server database, this list actually is a picture library, so the task actually need an operation which retrieve Sharepoint library images to the SQL server table (the image would store in a “image” type field). It can be achieved by different ways, one is using Microsoft.Sharepoint.dll which is really simple for development as all objects you needed are there, but this solution only can run on the server which installed sharepoint and limited its usage. So eventually we selected the solution to use SSIS SharePointListAdapters and sharepoint imaging service to implement this.

1. SharepointListAdapter for SSIS
This adapter is issued by Microsoft SQL Server Community on codeplex and accepted by MSDN. So far it supported SQL Server 2005/2008/2008R2/2012, and the installation can be auto recognized on SQL Server 2008 or upper version. The official website can be referenced at sharepoint list adapter, the detail of how to setup and samples can be referenced at Sharepoint Adapter details usage 1 and Sharepoint Adapter details usage 2

2. Sharepoint web service
a. why this SSIS package need call sharepoint web service.
If the request just need to import those regular list or non document/picture library attachments, then the adapter works perfectly. However it doesn’t work for those binary field no matter your data conversion component setup, the trick is as below
20130205_ssis8
as you can see the adapter output columns recognized this column to be a unicode string and cannot be changed to other data type (the correct data type should be “image” or “byte steam”), hence all the input columns afterward this source already convert the binary image data to a string, so there is no luck to fill the sql server “image” field for sure.

b. How to revoke sharepoint web service
First, take a glance of all web services provided by Sharepoint at here web services, I used the imaging web service as the importing referenced to the picture library.
Note 1: Since SSIS 2008, it allow you to add web service reference directly similar as add web service in VS2008/2010 (In Visual Studio 2010–> add “Service Reference” –> From “Advance” add web service), in VS2005, a proxy class needs to be generated by wsdl.exe.
Notes 2: the first retrieve actually got a list of the picture library records (xml content with “z:row” properties, can got it from the outerxml property ), in this xml list, it provided the image absolute path in the property “ows_EncodedAbsUrl”, after got this path, then retrieve it into a steam and write the steam to the database.
Note 3: the ID is the unique ID in sharepoint library, which can be used for updating.
Notes 4: Accessing ADO.Net Connection Managers from an SSIS script task / script component
You cannot access the connection string as the regular VisualStudio project (from app.config / web.config), because the app.config file actually only effective in development time, and at runtime, cannot be access from ConfigurationManager etc. The only way to access the connection is by the connection setup in SSIS itself.
you’ll need to add a reference to the Microsoft.SqlServer.DTSRuntimeWrap assembly to get the IDTSConnectionManagerDatabaseParameters100 interface. If you’re doing this in a script task, you’ll need to prefix the Microsoft.SqlServer.Dts.Runtime.Wrapper namespace (or use fully qualified names) so that it doesn’t conflict with the namespace for the VSTA proxy classes.

For ado.net connection (Native client) use the following:

sqlConnection conn = (SqlConnection)Dts.Connections["adonet"].AcquireConnection(null);

For OLEDB connection managers is little complex

ConnectionManager cm = Dts.Connections["oledb"];
IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as IDTSConnectionManagerDatabaseParameters100;
OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection;

So the step would be
1) add script task to the SSIS package
2) add sharepoint imaging web service to the script project
3) add the following code to revoke the service and insert into the table.

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.IO;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.Collections.Generic;
    using System.Text;
    using System.Net;
    using System.Xml;
    using System.Xml.XPath;

public void Main()
{
   string jpgname = string.Empty;
   string jpgpath = string.Empty;
   string imgid = string.Empty;
   try
      {
        SharepointWS_Photos.Imaging  objLists = new SharepointWS_Photos.Imaging ();
        objLists.Credentials = System.Net.CredentialCache.DefaultCredentials;
        objLists.Url = "http://mysharepoint/sites/mypictures/_vti_bin/Imaging.asmx";
        XmlDocument xmlDoc = new System.Xml.XmlDocument();
        XmlNode ndListItems = objLists.GetListItems("MyPhotosList", "");
        StringReader sr = new StringReader(ndListItems.OuterXml);
        xmlDoc.Load(sr);
        XmlNodeList nodelist = xmlDoc.GetElementsByTagName("z:row");
        foreach (XmlNode mynode in nodelist)
        {
           jpgpath = mynode.Attributes["ows_EncodedAbsUrl"].Value;
           string[] segment = jpgpath.Split(Convert.ToChar("/"));
           jpgname = segment[segment.Length - 1];
           imgid = mynode.Attributes["ows_ID"].Value;
           DownLoadAttachment(jpgpath, jpgname, imgid);
        }
     }
   catch (Exception ex)
      {
         Console.WriteLine(ex.Message);
       }

   Dts.TaskResult = (int)ScriptResults.Success;
        
}

private void DownLoadAttachment(string strURL, string strFileName, string imgid)
{
   HttpWebRequest request = default(HttpWebRequest);
   HttpWebResponse response = null;
   byte[] imageData = null;
   ConnectionManager cm = Dts.Connections["MyADODatabaseConnection"];
   SqlConnection conn = (SqlConnection)cm.AcquireConnection(null);
   try
     {
       request = (HttpWebRequest)WebRequest.Create(strURL);
       request.Credentials = System.Net.CredentialCache.DefaultCredentials;
       request.Timeout = 10000;
       request.AllowWriteStreamBuffering = false;
       response = (HttpWebResponse)request.GetResponse();
       Stream imgstream = response.GetResponseStream();
       BinaryReader br = new BinaryReader(imgstream);
       imageData = br.ReadBytes(Convert.ToInt32(response.ContentLength));
       SqlCommand sqlCmd = new SqlCommand("UPDATE MyPhotoTable SET photoField = @imgbinary where spID=" + imgid, conn);
                sqlCmd.Parameters.Add("@imgbinary", SqlDbType.Image);
                sqlCmd.Parameters["@imgbinary"].Value = imageData;
                sqlCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
              
                Console.WriteLine(ex.Message);
            }
            finally 
            {
                if (conn.State == ConnectionState.Open) conn.Close();
            }


        }


3. The Complete SSIS package
a. Control flow:
The first component is a SQL Server task which delete all the existing records
The second component is a data flow which load all the picture library records with all columns except the picture (binary or image) field
The third component is the SQL Server task which run an SQL statement to check the loading successfully or not
The fourth one is a script task which runs the script showing on upper. Before running this component, there is a condition there (“f(x)”), so this step will only run when the pre-step runs successfully.
20130205_ssis2
b. Data flow:
20130205_ssis5