Configuring the Extractions

After configuring the source and destination connections, you need to import the Sage 200 UK zip templates to create extractions. You then need to validate and build the extractions. This will create the tables and validate all SQL queries or calculated fields. Lastly, you need to run the extractions to populate the database tables.

You can choose between synchronization and consolidation extractions. If you only have one company and want to replicate the database, you will use the synchronization extractions. If you have multiple companies, you will use the consolidation extractions.

Both types of extractions will have an Incremental Refresh extraction and a Non Incremental Refresh extraction. The incremental refresh extraction is used for tables that have a valid Last Modified Date. The non incremental refresh extraction is used for tables that do not have a valid Last Modified Date.

Note

Since some Sage 200 UK tables do not have the DateUpdated field, they cannot be loaded incrementally. However, these tables are often very small and do not pose a problem.

The diagram below illustrates how DataSync processes data.

Refer to the procedures below to create the extractions:

OR

Configuring the consolidation Refresh extraction

  1. In DataSync, import the extraction (see Importing an Extraction for further details):

    1. Click the Import icon located on the upper right-hand corner.

    2. In the Import Extraction window, click the Choose a zip file hyperlink and browse to the location where you saved the DS_202X.0.X.XXX_Sage 200 UK CONSO Refresh.zip file or drag it directly into that window. Click Next.

    3. The default extraction type is already selected. Click Next.

    4. In the Description field, enter Refresh for the name.

    5. Enter the company code in the Unique Identifier field.

    6. Enter CPYID in the Column Name field.

    7. Select the Refresh source connection and the Data Warehouse destination connection.

    8. Click Add in the Destination Schema drop-down list to add the schema; for example, S200.

    9. Click Import.

    10. The Extractions window will automatically switch to the Tables window. Verify your tables and make sure that all fields are present. If some fields are missing, redo the import process.

  2. Validate and build the Refresh extraction:

    1. Click Extractions on the left to return to the Extractions page.

    2. Select the Refresh extraction in the list and click the Validate and Build icon.

    3. In the Validate and Build dialog box, select the Drop the previously created object and recreate the objects... option and click Build.

    1. Wait for the process to be done. A validation report is displayed to give you a quick overview once the process is finished. The results are displayed in the Status column. If there is an error, click the hyperlink in the Error column to view the Log Page.

  3. Run the Refresh extraction:

    1. Select the Refresh extraction in the list and click the Run Extraction Now icon.

    2. Select the tables to be populated.

    3. In the upper-right hand corner, select Truncate and Load and click Run. This will replace all data in your destination with the current data from your source.

    4. Wait for the process to be done. When the process is finished, the results are displayed in the Status column. If there is an error, you can view more details on it by clicking on the hyperlink in the Error column, which leads to the Log Page.

Configuring the consolidation No Refresh extraction

  1. In DataSync, import the extraction (see Importing an Extraction for further details):

    1. Click the Import icon located on the upper right-hand corner.

    2. In the Import Extraction window, click the Choose a zip file hyperlink and browse to the location where you saved the DS_202X.0.X.XXX_Sage 200 UK CONSO NoRefresh.zip file or drag it directly into that window. Click Next.

    3. The default extraction type is already selected. Click Next.

    4. In the Description field, enter No Refresh for the name.

    5. Select the No Refresh source connection and the Data Warehouse destination connection.

    6. Enter the company code in the Unique Identifier field.

    7. Enter CPYID in the Column Name field.

    8. Click Import.

    9. The Extractions window will automatically switch to the Tables window. Verify your tables and make sure that all fields are present. If some fields are missing, redo the import process.

  2. Validate and build the No Refresh extraction:

    1. Click Extractions on the left to return to the Extractions page.

    2. Select the No Refresh extraction in the list and click the Validate and Build icon.

    3. In the Validate and Build dialog box, select the Drop the previously created object and recreate the objects... option and click Build.

    1. Wait for the process to be done. A validation report is displayed to give you a quick overview once the process is finished. The results are displayed in the Status column. If there is an error, click the hyperlink in the Error column to view the Log Page.

  3. Run the No Refresh extraction:

    1. Select the No Refresh extraction in the list and click the Run Extraction Now icon.

    2. Select the tables to be populated.

    3. In the upper-right hand corner, select Truncate and Load and click Run. This will replace all data in your destination with the current data from your source.

    4. Wait for the process to be done. When the process is finished, the results are displayed in the Status column. If there is an error, you can view more details on it by clicking on the hyperlink in the Error column, which leads to the Log Page.

