Using SP2010 BCS Resource Files for BDC Model Settings

You've probably seen way too many Business Connectivity Services (BCS) demos using SharePoint Designer (SPD), showing how simple it is to connect to a SQL Server 2008 database and automagically create external content types and operations, with external lists that can be used both to display and update external data.

Have you ever wondered how to manage those data source connection settings across multiple SharePoint 2010 farms? How do you change the SQL Server name and other login information when deploying to your test and staging farm, and then to your production farm without using SPD again? Even good BCS books such as Professional Business Connectivity Services in SharePoint 2010 have very little coverage of BCS external system settings in Central Admin and of how to actually use BDC resource files. There is a nice end-to-end overview in the Migrating Business Connectivity Services External Content Types in SharePoint 2010 article on MSDN, also lacking some of these details.

Data source connection settings are stored as External System properties that can be configured from Central Admin by managing the BDC service application. This can be a bit confusing, as you might run into the "there are no configurable properties" message when trying to manage Settings for an External System. The trick is to remember that these settings are not for the external system per se, but for a specific external system instance aka connection. Chose the External Systems view in the ribbon, click the link of the applicable external system to see the instances, then select the instance and finally click Settings in the ribbon. Change the connection properties and click OK.

In the example data connection I've used the Secure Store Service (SSS) application for the login information because the target database requires SQL Server authentication instead of passthrough integrated Windows authentication.

Instead of manually changing these External System settings whenever deploying a new version across your development, testing, staging and production farms, you can use BDC resource files to apply the settings. This is done by exporting and importing resource files, either using Central Admin, code or Powershell.

Prototype and test your BCS solution on your development farm first, then package your solution into a feature as explained in How to: Deploy a Declarative BDC Model with a Feature on MSDN. Remember to change the model name and external system name, including the entity namespace into durable names, or at least change the entity version, from your SPD prototype when packing your BDC model.

The declarative BDC model is really just some XML stored in a BDCM file. In addition to the BDC model file, SharePoint 2010 also supports using BDC resource files for specific metadata elements that commonly change, such as SQL Server connection configuration. A resource file is really just some XML stored in a BDCR file, that can be merged with the stored model without deleting the existing model and its configuration from the BCS metadata store.

The simplest way to get a BDCR file to start with, is to export the BDC resources using Central Admin. Chose the BDC Models view in the ribbon, then select the applicable model and finally click Export in the ribbon. Set the file type to Resource and select which resources to include in the exported BDCR file, typically properties, and click Export to save the selected set of resources.

Edit the the resource XML to change e.g. the name of the SQL Server (RdbConnection Data Source) and save your changes, using one file per BDC model and target farm. Only the XML for settings that should be updated when applying the resource file need be in the file.

The edited BDC resource file can now be applied to the applicable BDC model in one of your farms, typically when deploying a new version of a model to the staging or production farm. Chose the BDC Models view in the ribbon, then select the applicable model and finally click Import in the ribbon. Click Browse to select the applicable resource file and set the file type to Resource. Then select which resources to import from BDCR file, typically properties, and click Import to load the selected set of resources.

Validate that the correct settings were imported by reviewing the import log warnings, and by reviewing e.g. the settings for your external system instance or the permissions for the BDC model and its external content types.

See How to: Use a Resource File to Specify Localized Names, Properties, and Permissions on MSDN to include an edited BDC resource file in a feature in your Visual Studio 2010 package. Note that BDC models created with SPD cannot be exported from CA as they are not complete. Such declarative BDC models must be exported from SPD.

Using Dynamic Stored Procedures in BCS Finders

We use quite a lot of Business Connectivity Services (BCS) at my current SharePoint 2010 project both for traditional integration of data from external systems into web-parts and lists, and also for crawling external systems for integrating those system using search and search-driven web-parts.

One of our integration partners prefers to provide their integration points as SQL Server 2008 stored procedures, which is very well supported by BCS. BCS supports both stored procedures and table valued functions, called "routines" in SharePoint Designer (SPD). SharePoint Designer is dependent on being able to extract metadata about the returned table data set when adding External Content Type (ECT) operations or when using the Operations Design View.

