View source for Development/Projects/AABC Data Migration Toolkit/UNBC

{{#pagetitle:UNBC data migration}}
[[Main Page]] > [[Development]] > [[Development/Projects]] > [[Development/Projects/AABC Data Migration Toolkit]] > UNBC data migration


= Overview =

[[File:Pdi nbca transform final.png|thumb|none|800px|Screenshot of final NBCA -> ICA-AtoM '''PDI Spoon''' migration job]]

[http://library.unbc.ca/UNBCArchives/archDisplay/Default.aspx UNBC Archives] is the first participant to be migrated as part of the [[AABC Data Migration Toolkit]] project.  The original project specification included migrating data from MS SQL Server, MS Access and ICA-AtoM (Release 1.0.9) MySQL databases as well as linking over 5,000 digital asset files (image and video).

Data migration from the MS SQL Server data was performed via the [http://wiki.pentaho.com/display/EAI/Spoon+User+Guide '''Pentaho Data Integration (PDI) Spoon'''] (pictured) extract, transform, load tool - see [[AABC_Data_Migration_Toolkit#Technical_Tool_Options|Technical Tool Options]] for other options considered, and reasons for selecting '''PDI'''.

Due to the pilot nature of the project, there were a number of unforeseen technical challenges and the project ran over schedule.

== Original sources for migration ==

One (1) MS SQL Server database backup file
# NBCA_backup_201004080030.bak

Four (4) MS Access databases:
# Fawcett.mdb
# Ray Williston Collection.mdb
# Cassiar_Box1_on.mdb
# Cassiar-Box751onwards.mdb

One (1) archive (ZIP) file containing digital objects (image and video files):
# Uploads.zip

Data from one (1) ICA-AtoM Release 1.0.9 instance (set up in late 2010?), including description and digital assets
# http://www.ica-atom.org/unbc

== Data migrated ==

The final migrated data includes
* The ''NBCA'' MS SQL Server database data as detailed in the [[#NBCA database mappings]] section below
* The digital files from the ''Uploads.zip'' archive
* The ICA-AtoM Release 1.0.9 UNBC website (hosted by Artefactual) including digital assets

== Data not migrated ==

=== MS Access databases ===

None of the four MS Access databases provided were migrated, for the following reasons
* the Cassiar fonds files (''Cassiar_Box1_on.mdb'', ''Cassiar-Box751onwards.mdb'') were never scheduled for migration
* in the course of the project it was determined that the "Ray Williston Collection" data was present in the NBCA (MS SQL Server) database, so migration of the ''Ray Williston Collection.mdb'' database was not necessary
* The ''Fawcett.mdb'' database will be migrated after development of a [[AABC_Data_Migration_Toolkit#CSV Import]] methodology

=== Accession data ===

[http://www.ica-atom.org/doc/Release_1.1 ICA-AtoM Release 1.1] has no provision for storing accession data.  The future [http://qubit-toolkit.org/wiki/index.php?title=Release_1.2 ICA-AtoM Release 1.2] '''will''' include an accession module, and this data will be migrated at a future date.

Accession data is stored in several tables in the NBCA database, and these were not migrated:
# ''Accession'' - accession and appraisal data
# ''Individual'' - most of the data in this table is specific to appraisers and donors
# ''AppraiserSpecialty'' - links appraisers (Individual) to their area of specialty
# ''Receipt'' - accession receipts?

=== Other MS SQL Server tables not migrated ===

# ''SpecifiedLocation'' - locations are specified at the Fonds level, and are very general (e.g. "Photograph room", "Mobile shelving", "On display in library").  UNBC agreed that migrating this data was not necessary.
# ''Lookup'' - contained controlled vocabulary maintained in the ''Lookups'' table was not applicable in the ICA-AtoM application.

== Project deliverables ==

# Migration of all source data to a single [http://www.ica-atom.org ICA-AtoM] [[ica-atom:Release_1.1|Release 1.1]] website (hosted by Artefactual)
# Integration of linked digital assets
# This process documentation

= Methodology =

# Migrate ICA-AtoM Release 1.0.9 data (with attached digital assets) to ICA-AtoM Release 1.1 data schema. See: [[qubit:Upgrading|Upgrading]]
# Install MS SQL Server 2005 Express and load the ''NBCA'' database from backup file provided
# Use the [http://wiki.pentaho.com/display/EAI/Spoon+User+Guide '''Pentaho Data Integration (PDI) Spoon'''] tool to create a data migration script to migrate the ''NBCA'' database to the ICA-AtoM data model
# Attach digital assets from ''Uploads.zip'' archive to ''NBCA'' descriptions
# Refine the data migration script through several iterations
# Set up an install of ICA-AtoM for UNBC to audit the migrated data - including Release 1.0.9 data, ''NBCA'' database data, and all digital assets from both
# UNBC audits migrated data on the auditing site
# Correct any problems with the migration script found in the data audit
# Do the final migration of the data to the live UNBC site (hosted by Artefactual)

= Extract =

== Restore UNBC MSSQL Backup ==

[[File:MSSQL restore database.png|thumb|Restore database dialog]]

* Download and install [http://www.microsoft.com/Sqlserver/2005/en/us/express-down.aspx SQL Server 2005 Express Edition with Advanced Services SP3 ]
* Installion options
** Components: Management Studio Express, Full-text search, Connectivity Components, Replication?
** Mixed Authentication Mode (Windows and SQL Server)
** Add user to the SQL admin role
* Copy NBCA_backup_201004080030.bak to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\NBCA_backup_201004080030.bak
* Run SQL Server Management Studio express
* Right-click "Databases" > Restore
* To database "NBCA" (or whatever).
* From device (browse to NBCA_backup_201004080030.bak)
* Click checkbox next to backup filename
* OK

== Setup MS SQL Server for remote connections ==

=== Create new SQL Server login ===

[[File:MSSQL new login.png|thumb|Create SQL Server login]]

* In left pane of "Microsoft SQL Server Management Studio Express" click to expand "Security > Logins"
* Right-click either "Login" folder or in Login pane and select "New Login..."
* Enter username
* Select "SQL Server authentication"
* Enter password, and confirm
* Uncheck "Enforce password expiration"
* Default database "NBCA"
* OK

=== Give read access to new login ===

[[File:MSSQL user mapping.png|thumb|Give read access to new login]]

* Right-click newly created login, and select "Properties"
* Click "User-Mappings" in left pane
* Click checkbox next to "NBCA" database in top-right pane
* Click "db_datareader" permission in lower-right pane
* Click OK


=== Turn on TCP/IP access ===

[[File:MSSQL configure TCPIP.png|thumb|Turn on TCP/IP access]]

* "Start > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager"
* In left pane, click "SQL Server 2005 Network Configuration > Protocols for SQLEXPRESS"
* Right-click "TCP/IP" in right pane, and click "Properties"
* click "IP Addresses" tab
* For desired TCP/IP connection select "Enabled: Yes", "TCP Dynamic Ports: <blank>", "TCP Port: 1433". OK (You will get a warning that server must be restarted for changes to effect) (See screenshot)
* Right-click "TCP/IP" and click "Enable" (Warning again)
* Click "SQL Server 2005 Services" in left pane
* In right pane, right-click "SQL Server (SQLEXPRESS)" and click "Restart"

=== Troubleshooting TCP/IP config ===

In several instances the static port settings above did not work properly with SQL Server 2005.  Fixing this issue required modification the following Windows registry keys to set the ''Port'' value to ''1433''.
* HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer\SuperSocketNetLib\Tcp
* HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.x>\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

== Connecting to MSSQL with PDI Spoon via JDBC ==

[[File:Spoon jdbc connection MSSQL.png|thumb|PDI Spoon JDBC connection dialog]]

'''PDI Spoon''' is a Java GUI for creating '''PDI''' Transformation scripts. We will use it to connect to MSSQL with the JDBC connector. Please read the [http://wiki.pentaho.com/display/EAI/Spoon+User+Guide Spoon documentation] for general instructions on using the tool.

* Add new "Input > Table input" step
* Double-click the Table input step
* Click the "New" connection button,
* Select the MS SQL Server connector, and fill in the form like the screenshot below
* Click "Test"
* Click OK

<div class="note">''NOTE: The "MS SQL Server (Native)" connector did not work in our tests, so the vanilla "MS SQL Server" connector was used''</div>

<br style="clear:both" />

= Transform =

== NBCA database mappings ==

=== Table: Fond ===

{|
!Source column
!Dest. column
!Notes
|-class="odd"
|Id||keymap.source_id||keymap table maps source keys to target keys
|-
|Accession||note_i18n.content||Store as RAD "Alpha-numeric designations" note
|-class="odd"
|Title||information_object_i18n.title||
|-
|DerivedNameOne||''none''||calculated value based on Title. Not migrated
|-class="odd"
|DerivedNameTwo||''none''||calculated value based on Title. Not migrated
|-
|BeginCirca||event.start_date||Parse regular dates (e.g. "22 February 1960", "Jul. 1983", "1922") to event.start_date (date type).  Copy all (regular and irregular) dates to event_i18n.date (string type)
|-class="odd"
|EndCirca||event.end_date||Parse regular dates (e.g. "22 February 1960", "Jul. 1983", "1922") to event.end_date (date type).  Copy all (regular and irregular) dates to event_i18n.date (string type)
|-
|Restrictions||information_object_i18n.access_conditions||RAD label "Restrictions on access"
|-class="odd"
|Copyright||''none''||All rows are blank in source table
|-
|SourceOfTitle||note_i18n.content||RAD note type: "Source of title proper"
|-class="odd"
|TitleAndAccessNotes||''none''||All rows are blank in source table
|-
|PhysicalDescription||information_object_i18n.extent_and_medium||
|-class="odd"
|Conservation||''none''||All rows are blank in source table
|-
|ScopeAndContent||information_object_i18n.scope_and_content||
|-class="odd"
|Series||''none''||Series list will be derived from parent/child relationship
|-
|PhysicalDescriptionNotes||''none''||All rows are blank in source table
|-class="odd"
|CustodialHistoryPrivate||''none''||ICA-AtoM doesn't currently have a column for storing "private" data.  Migrate with accession data after Release 1.2
|-
|CustodialHistoryPublic||information_object_i18n.archival_history||The "archival_history" column is labeled "Custodial history" in the RAD template.
|-class="odd"
|CreatorHistory||''none''||Source column contains a mix of: creator name, dates of existence and immediate source of acquisition.  Normalized creator name is imported from UNBC edited csv file "creator.csv"
|-
|ProvenanceNotes||''none''||All rows are blank in source table
|-class="odd"
|KeyWords||term_i18n.name, actor_i18n.authorized_form_of_name||Exported to csv file. UNBC manually normalized CSV data to name, place or subject "access points" using Google Refine.  The modified csv file was then imported using '''PDI Spoon'''
|-
|BioAdminHistory||actor_i18n.history||
|-class="odd"
|BioAdminHistNotes||''none''||All rows are blank in source table
|-
|LangaugeNotes||''none''||Only three rows in Fonds table have non-English languages, migrate manually
|-class="odd"
|Record||''none''||One row contains "FOR-17800", migrate manually
|-
|Arrangement||information_object_i18n.arrangement||
|-class="odd"
|FindAids||information_object_i18n.finding_aids||
|-
|GeneralNotes||notes_i18n.content||General note type
|-class="odd"
|ArchivistId||''none''||Ran out of time to migrate the archivist name to "revision history"
|-
|ShowOnWeb||status.status_id||false = "Draft" (status_id: 159), true = "published" (status_id: 160)
|-class="odd"
|CreatedBy||''none''||Machine generated data that is not relevant to ICA-AtoM
|-
|CreatedOn||object.created_at||
|-class="odd"
|ModifiedBy||''none''||Machine generated data that is not relevant to ICA-AtoM
|-
|ModifiedOn||object.updated_at||
|-class="odd"
|Deleted||''none''||Manually remove 'deleted' rows from Qubit.  Can't do this during migration because of data integrity issues, some deleted rows have non-deleted children
|}

=== Table: FondSeries ===

{|
!Source column
!Dest. column
!Notes
|-class="odd"
|Id||keymap.source_id||The keymap table maps source keys to target keys
|-
|Accession||note_i18n.content||Store as RAD "Alpha-numeric designations" note
|-class="odd"
|Title||information_object_i18n.title||''Trim trailing periods from title in transformation''
|-
|DerivedNameOne||''none''||Calculated value based on Title. Not migrated
|-class="odd"
|DerivedNameTwo||''none''||Calculated value based on Title. Not migrated
|-
|FondId||information_object.parent_id||Foreign key self-relation.  Use keymap table to lookup parent (Fond) target key
|-class="odd"
|BeginCirca||event.start_date||Parse regular dates (e.g. "22 February 1960", "Jul. 1983", "1922") to event.start_date (date type). Copy all (regular and irregular) dates to event_i18n.date (string type)
|-
|EndCirca||event.end_date||Parse regular dates (e.g. "22 February 1960", "Jul. 1983", "1922") to event.start_date (date type). Copy all (regular and irregular) dates to event_i18n.date (string type)
|-class="odd"
|SourceOfTitle||note_i18n.content||RAD note type: "Source of title proper"
|-
|TitleAndAccessNotes||''none''||All rows are blank in source table
|-class="odd"
|PhysicalDescription||information_object_i18n.extent_and_medium||
|-
|ScopeAndContent||information_object_i18n.scope_and_content||
|-class="odd"
|PhysicalDescriptionNotes||''none''||All rows are blank in source table
|-
|BioAdminHistory||''none''||Field data is often inconsistent with intended use.  Can't determine authoritative name for person or organization
|-class="odd"
|KeyWords||''none''||All rows are blank in source table
|-
|CustodialHistoryPrivate||''none''||All rows are blank in source table
|-class="odd"
|CustodialHistoryPublic||information_object_i18n.archival_history||The "archival_history" column is labeled "Custodial history" in the RAD template.
|-
|CreatorHistory||''none''||All rows are blank in source table
|-class="odd"
|ProvenanceNotes||''none''||All rows are blank in source table
|-
|Record||''none''||16 rows in database, all part of "Aleza Lake Research Forest Archival Records fonds". Migrate manually
|-class="odd"
|Arrangement||information_object_i18n.arrangement||
|-
|GeneralNotes||notes_i18n.content||General note type
|-class="odd"
|ArchivistId||''none''||Ran out of time to migrate the archivist name to "revision history"
|-
|ShowOnWeb||status.status_id||false = "Draft" (status_id: 159), true = "published" (status_id: 160)
|-class="odd"
|CreatedBy||''none''||Machine generated data that is not relevant to ICA-AtoM
|-
|CreatedOn||object.created_at||
|-class="odd"
|ModifiedBy||''none''||Machine generated data that is not relevant to ICA-AtoM
|-
|ModifiedOn||object.updated_at||
|-class="odd"
|Deleted||''none''||Manually remove 'deleted' rows from Qubit. Can't do this during migration because of data integrity issues, some deleted rows have non-deleted children
|}

=== Table: FondSeriesFile ===

{|
!Source column
!Dest. column
!Notes
|-class="odd"
|Id||keymap.source_id||The keymap table maps source keys to target keys
|-
|Accession||note_i18n.content||Store as RAD "Alpha-numeric designations" note
|-class="odd"
|Title||information_object_i18n.title||''Trim trailing periods from title in transformation''
|-
|DerivedNameOne||''none''||Calculated value based on Title. Not migrated
|-class="odd"
|DerivedNameTwo||''none''||Calculated value based on Title. Not migrated
|-
|FondSeriesId||information_object.parent_id||Foreign key self-relation.  Use keymap table to lookup parent (FondSeries) target key
|-class="odd"
|CreationCirca||event.date||Start date may be copied to event.start_date, if the format is recognized
|-
|SourceOfTitle||note_i18n.content||RAD note type: "Source of title proper"
|-class="odd"
|Copyright||''none''||All rows are blank in source table
|-
|AccessRestrictions||information_object_i18n.access_conditions||RAD label "Restrictions on access"
|-class="odd"
|TitleAndAccessNotes||note_i18n.content||Contains a mix of "Physical condition", "Cartographic scale", Physical location, previous Accession #'s and general notes. Migrated as "General Note"
|-
|GeneralMaterialDesignation ||''none''||All rows are blank in source table
|-class="odd"
|PhysicalDescription||information_object_i18n.extent_and_medium||
|-
|LocationId ||''none''||All rows are blank in source table
|-class="odd"
|ScopeAndContent||information_object_i18n.scope_and_content||
|-
|Conservation||note_i18n.content||"Conservation" note type
|-class="odd"
|PhysicalDescriptionNotes||''none''||All rows are blank in source table
|-
|BioAdminHistory||''none''||Field data is mostly inconsistent with intended use.  Can't algorithmically determine authoritative name for person or organization
|-class="odd"
|KeyWords||''none''||All rows are blank in source table
|-
|CustodialHistoryPrivate||''none''||ICA-AtoM doesn't currently have a column for storing private data.  Migrate with accession data after Release 1.2?
|-class="odd"
|CustodialHistoryPublic||''none''||All rows are blank in source table
|-
|ProvenanceNotes||''none''||All rows are blank in source table
|-class="odd"
|PhotographerOrArtist||note_i18n.content||Migrated as "General note"
|-
|Record||''none''||Appropriate RAD designation is unclear. Ran out of time to migrate (146 rows contain data)
|-class="odd"
|Arrangement||''none''||All rows are blank in source table
|-
|GeneralNotes||notes_i18n.content||General note type
|-class="odd"
|ArchivistId||''none''||Ran out of time to migrate the archivist name to "revision history"
|-
|ShowOnWeb||status.status_id||false = "Draft" (status_id: 159), true = "published" (status_id: 160)
|-class="odd"
|CreatedBy||''none''||Machine generated data that is not relevant to ICA-AtoM
|-
|CreatedOn||object.created_at||
|-class="odd"
|ModifiedBy||''none''||Machine generated data that is not relevant to ICA-AtoM
|-
|ModifiedOn||object.updated_at||
|-class="odd"
|Deleted||''none''||Manually remove 'deleted' rows from Qubit. Can't do this during migration because of data integrity issues, some deleted rows have non-deleted children
|}

=== Table: FondSeriesItem ===

{|
!Source column
!Dest. column
!Notes
|-class="odd"
|Id||keymap.source_id||The keymap table maps source keys to target keys
|-
|Accession||note_i18n.content||Store as RAD "Alpha-numeric designations" note
|-class="odd"
|Title||information_object_i18n.title||''Trim trailing periods from title in transformation''
|-
|DerivedNameOne||''none''||Calculated value based on Title. Not migrated
|-class="odd"
|DerivedNameTwo||''none''||Calculated value based on Title. Not migrated
|-
|FondSeriesFileId||''none''||All rows blank in source table
|-class="odd"
|FondSeriesId||information_object.parent_id||Foreign key self-relation.  Use keymap table to lookup parent (FondSeries) target key
|-
|CreationCirca||event.date||In addition, start date may be copied to event.start_date, if the format is recognized
|-class="odd"
|SourceOfTitle||note_i18n.content||RAD note type: "Source of title proper"
|-
|Copyright||''none''||One value for ''"Bob Bossin’s Home Remedy for Nucelar War" Show''. Migrate manually
|-class="odd"
|AccessRestrictions||information_object_i18n.access_conditions||RAD label "Restrictions on access"
|-
|TitleAndAccessNotes||note_i18n.content||Contains a mix of "Source of title proper", "Terms governing use, reproduction and publication", "Attributions and conjectures" (?) and general notes. Migrated to "General notes"
|-class="odd"
|GeneralMaterialDesignation ||object_term_relation.term_id||Match closest available term in "General material designation" taxonomy
|-
|PhysicalDescription||information_object_i18n.extent_and_medium||
|-class="odd"
|ScopeAndContent||information_object_i18n.scope_and_content||
|-
|Conservation||note_i18n.content||"Conservation" note type
|-class="odd"
|PhysicalDescriptionNotes||''none''||All rows are blank in source table
|-
|BioAdminHistory||''none''||Field data is mostly inconsistent with intended use (mostly "Part of" designation).  Can't algorithmically determine authoritative name for person or organization
|-class="odd"
|KeyWords||term_i18n.name, actor_i18n.authorized_form_of_name||Exported to csv file. UNBC manually normalized CSV data to name, place or subject "access points" using Google Refine.  The modified csv file was then imported using '''PDI Spoon'''
|-
|CustodialHistoryPrivate||''none''||ICA-AtoM doesn't currently have a column for storing private data.  Migrate with accession data after Release 1.2?
|-class="odd"
|CustodialHistoryPublic||''none''||All rows are blank in source table
|-
|ProvenanceNotes||''none''||All rows are blank in source table
|-class="odd"
|PhotographerOrArtist||note_i18n.content||Migrated as "General note"
|-
|Record||''none''||Appropriate RAD designation is unclear. Ran out of time to migrate (3561 rows contain data)
|-class="odd"
|Arrangement||''none''||All rows are blank in source table
|-
|GeneralNotes||notes_i18n.content||General note type
|-class="odd"
|ArchivistId||''none''||Ran out of time to migrate the archivist name to "revision history"
|-
|ShowOnWeb||status.status_id||false = "Draft" (status_id: 159), true = "published" (status_id: 160)
|-class="odd"
|CreatedBy||''none''||Machine generated data that is not relevant to ICA-AtoM
|-
|CreatedOn||object.created_at||
|-class="odd"
|ModifiedBy||''none''||Machine generated data that is not relevant to ICA-AtoM
|-
|ModifiedOn||object.updated_at||
|-class="odd"
|Deleted||''none''||Manually remove 'deleted' rows from Qubit. Can't do this during migration because of data integrity issues, some deleted rows have non-deleted children
|}

=== Table: Attachment ===

{|
!Source column
!Dest. column
!Notes
|-class="odd"
|SourcePath||digitalobject.path||See [[#Digital asset import]] below for details
|-
|colspan="3" | ''Remaining columns in this table were not migrated''
|}

=== Table: FondSeriesItemAttachement ===

{|
!Source column
!Dest. column
!Notes
|-class="odd"
|AttachmentId||digitalobject.information_object_id||See [[#Digital asset import]] below for details
|-
|FileSeriesItemId||information_object.id||See [[#Digital asset import]] below for details
|- class="odd"
|colspan="3" | ''Remaining columns in this table were not migrated''
|}

== ICA-AtoM Release 1.0.9 data ==

Data from the existing http://ica-atom.org/unbc database was migrated to ICA-AtoM Release 1.1 data schema via the standard ICA-AtoM [[qubit:Upgrading|upgrade]] process.

== Other transform sources ==

[[File:PDI-AccessPointEntities.png|thumb|'''AccessPointEntity.ktr''' '''PDI Spoon''' transform script creating actor (name) and term (subject, place) rows from access points]]

[[File:Unbc-access-point-relations-ktr.png|thumb|'''AccessPointRelation.ktr''' '''PDI Spoon''' transform script creating description->actor (name) and description->term (subject, place) relations from access points]]

=== Creator name ===

The NBCA.Fond table had no standard field for recording the creator of a Fonds, so the ''CreatorHistory'' and ''Keywords'' columns were used in an ad-hoc manner.  Because neither the name format nor the column used was consistent, there was no automated way to extract the name of the Fonds' creator.  The solution was to create a CSV export file from the NBCA ''Fond'' table and for the UNBC archivists to manually enter a creator name, where the creator was known.

The ''creator.csv'' file was created by export of the NBCA ''Fond'' table (columns: Id, Title, CreatorHistory and BioAdminHistory) and was then manually edited by the UNBC Archives staff. The edited ''creator.csv'' file is loaded into ICA-AtoM by the '''PDI Spoon''' Transformation "Creators.ktr".

=== Keywords ===

In the NBCA ''Fond'' and ''FondSeries'' source tables, the ''Keywords'' column contains a semi-colon separated list of strings.  This data is defined via  "Access Points" in the ICA-AtoM model.  Because ICA-AtoM distinguishes between name, subject and place access points, and there is no automated way to categorize all of the original strings, this required a multi-part process:
# Export a comma-separated file containing all ''Keywords'' column data from the NBCA database (only present in ''Fond'' and ''FondSeries'' tables)
# Run automated normalization and data-cleaning using [http://code.google.com/p/google-refine/ Google Refine] (Artefactual)
# Manual data cleaning via '''Google Refine''' (UNBC)
# Import of cleaned and normalized CSV file via '''PDI Spoon'''

The complex data structure of ICA-AtoM further complicated the import process (step #4) as can been seen in the '''AccessPointEntity.ktr''' and '''AccessPointRelation.ktr''' screenshots.

=== Digital asset import ===

Digital assets locations were exported from the NBCA MS SQL Server database to a CSV file via '''PDI Spoon''' transformation script then the actual digital assets were imported and linked to a description via the [[AABC_Data_Migration_Toolkit#Digital_object_load_task|digitalobject:load]] task.  The transformation script also mapped the source id (''FondSeriesItemAttachement.FondSeriesItemId'') to the target key (''information_object.id'') via the ''keymap'' table, and did a string search & replace on the ''Attachment.SourcePath'' column to substitute the local path for each digital asset.

<br style="clear:both"/>

= Load =

== Load existing ICA-AtoM install data ==

Data from the http://ica-atom.org/unbc site can be loaded via [http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html mysqldump] backup file. This step must be performed before any other migrations because it destroys any existing data in the target database.

== Load NBCA data ==

The '''PDI Spoon''' ''NBCA_Transform.kjb'' job writes data directly to the target ICA-AtoM MySQL database.

== Load digital objects ==

Digital objects are loaded using the [[AABC_Data_Migration_Toolkit#Digital_object_load_task|digitalobject:load]] task using the [[#Digital assets|nbca_digitalobjects.csv]] file to link assets to archival descriptions.

= Post-processing =

Several final tasks must be performed on the final ICA-AtoM instance data which require direct access to the ICA-AtoM/symfony framework

# Build nested set - See: [[AABC Data Migration Toolkit#Build nested set task|Build nested set task]]
# Generate permalink slugs - See: [[AABC Data Migration Toolkit#Generate slugs task|Generate slugs task]]
# Build search index - See: [[qubit:Rebuild search index|Rebuild search index]]