Using Data Source Browser

Connecting to Databases

Centerprise Data Integrator integrates multiple databases into a single user interface. With this application, you can interact with various types of databases simultaneously. The application is designed to make it easy for you to configure and access your data.

This section includes:

• Overview of the Data Source Browser - enables you to launch the Data Source Browser and helps you get familiarized with it.
• Connecting to Single Database - provides step-by-step instructions on how to connect to a single database.
• Connecting to Multiple Databases - helps you work with multiple databases.
• Disconnecting from a Database - provides information on how to disconnect from a database.

Using Data Source Browser

The Data Source Browser helps you connect to and query data on a database server. You can click the Add Database Server button to disconnect from the selected database server.

The Data Source Browser is also used while working with queries.

Steps:

1. Click View>Data Source Browser.

OR

Press Ctrl+Alt+D. The Data Source Browser is displayed.

Using Data Source Browser with Transfers

The Data Source Browser can be used to import data from a Microsoft Excel Worksheet, a delimited file or a fixed length file, into the connected database server in the form of a table. You can right-click the (+) in front of the connected database server to view the available options.

Steps:

1. Right-click (+) in front of the connected database server. The shortcut menu is displayed.

2. Click Import From Excel Worksheet. A new transfer starts with the MS Excel file selected as the source and a database table as the destination.

3. Click the Refresh button to refresh the database server.

4. Expand the database server tree by clicking (+).

5. The new database table is displayed in the list of tables.

You can follow the same steps to import data from a Delimited file or a Fixed length file.

Using Data Source Browser with Queries

The Data Source Browser can also be used to start a query. The connected database servers are displayed in the database browser window. You can right-click the (+) in front of the connected database server to view the available options.

Steps:

1. Right-click (+) in front of the connected database server. The shortcut menu is displayed.

2. Click New Query. A new query starts with a database connection already established.

Using Data Source Browser with Tables

The Data Source Browser provides a number of options for working with the tables displayed in the connected database server. You can right-click (+) in front of the table to view the available options.

Option	Helps to	Action
Display Table Records	Display all the fields and records in the selected table.	Right-click (+) in front of the table and select Display Table Records
Display Table Schema	Displays the schema of the table, i.e. the structure and all the contents of the elements of the table	Right-click (+) in front of the table and select Display Table Schema
Copy/Export	Transfer the contents of a selected table to a Delimited file/Microsoft Excel Worksheet/Database Table. It launches the transfer wizard when any one of the three options is chosen.	Right-click (+) in front of the table and select Copy/Export>Export Table Contents to Delimited File/Excel Worksheet/ Database table
Add Snippet For	Add a SQL code snippet into the active query window to insert, update, or delete a record from the selected table	Right-click (+) in front of the table and select Add Snippet For>Insert/Update/Delete
Script Table as	Create	Add a SQL code snippet to the clipboard/new window/active query window/file to create a new table based on the schema of the selected table.	Right-click (+) in front of the table and select Script Table as>Create>To Clipboard/To New Window/To Current Window/To File
Drop	Add a SQL code snippet to the clipboard/new window/active query window/file to drop the selected table.	Right-click (+) in front of the table and select Script Table as>Drop>To Clipboard/To New Window/To Current Window/To File
Select	Add a SQL code snippet to the clipboard/new window/active query window/file to select the schema of the selected table.	Right-click (+) in front of the table and select Script Table as>Select>To Clipboard/To New Window/To Current Window/To File
Insert	Add a SQL code snippet to the clipboard/new window/active query window/file to insert values in the selected table.	Right-click (+) in front of the table and select Script Table as>Insert>To Clipboard/To New Window/To Current Window/To File
Update	Add a SQL code snippet to the clipboard/new window/active query window/file to update the values in the selected table.	Right-click (+) in front of the table and select Script Table as>Update>To Clipboard/To New Window/To Current Window/To File
Delete	Add a SQL code snippet to the clipboard/new window/active query window/file to delete values from the selected table.	Right-click (+) in front of the table and select Script Table as>Delete>To Clipboard/To New Window/To Current Window/To File
Truncate Table	Delete all the fields and records from the selected table	Right-click (+) in front of the table and select Truncate Table
Drop Table	Delete the selected table	Right-click (+) in front of the table and select Drop Table
Refresh	Refresh the fields and records in the selected table	Right-click (+) in front of the table and select Refresh

Understanding Data Connection Dialog Box

