Microsoft Business Connectivity Services Team Blog

BCS Artifact Generator

bcsbauth — Fri, 04 Jun 2010 00:23:02 GMT

Chances are you’ve already read Brad Stevenson’s post Types of Solutions and you’re interested in building a BCS solution.

If you’ve read my post on the BCS Solution Packaging Tool, perhaps you’ve already tried to build and package a solution yourself.

To help out developers and power users we’ve created another new tool called the BCS Artifact Generator which will greatly help you get started building working solutions, which you can then customize however you’d like.

To use the tool simply select your BDC Model (see Lionel Robinson’s post Creating an External Content Type in SharePoint Designer 2010 for help creating a BDC Model), give your solution a name, then follow the wizard which guides you through picking and choosing how your solution will look in Outlook. When you click “Generate” the tool will produce a folder containing all of the files necessary to run a BCS solution.

You can then point the Solution Packaging Tool at this folder to package and deploy your solution to Outlook, all in a matter of minutes.

You can download the free Artifact Generator tool from MSDN Code Gallery.

Also, be sure to download the free Solution Packaging Tool, also from MSDN Code Gallery.

Creating a Declarative Solution for Outlook

To create a declarative BCS solution in Outlook, you’ll do the following:

Create the necessary solution artifacts (see below for details)
Put the artifacts in a folder
Create a BCS Package (a special type of VSTO ClickOnce package) using that folder - Optional: put the BCS Package somewhere public (i.e. SharePoint) where your users can then go to deploy the solution
Deploy the solution package to Outlook
You’re done!

Creating a BCS Package is done using the Solution Packaging Tool, which we’ve seen already. This post will examine the first step – creating the solution artifacts. We’ll show you how quickly and easily you can do this using the Artifact Generator tool.

What are the required artifacts?

To have a solution you’ll need the following three files at the very least:

Having these three artifacts will allow you to create and deploy a solution in Outlook. In addition, your solution can contain the following three customizations:

A) A custom item Taskpane (item C in the screenshot below)

B) A custom item Form Region (item D in the screenshot below)

C) A custom ribbon containing item action buttons (item B in the screenshot below)

The following screenshot shows a declarative solution and each of the three aforementioned customizations:

To add a custom Taskpane to your solution you’ll need each of the following:

a) InfoPath Form [.xsn]

b) Layout file [.xml]

c) A few lines of XML added to the Solution Manifest

To add a custom Form Region to your solution you’ll need each of the following:

a) Office Form Storage File [.ofs]

b) Form Region Manifest [.xml]

c) A few lines of XML added to the Solution Manifest

To add a custom ribbon tab with action buttons to your solution you’ll need each of the following:

a) Ribbon definition file [.xml]

b) A few lines of XML added to the Solution Manifest

Using the Artifact Generator

You can now use the Artifact Generator to create most of these files automatically:

Solution Manifest (including the additional lines needed for each customization)
Subscription(s)
Layout file
Form Region Manifest
Ribbon definition file

Before using the tool, be sure to create the following files if you’d like to include them for use:

BDC Model
[Optional] InfoPath form (to show item details in the taskpane)
[Optional] Office Form Storage file (to customize the added form region)

Steps to use the Artifact Generator

Give your solution a name
Import the BDC Model
Click “Next”
You can customize each of the External Content Types (ECT) that are mapped to Outlook item types by clicking “Customize” (see below for customization options):

First, you can change the name that is displayed for that item.
You can also change the icon that is displayed for that item.
Click “Next”.
For the ECT fields (i.e. “Product Serial Number”) that aren’t mapped to regular Outlook fields (like “First Name”, “Phone Number”, etc.), a form region will be automatically generated for your solution. You can also create and customize this region yourself by creating an Office Form Storage file and selecting to use that file for this item.
Click “Next”.
If the External Content Type has an association (in the BDC Model) you can show those associated items in a list on a taskpane (i.e. when you open a customer contact, it will show the orders that the customer has placed).
You can also add an InfoPath form which will show the field details for each item you select in that list. First create and customize the InfoPath form and select it for use in the wizard for this item.
Click “Next”.
To add a ribbon for the item which can have action buttons that perform custom actions for the item, click “Add a custom action” and then choose a name for the ribbon tab and for the ribbon group label where the button appears.
By default the Artifact Generator will help you create one action button to start with. You can easily edit the Ribbon definition file later to add more buttons. This default button is a “URL Action” type of button, which opens up a URL in the browser based on a parameter value for that item. Enter a URL (be sure to add “{0}” in the URL where the value should go) and select a field to use as that parameter.
Click “Finish”.

Click “Generate artifacts”.
A folder containing these newly generated artifacts will be created at the same location of the tool itself. The folder will also open up, displaying the artifacts.

Then, use the BCS Solution Packaging Tool to select this folder and create a new solution package to be used for installation and deployment of the solution. Be sure to select “Outlook Intermediate Declarative Solution” as the solution type.

You should now have created an Intermediate Declarative Solution in Outlook!

Data Solution

If you’ve read my post Overview of BCS Advanced Code-Based Solutions or the MSDN article Creating Advanced Code-Based Solutions Using Business Connectivity Services you’re aware that you can create Office Add-Ins using Visual Studio which can integrate with BCS and external data.

These solutions need metadata and subscriptions on the machine running the solution, which dictate synchronization of the external data between the client and the external system. This metadata can be set up on that machine by having a user deploy a Simple Solution to Outlook or SharePoint Workspace, but this requires additional steps that each user of your solution would have to perform initially.

The ideal and recommended approach is to use a complementary Data Solution to go with your Office Add-In, which will set up the metadata and subscriptions on the user’s machine automatically.

A Data Solution requires the following files:

BDC Model
Subscription(s)

Again, you can either create the subscriptions by hand or use the Artifact Generator to create them for you. Simply import your BDC Model to the Artifact Generator and click “Generate artifacts”. Then, use the Solution Packaging Tool to point to that folder and create a BCS Package of type “Data Solution for Office Add-In”.

