How to import data using a Data Connector Import Rule
|Article:HOWTO79380|||||Created: 2012-08-28|||||Updated: 2014-03-05|||||Article URL http://www.symantec.com/docs/HOWTO79380|
Data Connector can import or export data from various sources, such as from a .csv (Comma Separated Value) or .xls (Excel) file. This requires three parts to work: a data file or database, a Data Source and an Import Rule. This article describes how to do this and also provides examples, best practices, usage and troubleshooting tips. For more information on how to use Data Connector, refer to its user guide:
Symantec Management Platform 7.1 SP2 User Guide (contains instructions for using Data Connector)
Please Note: Symantec Technical Support can help assist the user in understanding how to use Data Connector Import Rules and provide troubleshooting for these. Symantec Technical Support does not, however, offer the services of creating Import Rules for the customer's needs or providing extensive education on how to do this. These services are provided by Symantec Consulting Services, which can be found here http://www.symantec.com/it-consulting-services, and by Symantec Education and Training, found here http://www.symantec.com/products-solutions/training/, respectively.
Part 1: Create a Data File
First, a data file must be created by the user and then populated with values. It is up to the user on how to do this, whether this is manually entered data or data created through an external process. Tip: If a data file is to be used, the first row can be the column names, which are not required but are recommended to enable the Import Rule to more easily match up the columns to the data classes. If these are the same names as data classes, the Import Rule will automatically select these during Part 3.
An example .csv file could look like the following, which will be used for the remainder of this article's instructions. This data file is for Computer records, that will populate their Serial Number, Model data classes, make associations to their Location, and add Cost Items.
PC1, A1234, D745, New York, Purchase, Expense, 8/28/12, 100.00, Incurred, New hard drive was installed,
PC1, A1234,,, Purchase, Purchase Cost, 8/27/12, 50.00, Projected, New keyboard and mouse
PC2, B890, D620, Los Angeles
Note: Data Connector can also use a database instead of a data file. This is described in the following article, of which instructions can be used instead of this article's Parts 1 and 2. If so, implement the steps from HOWTO94484, then continue here starting at Part 3.
How to create a Data Connector data source that can access a database
Part 2: Create a Data Source
A Data Source is next created in the Symantec Management Platform Console. This points to the data file created in Part 1. A Data Source is the Altiris pointer and basic configuration type for the data file. Tip: The data file and Data Source terms are sometimes mistaken for each other. They are, however, separate parts of the process and are both required.
- In the Symantec Management Platform Console, click on the Settings button > All Settings.
- Click to open the Notification Server > Connector > Data Sources folders.
- Right click on the Data Sources folder > New > CSV File Data Source.
- Click on "New CSV File Data Source" and rename the Data Source to the desired name, such as "Computers Data Source".
- Click on the "Allow import" checkbox to enable it.
- Click on the "Import from file" field's Browse button.
- Navigate to and select the data file created in Part 1. Tip: If the file is a .txt file that uses .csv formatting, change the file type drop-down list to "All files (*.*)" to see and then select the *.txt file.
- Click on the Open button.
- Click on the "Save changes" button. The result of these instructions is displayed in Figure 1.
Figure 1: The Data Source
Part 2 Troubleshooting
- To ensure that the data is in the correct columns, click on the "View import data" button. Visually verify that the data and columns appear as expected. If the data is not as expected, edit the data file to then correct any discrepancies.
- The "Pre-process import data" field can be used to pre-process the data file's data before it is used by the Import Rule using C# code. It is recommended to not set this up until the user feels comfortable that the complete Import Rule is working. Later, if this is used and issues then occur, the customer will then know that their code for pre-processing is the cause and can then troubleshoot this to resolve the issue. Information about using C# code with a Data Source can be found in the following article:
How to use C# code in Data Connector Data Source pre-processing
Part 3: Create an Import Rule
Finally, an Import Rule is created in the Symantec Management Platform Console. This points to the Data Source created in Part 2. An Import Rule assigns data classes to the columns found in the data file, and also makes associations or reverse associations to these.
Tip: If the user wants to import resources but isn't sure what data classes to set up, this can become a complicated process. One way of determining what data classes to set is to make an export rule for the resource. This will automatically fill out all of the data classes used by that resource. When ran, assuming that there is data for the resource in question, the output file will then include column names and "sample" data. The user can then evaluate these mappings, to determine how they want to set up an Import Rule to then import the same or not all of the same data classes and associations. Note: Symantec Technical Support is unable to provide a detailed list of what data classes and associations to map to but can provide suggestions, as the guidelines discussed at the top of this article mention.
- In the Symantec Management Platform Console, click on the Settings button > All Settings.
- Click to open the Notification Server > Connector > Import/Export Rules folders.
- Right click on the Import/Export Rules folder > New > Resource Import/Export Rule.
- Click on "New Import/Export Rule" and rename the Import Rule to the desired name, such as "Computers Import Rule".
- Click on the "Data source" drop-down list and select the Data Source created in Part 2. Tip: When the drop-down lists are used, the Import Rule window will refresh, changing other options appropriately.
- In the Column Mappings section:
a. Click on the "Resource type" drop-down list and select the resource type to import. For example, select "Computer" for computer records. Ensure that the appropriate resource type is picked; in general, do not use a generic or wrong type for a specific type, such as "Asset" instead of "Computer". Tip: Many user interface elements, such as drop-down lists, will display a pop-up help window when the mouse is hovered over them.
b. Click on the "Resource lookup key" drop-down list and select the key to best find the resources within the Notification Server. For example, select Resource Name.
c. Click on the Name dropidown list to select what column to use for the "Resource lookup key" to match on. For example, select "Computer Name". Tip: The Name field will automatically be set to the first column in the data file. This will usually need to be set to be the correct column.
d. The "Resource name" drop-down list value is generally automatically set, based on the "Resource lookup key" and Name drop-down list values. In some cases this is optional and can be left blank. In general, it is usually best that this is filled out, however, to ensure data is brought in and configured correctly. Tip: When this field is automatically set, the user does not need to manually assign a data class for the resource's Name.
- In the "Data class mappings" section, set any data classes to match their appropriate columns from the data file. If the data file uses column names in the first row, and if the column names match data class names, these will automatically be set. For the example .csv, however, the Serial column is intentionally named incompletely. The data class Serial Number will not recognize and then set this. Manually click on its drop-down list and change this to the Serial column. Tip: If the expected data classes are not present, click on the "Select data classes" link to manually pick these.
Note: For multi rowed data classes, such as cost items, special consideration is needed in the data to ensure that each record is brought in for the data class. Information on how to do this can be found here:
How to use Data Connector to import multi rowed data classes
- In the "Associations mappings" and "Reverse associations mappings" sections:
a. Click on the "Lookup key" drop-down list for any association or reverse association mappings to set. Use the value selected for the "Resource lookup key" from step 6b. For example, set the Location association mapping's "Lookup key" to Resource Name. Tip: The "Lookup key" is not the column that will be used for the association, this is the "Key column mappings" value from step 8b. Do not set both of the "Lookup key" and "Key column mappings" to be the same value (which normally cannot be done anyway) as this will result in a failed lookup assignment.
b. Click on the "Key column mappings" drop-down list and select the column to use. For example, set the Location association mapping's "Key column mappings" to "Location", which is a column from the data file. Another example, if Users are imported, Asset Owner can be selected to set the Computer ownership, etc. Tip: The "Key column mappings" field will automatically be set to the first column in the data file. This will usually need to be set to the correct column.
Note: When importing Asset Owners or Cost Centers, both the data class mapping and associations must be set or this will fail. The following article describes how to correctly do this:
Import Rule fails to set or removes Asset Owner or Cost Center
- Click on the "Save changes" button. Note: If the Source for data classes, or the Key column mappings for associations/reverse associations are not filled out, when Save is used, and the import rule is later returned to from another area in the Console, the partially completed items will be automatically removed. Save does not record partially entered items. The user should always complete these therefore to avoid having them automatically removed upon saving. Information about this can be found here:
Data Connector import rule doesn't save certain column mappings
Figure 2: The Import Rule
Figure 3: The Finished imported computer resource
Part 3 Troubleshooting
- If a data filter is used, this can only contain basic SQL commands. This may be present and have worked in the past, but an Import Rule may later stop working or skip bringing in certain data classes. To check if the data filter is the issue, #1 remove it temporarily. If the issue that the Import Rule sees disappears the data filter code is the issue. #2 Click on the data filter's "Show data" link. If this returns the error "Failed to apply data filter. An error occurred applying data filter '<data filter>'. (etc.)", there is some fundamental issue with the data filter now, such as invalid column name, etc. The error will often list what the specific issue is. Correct this so that no error occurs before continuing trying to use the Import Rule.
- If during the set up for the Import Rule, the user changes column names (add, rename, delete) in the data file, the Data Source must be re-selected to refresh the mappings for the Import Rule.
- When using the "Select data classes" link, data classes not specific to the resource type will not appear for selection.
- When using the "Select data classes" link, custom data classes will not by appear for selection if they have not been assigned to the resource type.
- Data classes, association and reverse association values must be set to the expected format. For example, the Serial Number data class is a nvarchar (alphanumeric) value of no greater than 100 characters. If the user attempts to import more than 100 characters, this data class assignment may fail and will not be truncated. Likewise, the values must be of the same type. For example, the Accounting Information > Return Date data class is a datetime value. If values are not as expected, the column or row will be skipped.
- When setting the value of a data class that is a foreign key, the foreign key's value must already exist. For example, for the Cost Items > Accounting Code foreign key, any specified values must exist exactly as spelled in Manage > Assets > Financial Types > Accounting Code. If these are not present, the column or row will be skipped.
- When setting the value of a data class that is a foreign key, the Update Mode may need to be changed if multi rowed items are used, such as cost items. The following article describes how this works:
Data Connector Import Rule fails to set a foreign key correctly
- When setting an association or reverse association, the resources to associate to must already exist. The Import Rule does not create these. (A separate Import Rule can be used beforehand, however, to first create these.) For example, when setting the Location association to a location of New York, this location must exist in Manage > Assets > Organizational Types > Location. If these are not present, the column or row will be skipped. The following article describes this in more detail:
Data Connector Import Rule fails to create foreign keys or association mappings
- If errors occur trying to save the Import Rule, these are usually caused by something from the above instructions not being set, left out by accident or misconfigured. Double check that everything has been configured, then retry clicking on the "Save changes" button.
How to troubleshoot import issues when using a Data Connector Import Rule
How to import Software Purchases and Software Licenses using a Data Connector Import Rule
How to import CMDB Solution departments and locations from Active Directory using a Data Connector import rule
How to create a Data Connector import rule to assign a user's department or location
How to create a Data Connector Import Rule to migrate data from an old Notification Server to Symantec Management Platform 7.x
Article URL http://www.symantec.com/docs/HOWTO79380