How to create a Data Connector data source that accesses data in a database through OLEDB

Products

• IT Management Suite

Issue/Introduction

 This article describes how to create and configure a Data Connector data source to access data in a database through OLEDB (Object Linking and Embedding, Database).

Environment

ITMS 7.x, 8.x

Resolution

 A Data Connector data source is needed to access the user's data so that a Data Connector import rule can later import this into the Symantec Management Platform database. For more information on how to use Data Connector or how to use different data source types, refer to the following article:

How to use Data Connector to import data into the Symantec Management Platform database
181160
 

1. In the Symantec Management Platform Console, click on the Settings button > All Settings.
2. Click on to expand the folders Notification Server > Connector > Data Sources.
3. Right click on the Data Sources folder > New > OLEDB Data Source.
4. Click on the New OLEDB Data Source name and change this to a new name. For example "Data source used for importing computers from a database".
5. Click on "Add description", if desired, to add an optional description.
6. Click in the "OLEDB data source type" drop-down list and select the database type to use. By default, this is set to MS SQL Server database.
   
   If a MS SQL Server database is used:
   
   a. In the "DB server name" field, enter the name of the Microsoft database to connect to. By default, the Symantec Management Platform database name (Symantec_CMDB) is entered, which is probably not where the user wants to import data from.
   b. Click in the "Authentication type" drop-down list and select the authentication type to use. By default, this is set to Microsoft Authentication. If the authentication type is changed to SQL Server Authentication, then fill in the SQL login and Password fields. For either authentication types, this requires a user account that has at least Read permissions to the database.
   
   If an Oracle database is used:
   
   a. In the "Net server name" field, enter the name of the Oracle database to connect to.
   b. In the "User name" field, enter the Oracle user account that has at least Read permissions to the database.
   c. In the "Password" field, enter the user account's password.
   
   Note: It may be problematic to connect to an Oracle/PeopleSoft database from the Symantec Management Platform server due to the specified Oracle user's permissions. It may therefore be easier to have the Oracle/PeopleSoft database export data to a .CSV or Excel spreadsheet using its own export function and then import this instead into Data Connector.
    
7. In the "DB name" field, click on the Refresh button.
8. Click on the "DB name" drop-down list and select the database to use.
9. In the "Table name" field, click on the Refresh button.
10. Click in the "Table name" drop-down list and select the table or view to use. Notes: (1) For tables, it's unlikely that all of the needed columns will all be available. If not, a view or custom import query must be used so that all needed columns are available. For custom import queries, these should be first tested in the database to be sure that they return the expected columns and data. (2) One purpose of performing a database import into the Symantec Management Platform is to only import certain records and their specific associations, such as setting asset owners or computer locations. This article includes two sample SQL scripts that demonstrate how to perform both of these procedures in SQL, and therefore can be used for the import query. These can be pasted as-is into the import query field or the user can adapt/add to these as they desire. (3) When using an import query, if the query returns no records, an error will occur either in the data source on in the import rule stating this. Ensure that at least one record is returned otherwise there is nothing for the import rule to actually do when it runs. (4) When using an import query, it is not recommended to use SQL statements that are not needed for data retrieval. These can result in data loss. With Data Connector 7.5, they may even run immediately, whereas the user would expect them to run when the import rule later runs. For more information about this issue, refer to the following article:
    
    Warning "This query contains at least one of the SQL statements that are unusual for data retrieval" occurs when using a Data Connector OLEDB data source that includes an import query
    KB 181647
     
11. Click on the "Allow import" checkbox to enable it.
12. Click on the "View import data" button to view the data from the database table or view. If the data doesn't appear as expected, try a different table or view or correct the custom import query until the expected data is present.
13. If data is to be pre-processed using C# code:
    
    a. Click on the "Pre-process import data" checkbox to enable it.
    b. Click on the "Edit function" button.
    c. Type the desired C# code.
    d. Click on the "Save changes" button.
    e. Click on the "View import data" button again. If the data no longer appears as expected, correct the C# code until it does.
    
    Note: When setting up the data source, it is not recommended to immediately set up its pre-processing C# code as that may further complicate the configuration. This can be skipped and later added after the data source and import rule are configured and working. For information about how to use C# code for data source pre-processing, refer to the following article:
    
    How to use C# code for Data Connector data source pre-processing
    181197
     
14. Click on the "Save changes" button. The data source should now look similar to the following example:
    
    
     
15. Next, create a Data Connector import rule that uses the data source created in these instructions. For more information on how to create a Data Connector import rule, refer to the following article:
    
    How to create a Data Connector import rule that creates or updates resources
    181748