If the user deploys this solution and then deploys your Office Add-In, your add-in will be able to display and manipulate external BCS data.

To try this out for yourself, check out the following: Business Connectivity Services Excel 2010 Add-In.

Thanks for reading, and enjoy the tool!

Russell Palmer

Program Manager

BCS Solutions in Outlook

bcsbauth — Fri, 04 Jun 2010 00:04:44 GMT

If you’ve read Brad Stevenson’s post Types of Solutions then you’re already familiar with the three types of BCS solutions:

Simple Out-Of-Box Solutions
Intermediate Declarative Solutions
Advanced Code-Based Solutions

This post will discuss the different types of BCS solutions possible in Outlook 2010.

Simple solutions can be created using out-of-box capabilities, by simply clicking the “Connect to Outlook” button in the ribbon of a SharePoint External List.

At that point, a new folder will be displayed in Outlook. Clicking on the item type in that folder will display the external data items, previously seen in the External List, inside of Outlook either as a Contact, Post, Task or Appointment. See External Lists in Outlook for an example.

The fields that are unique to this External Content Type (i.e. “Customer ID Number”) that were not mapped to Outlook fields (such as First Name, Phone Number, Email, etc.) will be displayed in a standard auto-generated form region when opening up an item. All of the names and icons for the solution will be automatically selected.

For developers that want to create a similar type of solution in Outlook, yet customize it to extend its capabilities and look more uniquely professional, an intermediate declarative solution can be created for these needs. This will allow you to customize the:

Number of External Content Types that appear in the solution folder in Outlook
Solution names and icons used
Taskpane, which displays related items for External Content Types with associations
Ribbon, which contains custom action ribbon buttons
Form region, used to show the unique fields

In addition to these Simple and Intermediate solutions, developers can also create Advanced BCS solutions in Outlook with code, by creating an Outlook Office Add-In in Visual Studio 2010 and packaging it together with a BDC Model, subscriptions and a Solution Manifest in a BCS Solution Package.

Let’s look a little closer at both the Intermediate Declarative Solution and the Advanced Code-Based Solution.

Intermediate Declarative Solution

To create an intermediate declarative solution:

Create the required artifacts (see table below for details)
Put the artifacts in a folder
Create a BCS Package (a unique kind of VSTO ClickOnce package) from that folder
Deploy the solution to Outlook
You’re done!

To create the required artifacts, you can either:

a) Create them automatically using the BCS Artifact Generator tool, or

b) Create them by hand, following the guide Creating Intermediate Declarative Outlook Solutions Using Business Connectivity Services

Be sure to check out the following resources as well:

To package the required artifacts, you can either:

a) Use the BCS Solution Packaging Tool with type “Outlook Intermediate Declarative Solution”, or

b) Use the server APIs to package manually

Here’s an example of what a custom intermediate declarative solution can look like in Outlook:

The following table lists the required and optional artifacts:

Component

Artifact

Details

BDC Model

Metadata.bdcm

Required; must have .bdcm file extension or be named exactly “metadata.xml”

Subscription file(s)

*Subscription.xml

Required, one for each External Content Type; must be named as *Subscription.xml (i.e. “myCustomerSubscription.xml”)

Solution Manifest

OIR.config

Required; must be named OIR.config

OIR or layout resource file(s)

*.resx

Optional

Outlook ribbon file(s)

*.xml

Optional

Layout file(s)

*.xml

Optional

InfoPath form(s)

*.xsn

Optional

Outlook form region(s)

*.ofs

Optional

Form region manifest file(s)

*.xml

Optional

Custom Ribbon Manger, Custom Action and Custom OBPart assembly

*.dll

Optional

Outlook view definition(s)

*.ovd

Optional

Icon(s)

*.ico, *.jpg, *.png

Optional

Advanced Code-Based Solution

To create an advanced code-based solution:

Create the required artifacts (see table below for details)
Create an Outlook Office 2010 Add-In using Visual Studio 2010
Put the artifacts and Add-In DLL in a folder
Create a BCS Package (a unique kind of VSTO ClickOnce package) from that folder
Deploy the solution to Outlook
You’re done!

To create the required artifacts, you can either:

a) Create them automatically using the BCS Artifact Generator tool, or

b) Create them by hand

To package the required artifacts, you can either:

a) Use the BCS Solution Packaging Tool with type “Outlook Advanced Code-Based Solution (OIR)”, or

b) Use the server APIs to package manually

The following table lists the required and optional artifacts:

Component

Artifact

Details

BDC Model

Metadata.bdcm

Required; must have .bdcm file extension or be named exactly “metadata.xml”

Subscription file(s)

*Subscription.xml

Required, one for each External Content Type; must be named as *Subscription.xml (i.e. “myCustomerSubscription.xml”)

Solution Manifest

OIR.config

Required; must be named OIR.config

VSTO Add-In assembly

*.dll

Required

OIR or layout resource file(s)

*.resx

Optional

Icon(s)

*.ico, *.jpg, *.png

Optional

BCS Solution Tools

For more information on the BCS Solution Packaging Tool read the blog post BCS Solution Packaging Tool.

For more information on the BCS Artifact Generator see the blog post BCS Artifact Generator.

How do I optimize backend synchronization?

lionelro — Thu, 13 May 2010 15:55:00 GMT

Business Connectivity Services (BCS) has a synchronization framework that uses a client cache to enable you to work with external data even when disconnected.

In this post, we’ll discuss ways to optimize solutions to best use the synchronization framework client cache. We’ll be using the Contoso Sales Manager sample solution presented in the previous post, BCS Solution Packaging Tool. Please check that post for instructions on how to package and deploy the solution.

How does the client cache work?

When a solution is deployed to an Office client, a process called BCSSync.exe contacts the external system directly and downloads the data into a per-user SQL CE database managed by the synchronization framework.

