Skip to content
March 12, 2009 / red1s

Connecting MOSS 2007 to SQL Data using BDC and the MetaMan tool


My company recently acquired a new tool to create connections to SQL server much quicker! – this is much quicker than me usually creating ADF files manually using the MS provided tools

This tutorial is split up into 2 sections to assist you with understanding the stages of the deployment process

Section 1 – Deploying the data enabled content to your SharePoint site

Open up BDC Metaman once you’ve installed it and I’d recommend you to configure the admin site to work directly within BDC Meta Man by configuring the SSP portal: (Note this only works correctly if the tool has access to the machine you’ll be deploying the ADF files to – if not then skip this step)

clip_image001

Disregard if you didn’t complete above step – Part of the previous step – This will allow you to tab at the top of the settings page

clip_image002

Open the ‘Connect to data source’ menu :

image

Configure the neccessary connection:

image

Once you’ve successfully configured it you should have the following explorer view present itself:

image

Expand the required DB and drag the necessary tables onto the left pane work area:

image

Connect multiple tables by selecting a row and connecting it to the connected table.  Once you’re happy that your design is correct:

clip_image004

Once you’ve created the necessary content in BDC Meta Man you’ll need to configure the connection file, which comes out as an ADF XML file

image

image

Hit the run key now that the configuration has been created

image

If it is successful it will notify you that the action was successful:

image

If you click next you will be taken to the generated XML file: Which will allow you to review the connection details located therein:

image

Now that you have your file – go into your BDC Import Application Definition area of your Shared Services Provider for your farm

image

clip_image008

Once the import occurs successfully after you have located the previously created ADF file

clip_image009

You should now see the below information regarding the application definition file imported:

image

Here are the Entities which you’ve imported and have the ADF created for:

clip_image011

Section 2 – Deploying the data enabled content to your SharePoint site

Lets go ahead and add the data enabled content to our SharePoint site

Go into your SharePoint site:

image

Edit the page and Add the following webparts to the page:

  • business data list
  • and the business data related list webpart

clip_image013

Now you’ll need to configure these newly added webparts:

clip_image014

Firs the Business Data List:

clip_image015

Select the Business Data list from the list picker

clip_image016

You’ll notice the 2 entities that you imported:

image

The webpart will now show the newly added webpart

clip_image018

And once you select save the webpart will show the data which exists within these tables:

Webpart Data View:
clip_image019

SQL Table View:
clip_image020

Select the business data related list and from there choose the related data instance:

clip_image021

In order for this webpart to work you’ll be notified to connect it:

clip_image022

This can be done by opening the edit menu And selecting the ‘Connections’ from the drop down menu

clip_image023

Your business data list will now look like a choice list ;

clip_image024

By selecting one of the choices, the related data list will now only show values relevant t that fey id:

Selection for no.:1

clip_image025

And selection for No.: 2

clip_image026

So there you have it, and now you’re able to expose SQL data in SharePoint effortlessly ! enjoy Hot smile

Have a look at my follow up post on how to edit SQL data just as effortlessly!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: