Connecting to Microsoft SQL Server to Define a Transformation Map

Connecting to Microsoft SQL Server to Define a Transformation Map

While defining a Transformation Map using the mapping tool, you will select the Database option from the Insert menu (or use the corresponding toolbar button) to insert a database component.

The dialogs you have to complete depend on the type of database connection you insert and on choices you have made in previous sessions in the mapping tool. The following steps describe some of the most common and important dialogs and choices:  The actual windows you will see and have to complete in order to establish your database connection will vary according to the type of database, driver, previous database selections and other factors. What follows is an example sequence of windows for establishing a new ADO connection to a Microsoft SQL Server database running on your network.

1.  On the first panel of the Connection Wizard, choose Microsoft SQL Server as the database type and click Next.

(If the Connection Wizard is not shown, click the Connection Wizard button to display it)

Note:  These examples will use the Microsoft OLE DB Provider for SQL Server to establish an ADO connection to the MS SQL Server database.  This is the preferred means of accessing MS SQL Server from the mapping tool.  Refer to the MapForce documentation for more information.

It is also possible to establish an ODBC connection if you have an ODBC driver installed for MS SQL Server.  To do so, click ODBC Connections and complete the steps for creating an ODBC connection.  You may need to create a DSN for your server and database.

2.  When prompted to select an ADO provider, choose the Microsoft OLE DB Provider for SQL Server and click Next.

Note: If you have the Microsoft SQL Server Native Client software installed, you can also select the SQL Native Client option from the drop-down list.

3.  The Connection tab of the Data Link Properties window allows you to enter details of the database to which you wish to connect.  Complete the details as described below:

• Type the name of the server that hosts the MS SQL Server data or select it from the drop-down list.  In the illustration, the server name is TEST0101.

• Complete the logon security credentials as required for your server and database.  If you enter a required password, you must check the Allow saving password box.

• Select or type the name of the database on the server that you wish to use.

Important: If you are having connection problems at this point, then you need to check if the MS SQL Server is reachable (ping).  If that is ok, check that the username / password is correct.  If using "Windows NT Integrated Security", then you will need to make sure that the current Windows user of the client is registered as a valid user in the MS SQL Server.  Refer to the security options on the Database for adding a new user on the MS SQL Server Guide.  Additionally, for more information on the error, see the SQL Server logs in the SQL Server Management Studio.

     You can click Test Connection to verify that the connection can be established with the details you have entered.  When complete, click OK to continue.

Important:  If you enter a required password, you must check the Allow saving password box to save the password in the connection string.  If a password is required, it must be saved in the connection string.  Otherwise the connection will fail when MapForce attempts to insert the component, even if the Test Connection succeeds.

4.  When your connection has been successfully established, the mapping tool allows you to choose the particular tables you wish to use in your transformation map:

     Select the database tables you wish to use and click OK. A database component representing the selected tables is inserted in the workspace for the map.