Every client will be contacting the external system directly, so it’s really important to optimize your solution for offline use to minimize the load on the external system.

Working with Subscriptions

To determine what data will be brought to the client cache, solutions need to define subscription xml files. Each subscription file is responsible for defining how to request data from the external system.

Subscriptions can have 3 types of items: Queries, Explicit Identities and Associations. Queries point to finders that will be executed against the external system to populate the cache, Explicit Identities are additional instances that are added individually to the subscription, and Associations are used to bring related instances.

Here is the subscription file for Contoso Sales Manager solution defining how to request data for the “Customers” external content type.

<?xml version="1.0" encoding="utf-8"?>
<Subscription LobSystemInstanceName="AdventureWorksContosoLOBInstance"
    EntityNamespace="AdventureWorksContoso" EntityName="Customer"
    Name="AdventureWorksContosoCustomerSubscription" View="GetCustomerById"
    IsCached="true" RefreshIntervalInMinutes="360"
    xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
<Queries>
    <Query Name="AdventureWorksContosoCustomerQuery"
        MethodInstanceName="GetCustomers"
        DefaultDisplayName="Customer Read List"
        RefreshIntervalInMinutes="180" IsCached="true" Enabled="true" />
</Queries>
<Associations>
    <Association Name="GetOrdersForCustomer"
        MethodInstanceName="GetOrdersForCustomer"
        TargetSubscriptionName="AdventureWorksContosoOrderHeaderSubscription"
        TargetView="GetSalesOrderHeaderById"
        LobSystemInstanceName="AdventureWorksContosoLOBInstance"
        RefreshIntervalInMinutes="60"/>
</Associations>
</Subscription>

The subscription above has one query, which runs the “GetCustomers” Finder on the external content type. All Customer instances returned by the finder call will be cached locally on every client that has the solution installed. The refresh interval for the query is set to 180 minutes, so every 3 hours the finder will be executed and the local data will be refreshed on each client.

NOTE: When working with the client cache, it’s important to know that it performs best when the Finder view matches the SpecificFinder view. If the Finder returns a subset of the fields that the SpecificFinder returns, the synchronization framework is forced to call the SpecificFinder on every instance returned by the finder to populate all fields of the instance. If it’s not possible for the Finder and the SpecificFinder views to match, bulk stereotypes can be used to improve performance. Bulk stereotypes will be covered on a later post.

Optimizing Subscriptions

Explicit Identities

If you know exactly which items to synchronize, you can just add their identities to the subscription individually. This is a lot easier to do programmatically since you have to put the serialized identity on the subscription XML file.

To get the serialized Identity, you need to instantiate an Identity object with the appropriate identifier values, and call its Serialize method. More information can be found at the Identity.Serialize method page on MSDN. Once you have the Identity’s serialized string, you just need to add it in an “<Identity>” tag within the “<Identities>” tag, as will be shown later in the example.

Filtering data

The obvious way to optimize synchronization is to reduce the amount of data that is downloaded by the client. The easiest way to do this is by using filters. The filters supported by the synchronization framework that can be used to reduce the number of items downloaded are Wildcard, Comparison and Limit filters.

For example, you can use a Wildcard filter to download all employees whose names start with the letter “M” using “M*” as the filter value. Or you can select all customers that have their zip code equal to “98052” using a comparison filter. A limit filter can be used to limit the number of products to 100.

Example

Here is an example of the subscription above with 11003 and 11020 as explicit identities and a filter to return only customer with CustumerIDs greater than 11050.

<?xml version="1.0" encoding="utf-8"?>
<Subscription LobSystemInstanceName="AdventureWorksContosoLOBInstance"
    EntityNamespace="AdventureWorksContoso" EntityName="Customer"
    Name="AdventureWorksContosoCustomerSubscription" View="GetCustomerById"
    IsCached="true" RefreshIntervalInMinutes="360"
    xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
<Identities>
    <Identity>i+yoAAA==</Identity>
    <Identity>iDCsAAA==</Identity>
</Identities>
<Queries>
    <Query Name="AdventureWorksContosoCustomerQuery"
        MethodInstanceName="GetCustomers"
        DefaultDisplayName="Customer Read List"
        RefreshIntervalInMinutes="180" IsCached="true" Enabled="true">
      <FilterValues>
        <FilterValue FilterDescriptorName="MinCustomerId" FilterIndex="0"
           Type="System.Int32">11050</FilterValue>
      </FilterValues>
    </Query>
</Queries>
<Associations>
    <Association Name="GetOrdersForCustomer"
        MethodInstanceName="GetOrdersForCustomer"
        TargetSubscriptionName="AdventureWorksContosoOrderHeaderSubscription"
        TargetView="GetSalesOrderHeaderById"
       LobSystemInstanceName="AdventureWorksContosoLOBInstance"
      RefreshIntervalInMinutes="60"/>
</Associations>
</Subscription>

This subscription will only download the specified customers, as you can see in the image below.

The filters used by the subscription file have to be defined on the model, and the external system needs to support them, too. The model for the Contoso Sales Manager solution and the AdventureWorks web service used by the solution were updated to support a comparison filter on the CustomerID field. The updated solution can be found in the package attached to this post.

NOTE: The version number of the Customer external content type was increased on the updated solution. When editing the model manually, this is an important step to make sure our changes are not ignored during deployment.

Is that all?

Limiting the amount of data downloaded by the client is not always an option. For those cases, version field, Bulk Stereotypes and Last Id Seen filter are a great option. They will be covered in an upcoming post.

- Rodrigo Silveira.

How do I leverage Secure Store from within my .Net Connectivity Assembly?

lionelro — Tue, 11 May 2010 15:00:00 GMT

With Database, Web Service and WCF connectors, there is one and only one external system. BCS understands how to connect to these external systems, and it supports Secure Store integration out-of-box for these connectors. Within a .NET assembly connector, the .NET assembly code can use zero, one or many external systems. Since BCS does not know what the .NET assembly code does, it does not support Secure Store integration out of box for the .NET assembly connector.