Configuring the synchronization Refresh extraction

  1. In DataSync, import the extraction (see Importing an Extraction for further details):

    1. Click the Import icon located on the upper right-hand corner.

    2. In the Import Extraction window, click the Choose a zip file hyperlink and browse to the location where you saved the DS_202X.0.X.XXX_Sage 200 UK SYNC Refresh.zip file or drag it directly into that window. Click Next.

    3. The default extraction type is already selected. Click Next.

    4. In the Description field, enter Refresh for the name.

    5. Select the Refresh source connection and the Data Warehouse destination connection.

    6. Enter the company code in the Unique Identifier field.

    7. Enter CPYID in the Column Name field.

    8. Click Add in the Destination Schema drop-down list to add the schema; for example, S200.

    9. Click Import.

    10. The Extractions window will automatically switch to the Tables window. Verify your tables and make sure that all fields are present. If some fields are missing, redo the import process.

  2. Validate and build the Refresh extraction:

    1. Click Extractions on the left to return to the Extractions page.

    2. Select the Refresh extraction in the list and click the Validate and Build icon.

    3. In the Validate and Build dialog box, select the Drop the previously created object and recreate the objects... option and click Build.

    1. Wait for the process to be done. A validation report is displayed to give you a quick overview once the process is finished. The results are displayed in the Status column. If there is an error, click the hyperlink in the Error column to view the Log Page.

  3. Run the Refresh extraction:

    1. Select the Refresh extraction in the list and click the Run Extraction Now icon.

    2. Select the tables to be populated.

    3. In the upper-right hand corner, select Truncate and Load and click Run. This will replace all data in your destination with the current data from your source.

    4. Wait for the process to be done. When the process is finished, the results are displayed in the Status column. If there is an error, you can view more details on it by clicking on the hyperlink in the Error column, which leads to the Log Page.

Configuring the synchronization No Refresh extraction

  1. In DataSync, import the extraction (see Importing an Extraction for further details):

    1. Click the Import icon located on the upper right-hand corner.

    2. In the Import Extraction window, click the Choose a zip file hyperlink and browse to the location where you saved the DS_202X.0.X.XXX_Sage 200 UK SYNC NoRefresh.zip file or drag it directly into that window. Click Next.

    3. The default extraction type is already selected. Click Next.

    4. In the Description field, enter Refresh for the name.

    5. Select the Refresh source connection and the Data Warehouse destination connection.

    6. Enter the company code in the Unique Identifier field.

    7. Enter CPYID in the Column Name field.

    8. Click Import.

    9. The Extractions window will automatically switch to the Tables window. Verify your tables and make sure that all fields are present. If some fields are missing, redo the import process.

  2. Validate and build the No Refresh extraction:

    1. Click Extractions on the left to return to the Extractions page.

    2. Select the No Refresh extraction in the list and click the Validate and Build icon.

    3. In the Validate and Build dialog box, select the Drop the previously created object and recreate the objects... option and click Build.

    1. Wait for the process to be done. A validation report is displayed to give you a quick overview once the process is finished. The results are displayed in the Status column. If there is an error, click the hyperlink in the Error column to view the Log Page.

  3. Run the No Refresh extraction:

    1. Select the No Refresh extraction in the list and click the Run Extraction Now icon.

    2. Select the tables to be populated.

    3. In the upper-right hand corner, select Truncate and Load and click Run. This will replace all data in your destination with the current data from your source.

    4. Wait for the process to be done. When the process is finished, the results are displayed in the Status column. If there is an error, you can view more details on it by clicking on the hyperlink in the Error column, which leads to the Log Page.