The New Connection Dialog box is a tool used to establish connection to a database server, while working with transfers, batches and queries.

The following table describes the options available in the New Connection Dialog Box.

Option	Description
Recently Used list	It a list of connections that the user has defined recently. This helps to save the user from retyping the information over and over again. The list would be blank when the connection is being setup for the first time.
Data Provider list	It is a list of supported database types. The choices are: SQL Server, Oracle, DB2, Microsoft Access, Sybase, MySQL, as well as the Salesforce service which Centerprise treats as a database type.
Use Windows Authentication check box	It is an option to ’log in’ to the database using your windows credentials rather than supply a user name and password.
Advanced Connection Infobutton	This button launches the Advanced Connection Info dialog box. This is where a user can specify database specific settings such as connection timeout and command timeout.
User Id box	This is the box provided to enter your assigned unique identification information.
Password box	The password assigned to the user is entered in this box.
Server Name box	It is the name of the computer that is running the database management system.
Database list	The database selected is the one that the user wishes to use when he/she connects to the database management system. This list is retrieved when enough connection information is provided so that Centerprise Data Integrator can ask the database management system for this list.
Port box	This box displays the port number that the program should use on the remote machine to communicate with it. The defaults provided are the ones that are typically used by each database management system.
Test Connection button	This button displays the Connection Test Results window, which provides information about whether the connection was successful.

Connecting to Single Database

This section helps you understand how to connect to a database server.

Steps:

1. Click the Add Database Engine button The New Connection Information dialog box is displayed.

2. In the Recently Used box, enter the path of the database management system. Ensure that the Specify Database Information box is checked.

3. In the Data Provider box, click the drop-down arrow and select the type of database.

Note: You can use the Advanced Connection Info box to specify the database specific settings. There are a number of connection attributes that are specific to databases. For instance, in some drivers you can specify Connection Poolsize attribute as well as Command Timeout, Connection Timeout and so on. The actual attributes depend on the database and the driver being used.

4. In the User Id box, enter your assigned unique identification information.

5. In the Password box, enter your assigned password.

Note: Check Use Windows Authentication box if you do not want to provide your User Id and Password. In this case the connection will be authenticated using the credentials provided by Windows.

6. In the Server Name box, enter the full name of the computer that is running the Database Server.

7. In the Database box, enter the name of the database you want to use when you connect to the Database Server.

8. In the Port box, enter the port number that the program uses on the remote machine to communicate with it. The defaults provided for each database type are the ones that are typically used by each Database Server.

9. Click Test Connection. The Connection Test Results window appears.

If the connection was opened successfully, ’TEST COMPLETED SUCCESSFULLY’ will be displayed.

10. Click OK to close the Connection Test Results window.

11. Click OK to close the New Connection Information dialog box.

The database tables are displayed in the work area and the Server name is displayed in the navigation bar. You can refresh the displayed list by clicking the Refresh button.

You have successfully connected to a single database.

Connecting to Multiple Databases

Centerprise Data Integrator gives you the option to work with more than one database at the same time. The steps for connecting to multiple databases are the same as those for Connecting to Single Database. While connecting to multiple databases, you can connect to the existing Data Provider or establish a new connection with a different Data Provider.

The navigation bar displays all the active database connections. You can switch between databases by clicking on the Server name in the navigation bar. The active database appears highlighted in the navigation bar.

Disconnecting from a Database

This section describes the process of disconnecting from a database.

Steps:

1. In the navigation bar, select the database Server from which you want to disconnect.

2. Click the Disconnect Selected Database button. All information related to the database disappears from the Data Source Browser.

You have successfully disconnected from the database.

Changing All Database Connections

Centerprise Data Integrator comes with a handy feature which allows you to quickly change required database connections throughout your transfer setting, using a single UI interface.

This may be useful if you are migrating your transfer setting into a different environment, such as from Beta to Production, in which case all of your database connections would need to be changed.

Using the Change All Database Connections screen, you can replace current database connections specified in the transfer setting with the ones that are appropriate for your target environment.

Examples of database connections that you can change include source connections, destination connections, data model connections, SQL statement map connections, database lookup map connections and others.

Note: When changing database connections, you can only change the connection within the same database provider. For instance, you cannot change a connection from SQL Server to Oracle. However, you can change the connection from SQL Server 2005 to SQL Server 2008.

Change All Database Connections iconis available in the main toolbar. It is also available in Server Administration under Scheduler, on the Change All Database Connections tab.