Even though BCS does not have OOB support for Secure Store in .NET assembly connector, it provides two different mechanisms to leverage Secure Store within a .NET connectivity assembly. In the first mechanism (no-code approach) BCS does the heavy lifting of reading the credentials from secure store whereas in the second mechanism (code approach) .NET connectivity assembly needs to do the heavy lifting.

This blog explains how both approaches are used.

No-code approach

As the name suggests, your .NET connectivity assembly does not directly interact with Secure Store. Using filters, it can utilize BCS to read the credentials from Secure Store and get the credentials as input parameters to the method. This approach is not unique to .NET assembly connector and can be used with Database, Web Service and WCF connectors.

When writing the method in .NET connectivity assembly, you will need to have input parameters for credentials. The following example shows “GetEntity” method with input parameters for username and password along with other parameters.

public SampleEntity GetEntity(string username, string password, int id){
// parameters username and password
// contains the credentials
}

To get the credentials in the username and password parameters, you will need to use filters UsernameFilter and PasswordFilter. In the metadata model, you will need to define the properties “SecondarySsoApplicationId” and “SsoProviderImplementation” for LobSystemInstance and associate the filters to appropriate input parameter of the method. When BCS encounters filters, it uses the properties “SecondarySsoApplicationId” and “SsoProviderImplementation” for secure store target application id and secure store provider.

<LobSystemInstance ...>
<Properties>
    <!-- other properties omitted –>
    <Property Name="SecondarySsoApplicationId"
        Type="System.String">MyLobAppId</Property>
    <Property Name="SsoProviderImplementation" Type="System.String">
        Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>
</Properties>
</LobSystemInstance>

Now let’s define the two filters for the method.

With the filters defined for the method, the following snippet shows how to associate the two filters to the input parameters of the method.

That’s all you will need to leverage secure store in .NET connectivity assembly. When BCS executes the method, it will read the credentials from the secure store and set the credentials in the input parameters of the method.

Code-approach

Before we start writing code for leveraging Secure Store, let’s take a look at how BCS leverages Secure Store in other connectors. Other connectors in BCS rely on the metadata model to provide them information about Secure Store and the target application id where the credentials can be retrieved from for authenticating with the external system. For example, in the case of the Database connector, the properties for LobSystemInstance in the metadata model look like this:

<LobSystemInstance ...>
<Properties>
    <!-- other properties omitted –>
    <Property Name="SsoApplicationId"
        Type="System.String">MyLobAppId</Property>
    <Property Name="SsoProviderImplementation" Type="System.String"> Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>
</Properties>
</LobSystemInstance>

Reading the metadata model, the Database connector understands that it needs credentials to connect to the database and the credentials can be obtained from the specified secure store provider. Once the database connector has the necessary information for the Secure Store provider, it reads the credentials from the provider and impersonates the user for database connection.

The following sections explain how to read the metadata model properties within your .NET assembly code and use secure store APIs to read user credentials.

Reading metadata model properties

To read metadata model properties in the .NET connectivity assembly, code in .NET assembly must implement IContextProperty interface. The following code shows a sample implementation of IContextProperty interface.

Sample implementation of IContextProperty

using IContextProperty = Microsoft.BusinessData.SystemSpecific.IContextProperty;

public class SampleConnector : IContextProperty
{
    private IMethodInstance methodInstance;
    private ILobSystemInstance lobSystemInstance;
    private IExecutionContext executionContext;

#region IContextProperty implementation

    public IMethodInstance MethodInstance
    {
        get { return this.methodInstance; }
        set { this.methodInstance = value; }
    }

    public ILobSystemInstance LobSystemInstance
    {
        get { return this.lobSystemInstance; }
        set { this.lobSystemInstance = value; }
    }

    public IExecutionContext ExecutionContext
    {
        get { return this.executionContext; }
        set { this.executionContext = value; }
    }

#endregion

// unrelated code removed for brevity
}

When the .NET connectivity assembly class implements the IContextProperty interface, the BCS runtime automatically initializes IContextProperty properties.

At runtime, the code has the context (method instance and LOB system instance) in which it executes; a method implemented in the code can read the properties from the metadata model. The following code shows how LobSystemInstance properties are read:

public SampleEntity GetEntity(int id)
{
    // read the LobSystemInstance property defined in metadata model
    string provider =
        LobSystemInstance.GetProperties()[“ssoProviderImplementation”]
        as string;

// unrelated code removed for brevity
}

Interacting with Secure Store

When the .NET connectivity assembly requires reading one credential from Secure Store, we recommend using the following property names.

ssoProviderImplementation – Secure Store provider
SecondarySsoApplicationId – Target Application for Secure Store

ssoProviderImplementation and SecondarySsoApplicationId properties are supported for modeling .NET assembly connector models in SPD.

If the .NET connectivity assembly requires reading more than one credential from Secure Store, choose a property name that makes the most sense for your scenario.

Sample implementation for reading credentials

The first step is to instantiate the Secure Store provider from the provider implementation. In your code, make sure that the provider implements ISecureStoreProvider interface.

A simple implementation for instantiating the provider may look like

private ISecureStoreProvider GetSecureStoreProvider()
{

// error checking removed for brevity

string provider = this.LobSystemInstance.GetProperties()
[“ssoProviderImplementation”] as string;

Type providerType = Type.GetType(provider);

return Activator.CreateInstance(providerType)
as ISecureStoreProvider;

}

Once the Secure Store provider has been instantiated, it’s time to get the credentials from Secure Store. Secure Store returns a SecureStoreCredentialCollection for the user and target id, which contains a collection of credentials. ISecureStoreSecureCredentials interface defines the credentials type and its value.

