SharePoint 2013 adds many new features to Lists. In this article, we’ll take a look at how to import Data from a SQL table into a .bdcm file using SharePoint Designer 2013. As you know, the Business Data Connectivity (BDC) Service enables SharePoint to display business data from back-end server applications, Web services, and databases. This walkthrough shows you how to create a model for the BDC service that returns information about contacts in a sample database.

Solution:

We have the following two parts to configure:

1. In the first part of this post, I will demonstrate how to get Data from a SQL table through SharePoint Designer 2013.

2. In the second part, we’ll look at how to import a Database into a .bdcm file using SharePoint Designer 2013.

 

I. How to get Data from a SQL table through SharePoint Designer 2013

• Step 1: Make sure that you’ve created a Northwind database in SQL Server:

 

• Step 2: Go to SharePoint Designer 2013 and click on the Open Site icon:

 

• An Open Site dialog box is shown. You must input the site URL into the Site Name textbox to create the External List.
• Click the Open button:

 

• The Windows Security dialog is shown, requesting your User Name and Password:

 

• Step 3: Go to SharePoint Designer to connect the database with SQL Server as follows:
  • Click on the External Content Types menu on the left pane.
  • Then, select the External Content Types icon at the top.
  • In External Content Type Information, type its Name and click on the External System hyperlink to connect to SQL Server.

You will see a Connection window appear:

 

• Click the Add Connection button:

 

• You’ll see a dialog appear, prompting you to select Data Source Type as pictured below.  Select SQL Server:

 

 

• After selecting the Data Source Type, fill in the SQL information fully, including Database Server, Database Name, and Secure store Application ID as shown below:

 

• Step 4: After connecting successfully, you’ll see Northwind database shown on Data Source Explorer as shown below. We’ll select a Table to create in here right-clicking Table Name and selecting Create All Operations:

 

• A Parameter Configuration is shown after that. Select as many columns as you need, and click the Finish button to complete:

 

• Finally, don’t forget to click the Save button after you’re done. This is pretty important, for obvious reasons:

 

• Now, we have a Northwind database in place, as expected:

 

 

II. How to import a Database into a .bdcm file using SharePoint Designer 2013

 

• Go to External Content Type and click on the Export BDC Model Manage icon in the Ribbon:

 

• Type a File Name and select the location to which you want to save the file:

 

• You will see the .bdcm file after clicking the Save button:

 

• Open the .bdcm file. You will see the structure of the Data Table which you created before:

 

Good luck!

---