When attempting to load an XML file into CA-CMDB using the GRLoader utility, GRLoader may give an error that a date value is unparsable or has an invalid format. If the XML was created by an Advantage Data Transformer script, it will have been generated with a string date in the format Mon dd, yyyy. GRLoader requires yyyy.mm.dd. Both are valid date formats, but for GRLoader, the date generated by ADT needs to be converted to the format acceptable to GRLoader. The attached ADT Reusable Transformations can be imported into Mapper and used to convert values of any date datatypes to strings in the proper format for GRLoader.
Whether the date is coming from Excel or a database such as SQL Server or it's the System Date, you'll need to convert it to the format GRLoader wants as input. GRLoader doesn't care if the value includes the time or not, but the date format is year first and dots between date parts. Attached is an ADT export file containing a set of Reusable Transformations to do this. GRLoader will load a default time of 12:00 AM, ignoring the time value in the XML if it exists.
Reusable Transformations to convert dates to the format required by GRLoader.
Use this when the source is a date datatype such as in SQL Server or Excel.
Use this when the source date includes time such as in SQL Server datetime datatype.
Use this when the source should be the current date derived from the system. This RT uses ADT's GETDATE() function.
If you get a script generation error about mismatched types, DATE and DATETIME, then you may need to switch between "Date_2_YYYYMMDD_wDots" and "DateTime_2_YYYYMMDD_wDots".
Instructions for installing Reusable Transformations.
- Download the attached DatesToGRLoaderFormat.zip. Unzip it to any convenient location to extract two files: HowtoConvertDatesForGRLoader.docx (this document) and ConvertDates_to_GRLoaderFormat.xml.
- Open ADT Script Manager. Look in MainFolder for folder ZSystem_Fragments_Library, and in that folder, for fragment "Transformation_Definitions".
If it exists, go on to step 3.
If it does not exist, do steps 2a and 2b.
- Copy x:\Program Files\CA\Advantage Data Transformer\Examples\starterkit\dumpSystemTransformations.dmp to x:\Program Files\CA\Advantage Data Transformer\bin\ip<today's date>.dmp such as
x:\Program Files\CA\Advantage Data Transformer\bin\ip20110915.dmp
- Load these sample fragments and scripts using the Script Load Utility by one of these two methods.
- Start, Programs, Computer Associates, Advantage, Data Transformer, Script Load Utility
You will be asked for infopump login credentials.
A command window will open and close quickly. Messages are not saved.
Go to Script Manager and confirm that the "ZSystem_..." folders have been imported into the MainFolder.
- If you wish to watch the messages, use the command line to run this load.
Open a command window and go to the x:\Program Files\CA\Advantage Data Transformer\bin directory. Enter ipload -h to see the syntax. Your command will be like:
ipload -D ip20110915.dmp infopump MyPwd
where MyPwd is your infopump password. If the dmp file is not in the same directory as the ipload executable, then the -D value must be a fully-qualified path and file name enclosed in double-quotes.
- Open ADT Mapper and import the ConvertDates_to_GRLoaderFormat.xml.
This will add folder ConvertDates_to_GRLoaderFormat to the DEV status of the Transformations tab in the metadata store.
- Open your program and drag one of the Reusable Transformations onto the palette.
- Draw a transform line from the source date to the input parameter (DateIn) of the RT, unless the source is system date.
- Draw a transform line from the output parameter (DateOut) of the RT to the target date field in the XML.
- Delete the transform line that was previously going directly from the source date to the target string.
- Regenerate the script.
Note about modifying Reusable Transformations
When any instance of an RT is modified, that change is propagated to every instance - on other programs as well as in the metadata store - so it's recommended that before making any changes, backup the original Reusable Transformation. If you find later that you need that original form in another program, it will still be available.