private void ReadCredentialsFromSecureStore()
{

// error checking removed for brevity

    string targetId =
        LobSystemInstance.GetProperties()[“SecondarySsoApplicationId”]
        as string;
    ISecureStoreProvider provider = GetSecureStoreProvider();

    // get the credentials for the user on whose behalf the code
    // is executing
    using(SecureStoreCredentialCollection credentials =
        provider.GetRestrictedCredentials(targetId))
    {
        SecureString secureUsername;
        SecureString securePassword;

         // look for username and password in credentials
        foreach (ISecureStoreCredential credential in credentials)
        {
            switch (credential.CredentialType)
            {
                case SecureStoreCredentialType.UserName:
                case SecureStoreCredentialType.WindowsUserName:
                    secureUsername = credential.Credential;
                    break;
                case SecureStoreCredentialType.Password:
                case SecureStoreCredentialType.WindowsPassword:
                    securePassword = credential.Credential;
                    break;
                default:
                    break;
            }
        }
        // username and password have been read
        // use them as necessary.
    }

     //NOTE: Since we are getting the credentials in the using block,
    //all the credentials that we get will be disposed after the
    //using block. If you need to cache the credentials don't use
    //using block but dispose the credentials when you are done
    //with it.

}

The above code shows how the credentials are read from secure store. These credentials can be used for authentication to an external system.

Summary

Depending upon your scenario, you may choose no-code approach or code approach to leverage secure store in your .NET connectivity assembly. The following table lists the differences between these two approaches.

	No-code approach	Code approach
Interaction with secure store API required?	No	Yes
Exclusive to .NET assembly connector	No	Yes
SPD support	Yes	Partially
Impacts method signature	Yes	No
Read from multiple Secure Store	No	Yes
Can credentials be set at LobSystemInstance level?	No	Yes

-Kaushik Raj

BCS Logging

lionelro — Fri, 07 May 2010 09:36:00 GMT

BCS is a cool framework to bring external data to Office Applications on the client. But due to various reasons like misconfigured BDC models/ Advanced Code-Based Solutions/ Intermediate Declarative Solutions or External System’s business logic, the expected data might not show up on the client. Most of the troubleshooting can be done by looking at the event logs. If the information in event logs is not enough to troubleshoot the issue, then the next best thing to do is start BCS logging on the client. Client logs provide rich and descriptive information, which is really helpful in debugging complex issues. Check out “Diagnostic logging in Business Connectivity Services overview (SharePoint Server 2010)” for information on how to enable BCS logging on the client.

There are 2 levels of logging from BCS on the client – Events logs and Trace logs. All the logging from BCS can be found in Trace logs. Whereas only a subset of the Trace logs are logged under Event Logs and they comprise of the important BCS actions which will help the user in figuring out the state of the BCS Solutions on the client i.e. information regarding the Solution download, Install & Uninstall, solution activation, Services startup & shutdown, Solution Registration and Errors. Trace Logs are more descriptive than Event logs and contains the exception stack trace of errors if any.

Here are the different Categories under which most of the BCS actions are logged:

Deployment - The logs under this category help the user to figure out the important deployment actions that happen when offlining an External list from SharePoint or while doing a standalone install of Declarative/Code-Based solutions. All the actions involved with Solution download, Activation, package verification and Uninstallation get logged under this category.
Runtime – BCS on the client has both managed and unmanaged assemblies. All the offlining calls from the managed components of BCS like CRUD operations, Subscription Refresh, Picker resolution, Rich List part actions from office applications, etc…fall under this category.
Wrapped PST – The logs under this category are from the unmanaged BCS Add-in running in outlook.
Business Data – All the BDC Runtime calls involved in executing against the External System are found under this category. These calls include preparation of the request, invoking of BDC stereotype methods against the External System and runtime processing of the results.

By default the values of Input and Return Parameters are not dumped in the logs due to privacy issues. For debugging purpose, you can add the “LogParameterValues” property at the Method Parameter level in metadata to log the parameter values sent and received from the External System for every call.

<Property Name="LogParameterValues" Type="System.Boolean">true</Property>
Cross Sync – Actions involving data Sync between Offline Cache (i.e. SQL CE database) and Outlook Wrapped PST are logged under this category.

Here are some sample logs which outline the important actions logged as part of the E2E life cycle of a WCF based External Content Type (ECT) off-lined to outlook:

Solution Install/ List Offlining logs:
- Runtime - BCSSync started with PID: 4020, integrity level: 8192 and command line: ""C:\Program Files (x86)\Microsoft Office\Office14\BCSSync.exe" -Embedding".
- Runtime - Cache database file not found, creating new database file.
- Runtime - BCS directory encrypted successfully.
- Runtime - Service Microsoft.Office.BusinessApplications.Runtime.DiskIOThrottler was started.
- Runtime - Service Microsoft.Office.BusinessApplications.Runtime.ProxyRegistrationService was started.
- Runtime - Service Microsoft.Office.BusinessApplications.Runtime.Deployment.ActivatorService was started.
- Runtime - Service Microsoft.Office.BusinessApplications.Runtime.Deployment.VerifierService was started.
- Runtime - Service Microsoft.Office.BusinessData.Offlining.SynchronizationManager was started.
- Runtime - Service Microsoft.Office.BusinessApplications.Runtime.Deployment.UpdateService was started.
- Deployment- Url:file:///C:/Users/<UserName>/Desktop/Output/ContosoSalesManager.vsto Solution Name:ContosoSalesManager Solution Id:ContosoSalesManager Solution Version:1.0.0.0 Message:Solution download completed successfully.
- Runtime - Starting verification process for model C:\Users\<UserName>\AppData\Local\Microsoft\BCS\cont..ager_ae3bf1f59b9d1e0f_0001.0000_0c401f2e52834d92\metadata.xml.
- Business Data - Generating WCF Proxy Assembly in a parallel App Domain
- Business Data - Trying to discover service document at 'http://<Server>:8181/webservice.asmx?wsdl'
- Business Data - Compiling proxy was success, will retrieve the assembly.
- Runtime - Set to Verification Complete for the solution ContosoSalesManager.
- Runtime - BCSSync with PID: 4020 was requested to spawn a new instance and shutdown.
- Runtime - BCSSync started with PID: 4068, integrity level: 8192 and command line: ""C:\Program Files (x86)\Microsoft Office\Office14\BCSSync.exe" /Restart /Activation /RestartApps".
- Runtime - Starting deployment of subscriptions for solution ContosoSalesManager.
- Runtime - Verifying subscriptions.
- Runtime - Generating subscriptions in cache