Alas, the provided integration sprocs used dynamic SQL statements, and for technical reasons this could not be rewritten to inline SQL select statements. This is as always a problem with tooling such as SPD, as no result set metadata can be discovered. When connecting SPD to the external system, I got no fields in the Data Source Elements panel in the Read List operation's Return Parameter Configuration. Rather I got three errors and a warning.

The workaround is quite simple and requires the use of a SQL Server table variable, which defines the result set and allows SPD to discover the table metadata. Rewrite the stored procedure by declaring a table variable, insert the result of the dynamic SQL statement into the variable, and finally return the result set by reading the table variable. The changes to the sproc is shown in blue in this example:

CREATE PROCEDURE [dbo].[GetFavorites]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @DbName AS NVARCHAR(max) = 'ARISModellering1'

DECLARE @ObjDef AS NVARCHAR(max) = dbo.GetArisTableName(@DbName, 'ObjDef')

DECLARE @Model AS NVARCHAR(max) = dbo.GetArisTableName(@DbName, 'Model')

DECLARE @FavoriteSQL AS NVARCHAR(max) = N'

SELECT

ARISBPDATA.BOOKMARKS.ID AS FavoriteId

, ARISBPDATA.BOOKMARKS.DESCRIPTION AS FavoriteName

, ModelType.ModelTypeName AS FavoriteType

, LOWER(ARISBPDATA.BOOKMARKS.USERNAME) AS UserName

, ''http://puzzlepart/index.jsp?ExportName=ARISModellering&modelGUID='' 

+ ARISBPDATA.BOOKMARKS.DATAKEY AS FavoriteUrl

FROM

ARISBPDATA.BOOKMARKS

INNER JOIN ' + @Model + ' m ON ARISBPDATA.BOOKMARKS.OBJID = m.Id

INNER JOIN ModelType ON m.TypeNum = ModelType.ModelTypeId

'

DECLARE @userFavs TABLE(FavoriteId int not null, FavoriteName nvarchar(max), 

FavoriteType nvarchar(max), UserName nvarchar(max), FavoriteUrl nvarchar(max))

insert @userFavs EXEC sp_executeSQL @FavoriteSQL

select * from @userFavs

END

Refreshing the external system data connection and then creating the ECT read list operation now works fine, and all the return type errors and warnings are gone.

Note that the classic #temp table workaround won't work with SPD, you have to use a table variable in your stored procedure. The sproc will now use more memory, so the BCS best practice for keeping finder result sets small applies.

The table_variable declaration is also a good place to make sure that the identifier column is "not null" and that it is a supported BCS data type such as "int32". External lists cannot be created from an ECT whose identifier field is unsupported, such as SQL Server "bigint", and I strongly recommend using a supported identifier data type right from the start. Getting the ECT identifier wrong in the BCS model will give you problems later on when using SharePoint Designer.

BCS External Lists causes SharePoint 0x80131600 exception for SPSiteDataQuery

Issue: Your SharePoint 2010 code use the SPSiteDataQuery or CrossListQuery and get an exception with code <nativehr> 0x80131600 and absolutely no other helpful details.

Cause: External list referencing an external content type that has been deleted from the BDC metadata store.

Solution: Delete the applicable external lists from your sites - or recover the deleted external content type.

A related error code is code 0x8102003 which is caused by missing list definitions in activated features.

Installing SharePoint SPSF on Visual Studio 2010 SP1

In preparing for the 2011 Arctic SharePoint Challenge next week with my awesome Puzzlepart team, we're installing the SharePoint Software Factory (SPSF) tooling available at CodePlex. There is a nice prerequisite installer that helps you download and install the Guidance Automation Extensions and Toolkit, but it would not install the guidance packages properly on my Visual Studio 2010 SP1.

Installing GAT2010.vsix failed with missing "Visual Studio 2010 SDK", so I downloaded that and got the "you must have Visual Studio 2010 installed" error instead. As it turns out, you will of course need "Visual Studio 2010 SP1 SDK" (download).

Now I've got the SPSF project types in the Guidance Packages section of File > New Project and are ready for #ASC2011.