Data Migration task
The Data Migration task would be used for the following scenarios:
- Rapid extraction of data from one source system a target data warehouse through straight table copy
- Use a SQL query to extract data from a source database and load into a target data warehouse
- Incremental extraction of data for large datasets
- Loading of data from flat files
From the task type screen, select Data Migration.
When using a Data Migration task, Loome Integrate logs audit information in the TaskExecutionRowCount table including row counts and job and task durations. These values can also be viewed in the execution history.
For a Data Migration task, the following information are logged:
- Process Execution ID
- Task name
- Sequence
- Destination table
- Process Start time
- Process End time
- Inserted row count
- Deleted row count
Tip: Data migration fails if importing into a target table with an identity column, unless it is configured as an incremental update.
Task Name
Enter a unique task name here. Task names must be unique to a project.
Source Connection
Select the source connection from the drop down list. Predefined connections will be listed here. Select the schema from the schema drop down list. The available schemas from selected source connection will be populated and listed here. Select the Change to File option (file icon) to use a flat file as a source. The file definition must be defined first.
Target Connection
Select the target connection from the drop down list. Predefined connections will be listed here. Select the schema from the schema drop down list. The available schemas from selected target connection will be populated and listed here. Select the Change to File option (file icon) to use a flat file as a target. The file definition must be defined first.
Tip: For a Data Migration task, the target destination can only be of the SQL Database type.
Tables to Copy
Select the tables you want to copy by selecting them from the box on the left hand side and move them across with the arrows to the box right hand side. The tables listed on the left will be based on the selected source connection and source schema. You can reorder the tables to copy if they have dependencies using the up & down arrows on the far side of the right hand side box.
You can choose to check the Copy All box if you would like to copy all of the tables in the source database across to the target destination.
Tip: If you select Copy all tables, Loome Integrate will copy all tables or files from the source to the target without any reconfiguration when new tables or files are added to the source.
Queries to Copy
As an alternative, rather than selecting the tables to copy, a query can be used to retrieve data from the source to import into target tables.
Under Queries to Copy, click Add a New Query to create a query against the source connection selected above. Specify the table name of the source table. If you wish to rename the table on the target database, specify a target table.
Data will be loaded into this table using the results from the query executed on the source database.
Click Verify SQL to validate the SQL query. You can choose to verify the query at a later time by clicking Bypass. The query will be saved as unverified. Select Insert to save the query. Note that this does not save the task itself, just the query.
Tip: When the new task has been created, a new Incremental Config pane will appear for each table selected or query-based data set defined.
Refer to Table Names, Incremental Data and Index Migration for more information on incremental configuration.
Advanced Settings
Toggle to show or hide the advance settings. Advanced settings are different for each task type.
Subject Areas:
Select Subject Areas for the task if applicable. Refer to Using a Subject Area for more information.
Logging:
You can choose to select task logging.
Select a logging level from the drop down list. Logging options vary depending on the task type selected.
The available logging options for a Data Migration task type are:
- Standard - the default logging level as provided by Loome Integrate.
- Debug - the standard logging level with additional logging to assist with investigation of issues.
Tip: If you have both job logging and task logging on- when job logging is set to Standard then the task logging option will override this. Otherwise job logging will always take precedence.
Action Buttons
When you have entered all the necessary task details, click Save.
An information box will appear to confirm that the task has been successfully saved. Click Close.
Tip: New tasks are added to the bottom of the list as Enabled.