Wrapped PST Data Sync logs:
- Runtime - Notification is being sent
- Cross Sync - Receiving Cache Changed (Direct) Event
- Runtime - 17 direct change notifications processed at 10:42:11.0782575
- Cross Sync - BCS/PST synchronization: Changing 1 Outlook items with information from item 'e83807ab-4748-4a36-ba32-ab62a9aafd78' IsDeleted=False .
- Cross Sync - BCS/PST synchronization: Folder 'ContosoCustomerFolderID' is scheduled to be synchronized.
- Cross Sync - BCS/PST synchronization: Folder state changed to 'vfId=ContosoCustomerFolderID, status=resync, token=8973, exception=null'.
- Cross Sync - Copied Syncstamp 2010-04-28 17:45:50Z for folder ContosoCustomerFolderID ito XS_FOLDER record
- Cross Sync - BCS/PST synchronization: Completed synchronization of folders: ContosoCustomerFolderID,
- Cross Sync - Syncstamp for folder ContosoCustomerFolderID is set to 2010-04-28 17:45:51Z

LOB communication logs:
- Runtime - Data change detected; restarting synchronization loop.
- Runtime - Refreshing subscription AWWSExampleCustomerSubscription with results of query AWWSExampleCustomerQuery
- Business Data - Opening connection to SystemInstance: http://<Server>:8080/webservice.asmx?wsdl
- Business Data - Creating ClientBase object with type : BCSServiceProxy.WebServiceSoapClient
- Business Data - Using WCF authentication mode : PassThrough
- Business Data - Executing MethodInstance '2147484216' representing Web Method 'GetCustomers'
- Business Data - Closing connection to SystemInstance: http://<Server>:8080/webservice.asmx?wsdl
- Business Data - Exiting out of method to enumerator executor

Most of the BCS logs are pretty descriptive and intuitive. Hopefully this blog post can act as a starting point for debugging issues while developing BCS solutions.

- Sreekanth Lingannapeta

BCS Team Channel: Secure Store Service

lionelro — Thu, 06 May 2010 22:55:31 GMT

We just uploaded the next installment on the BCS Team Channel. In this video Sanjay Rama takes you through a quick introduction and demo of the Secure Store Service.

Secure Store Service is the new single sign on service in SharePoint 2010. It provides the capability of storing secure data (i.e. A set of credentials) and the ability to map this data to a specific identity or a group of identities.The most common scenario for Secure Store Service is an application trying to authenticate against a system in which the current logged in user is known differently or has a different account for authentication. It’s a shared service in SharePoint 2010 and can be used to store credentials of multiple external systems.

Why it’s important to define filters for a good picking experience

lionelro — Wed, 05 May 2010 22:29:35 GMT

Hi this is Juan Balmori with another post for the BCS blog. This is a follow up to my previous post, about the external item picker. There is a problematic scenario that you may face: You are searching for an external item on the picker dialog that you are 100% sure it exists on the external system but the dialog does not seem to work properly and is not finding it. Worse it’s not even filtering by the specified search criteria.

For example in the following image I am searching for an ‘Artist’ whose name is ‘Kim’ and evidently exists as you can see on the search results but the picker is:

Effectively showing Kim
But is also showing a bunch of additional results that don’t even meet the search criteria
On top of that, I get a strange error message telling me that ‘Search criteria cannot be met as Filters are not defined for the External Content Type. Contact your system administrator’

What’s going on?

Well, as the error message is suggesting, the problem is that in this example the “Artist” External Content Type (ECT) does not have a filter defined and when an ECT lacks a Filter, the default behavior is to show the first 200 items and we cannot filter/narrow the search results. And you are going to get that warning.

Moreover, imagine a scenario in which you have thousands of items to pick from; you will need to have a good set of filters defined on your ECT so that your users can find the items they are searching for, and/or quickly narrow their search to the item they are looking for.

So as a general rule of thumb, I strongly recommend you to always define at least one filter on your ECTs.

Fortunately, filter definitions can be done in SharePoint Designer (SPD) and it’s really easy to do it, I will describe how. Filters need to be defined on the read list operations (aka finders, as named in the BDC metadata model). There is a specific dialog to configure a filter for the read list operation as you can see on this image. (Double-click on the read list operation and then hit ‘next’ to get here)

Then follow these instructions:

You need to click on the ‘Add Filter Parameter’ button
Click on the ‘(Click to Add)’ link on the Properties panel to configure the picker. You’ll find the following dialog:
Give the filter a name on the New Filter text box
Select a filter field to be used. In this example I want to filter by ‘Artist Name’ so I select that field.
Define the type of filter to use. For a complete list of filter types and how they work please refer to this article. It’s worth mentioning two frequently used filters types: Comparison and wildcard. For this example, the artist name can have multiple words. (i.e. ‘Franz Kohl’), let’s see what happens when I use each filter type:
- Comparison: If you use the ‘equal’ operator we’ll expect the user to type the full artist name to find items. Hence it will only filter if the user types ‘Franz Kohl’, but will not work with either just ‘Franz’ or with just ‘Kohl’
- Wildcard: If you use the ‘wildcard’ type then the filter will be send to the external system with wildcards. In this case it will filter if the user types ‘Franz’ or ‘Kohl’ (we’ll return all the artists that have the string ‘Franz’ as part of the name or vice-versa).
Finally, identify what to do if the filter value is null. You need to select this option if you have an external list associated with this ECT. Otherwise you’ll see no items when browsing the list (because the filter value will be null).
You can add more filter parameters as needed and add a AND/OR behavior to it. For instance, we are interested to filter by last name and zone.

One very important note regarding the design experience: when integrating specifically with databases, our tools generate the necessary queries to enable the designed filters. If you are using other types of BCS data sources such as web services, WCF or .NET assemblies you need to make sure that your filters are part of their API, before you can model them in SPD.

Here is an example of how the picker dialog is going to look like when we select a ‘wildcard’ type of filter on the artist name and we search for ‘Karen’

Once multiple filters are defined on the ECT, the external item picker algorithm to apply those filters is the following: it appends the resulting items of each filter of each read list operation of the ECT. So to be more efficient we recommend not having a high number of filters/finders on each ECT as this can potentially result in calling a high number of operations on the ECT.

In summary, the most important piece of information for this post is: Always define filters on your ECT, especially when you are going to use it for picking purposes.

Thanks for reading!

- Juan Balmori, Program Manager

Searching External Data in SharePoint 2010 Using Business Connectivity Services

lionelro — Fri, 30 Apr 2010 22:10:50 GMT

Eric White posted great content on how to Search External Data in SharePoint 2010 using BCS. Here is the overview of the post from his blog:

Overview

This article will examine how to use SharePoint Designer to define an external content type (ECT) and how to set up a search crawl that includes that external data. You will then see how to reduce crawl times for large amounts of data by using incremental crawls. All of this will be done using SharePoint Designer, SharePoint Central Administration and some editing of XML.

Before beginning, you should have the following:

Access to SharePoint 2010 Central Administration, including the search service and the business data connectivity service.
Access to SharePoint Designer.
A SharePoint 2010 web application with a site collection. For more information, see Create a site collection (Office SharePoint Server). You can probably use Sharepoint - 80 for these examples.
Access to Microsoft SQL Server. You can use the same instance of SQL Server that SharePoint uses.
A sample database for Microsoft SQL Server (AdventureWorks will be used in these examples. See Sample Databases for Microsoft SQL Server 2008R2 to install.)

These examples will be more meaningful if you have some general understanding of SQL queries and views. Previous experience with XML will also be helpful. Even without that experience, you should be able to successfully complete all the examples.

- Lionel Robinson, Program Manager

Introduction to System Filters

lionelro — Fri, 30 Apr 2010 00:14:33 GMT

Filters are used by Microsoft Business Connectivity Service (BCS) to give values to input parameters while invoking a Method on the External System (LobSystem). But there could be some parameters whose values should be provided by the system (example: username, password) and not from the user. Filters that provide values to these input parameters are called System Filters.

Here is an example Scenario of when to use System Filter: Jim has a web service operation that creates a file. The method takes filename, user name, and password. File name will be passed as user input, but username and password will have to be passed from the system. Jim needs a solution to pass these values without requesting them from the user.

Solution: Jim will have to configure username and password filters for the parameters that match username and password. Jim will also have to specify the “SecondarySSOApplicationID” and “SSOProviderImplementation” on the External System Instance, which will be used by the filters to fetch username and password values. This will make the system provide username and password as values to input parameters.

The different types of System Filters that are supported are:

ActivityID (Activity Identifier Filter)
Activity Identifier Filter is used while calling operations on an external system, and the value to be passed as input parameter should represent a context identity of the invoking operation.
BCS sets a GUID that represents the current operation context as the value on the input parameter which is associated with the ActivityID filter.
Password (Password Credential Filter)
Password credential filter is used while calling operations on external system and the value to be passed as input parameter should represent a password stored in Secure Store.
BCS gets the credentials that map to the External System Instance (LobSystemInstance) properties “SecondarySsoApplicationId” and “SsoProviderImplementation”. It uses the password obtained from credentials as the value for the input parameter which is associated with the Password filter. Note: SecondarySSOApplicationId is different from SSOApplicationId, SecondarySSOApplicationId is used by UserName filter and Password filter. For more information read SecondarySSOApplicationID property on LobSystemInstance.
SSOTicket (Single sign on Ticket Filter)
Single sign on ticket filter should be used while calling an operation on the External System and the value to be passed as an input parameter should represent a sign on ticket for the user from the Secure Store. The target service or application can then redeem this ticket to get the user credentials.
BCS uses the “SSOProviderImplementation” property specified in the External System Instance and requests this provider to issue an SSOTicket. This SSOTicket is passed as the input parameter which is associated with the SSOTicket filter.
UserContext (User Context Filter)
User Context Filter should be used while calling an operation on the External System.The value to be passed as an input parameter should represent the user context.
- In this case, BCS uses the “UserContextType” property on the Filter (FilterDescriptor) to find a value to set on the input parameter. If the “UserContextType” specified is “MachineId”, the value passed to the input parameter will be the unique id for machine’s operating system installation.
- If the “UserContextType” specified is “QualifiedUserName”, the value passed to the input parameter will depend:
  - If the login mode for user is Windows classic, the value will be the windows logon name for the user. This will contain the domain name and the user name (example: domainname /username).
  - If the login mode for user is Claims based, then value passed will be the unique value that identifies the user (example: membership:username, username@provider.com).
UserCulture (User Culture Filter)
User Culture Filter should be used while calling an operation on the External System and the value to be passed as an input parameter should represent the culture of the user environment invoking the operation.
BCS uses the “Format” property on the Filter to find a value to set on the input parameter.
- If the value of the “Format” property is “RFC1766”, the value passed to the input parameter will be the name of the culture. Read about name of a culture.
- If the value of the “Format” property is “TwoLetterISO”, the value passed to the input parameter will be two letter ISO language name of the culture. Read about two letter ISO language name of a culture.
- If the value of the “Format” property is “ThreeLetterISO”, the value passed to the input parameter will be the three letter ISO language name of the culture. Read about three letter ISO language name of a culture.
- If the value of the “Format” property is “ThreeLetterWindows”, the value passed to the input parameter will be the three letter windows language name of the culture. Read about three letter windows language name of a culture.
- If the value of the “Format” property is “LCID”, the value passed to the input parameter will be the LCID of the culture. Read about LCID of a culture.
UserName (User Name Credential Filter)
User Name Credential Filter should be used while calling an operation on the External System and the value to be passed as an input parameter should represent the User Name stored in Secure Store.
BCS gets the credentials that map to the External System Instance properties “SecondarySsoApplicationId” and “SsoProviderImplementation”. It uses the user name obtained from credentials as the value for the input parameter which is associated with the UserName system filter. Note: SecondarySSOApplicationId is different from SSOApplicationId, it’s used by UserName filter and Password filter. For more information read SecondarySSOApplicationID property on LobSystemInstance.
UserProfile (User Profile Filter)
User Profile Filter should be used while calling an operation on the External System and the value to be passed as an input parameter should be a property on the current user’s profile.
BCS uses the “UserProfileProvider” and the “UserProfilePropertyName” properties specified on the Filter to find a value to set on the input parameter. “UserProfileProvider” is used to fetch the current user’s profile and “UserProfilePropertyName” contains the property name whose value on the user profile should be passed to the input parameter.

Configuring System Filters

SPD approach:

System Filters can be configured for input parameters via SPD. The steps to configure System Filters via SPD are:

Open SPD
Click on “OpenSite” and open a site.
Click on “External Content Types” on “Site Objects” pane on the left of SPD.
Click on “New External Content Type” ribbon button to create new external content type.
Click on “Operations Design View” ribbon button to create new methods for external content type.
Click on “Add Connection” button and discover a data source connection.
Enter the method configuration wizard used to design the operations, by right clicking a data source node and selecting the operation that is required.
Move to the Input Parameter Configuration to define filters for input parameters.
Click on the “Click to Add” next to filters to configure filters. The screen shot below shows the link that is used to create filters.
Select New Filter and give it a name.
Use the Filter Type combo box to choose a System Filter as shown in the screen shot below:

Hand Written Approach:

System Filters can be configured by making changes to the model file by hand. The steps to follow to configure System Filters are:

Open the Model file.
Find the method whose input parameters should be configured with system filters.
Add FilterDescriptors section or modify if one already exist, to add filter descriptors that point to system filters. An example to configure SsoTicket System filter is shown below.

<FilterDescriptors>
<FilterDescriptor Type="SsoTicket" Name="NameofSsoTicketFilter">

</FilterDescriptor>
</FilterDescriptors>
Configure your desired input parameter’s type descriptor to point to this FilterDescriptor created in the previous step. An example to configure the above created FilterDescirptor is shown below:
<Parameter Direction="In" ...>
<!-- properties omitted –>
<TypeDescriptor FilterName="NameofSsoTicketFilter" ...>
<!-- properties omitted –>
</TypeDescriptor>
</Parameter>

- Tittu Jose, Software Development Engineer

Accessing BLOB Data from External Systems Using Business Connectivity Services in SharePoint Server 2010

lionelro — Wed, 28 Apr 2010 00:20:06 GMT

Tajeshwar Singh wrote a white paper which shows you how to use Microsoft Business Connectivity Services (BCS) in Microsoft SharePoint Server 2010 to access and surface BLOB data in the SharePoint user interface and search. Check out the overview below taken from the paper.

Link to document: Accessing BLOB Data from External Systems Using Business Connectivity Services in SharePoint Server 2010

Overview of the white paper

Microsoft Business Connectivity Services (BCS) is the new version of Microsoft Office SharePoint Server 2007 Business Data Catalog functionality. New features are added that help retrieve binary large object data (referred to as BLOB data) from external systems and make it available in Microsoft SharePoint Server 2010. This article describes the following:

The functionality that is provided by the StreamAccessor stereotype that is introduced in Business Connectivity Services.
How to use StreamAccessor to retrieve file attachments from external systems for viewing and indexing.
How to write the BDC model that is required to consume BLOB data.
The built-in Web Parts behavior for BLOB data, and how BLOB fields can be indexed by SharePoint Server search.

In this article's scenario, the AdventureWorks database that is hosted in Microsoft SQL Server 2008 is used as an external system that contains the binary data. The BDC metadata model is created with a StreamAccessorMethodInstance to retrieve the BLOB field of type varbinary from SQL Server as an external content type. The BLOB fields are modeled as types that can be read in chunks to help Business Connectivity Services read the stream in chunks, and not load the complete content in memory. This can help prevent out-of-memory conditions. An example of such a type is System.IO.Stream in the Microsoft .NET Framework. An External Data Grid Web Part is configured to show the external items with links to download the BLOB. Finally, Search is configured to crawl the BLOBs and show the results in the SharePoint Server search user interface (UI).

- Lionel Robinson, Program Manager

Microsoft Business Connectivity Services Team Blog

BCS Artifact Generator

Creating a Declarative Solution for Outlook

What are the required artifacts?

Using the Artifact Generator

Steps to use the Artifact Generator

Data Solution

BCS Solutions in Outlook

Intermediate Declarative Solution

Advanced Code-Based Solution

BCS Solution Tools

Further Reading

How do I optimize backend synchronization?

How does the client cache work?

Working with Subscriptions

Optimizing Subscriptions

Explicit Identities

Filtering data

Example

Recommended reading

Is that all?

How do I leverage Secure Store from within my .Net Connectivity Assembly?

No-code approach

Code-approach

Reading metadata model properties

Sample implementation of IContextProperty

Sample implementation for reading credentials

Summary

BCS Logging

BCS Team Channel: Secure Store Service

Why it’s important to define filters for a good picking experience

Searching External Data in SharePoint 2010 Using Business Connectivity Services

Overview

Introduction to System Filters

Configuring System Filters

SPD approach:

Hand Written Approach:

Accessing BLOB Data from External Systems Using Business Connectivity Services in SharePoint Server 2010

Overview of the white paper