BCP were moving away from their old legacy systems, which had been developed over time as the company and its needs grew. These systems had been developed separately as needed, did not talk to each other very well, and were in some cases quite old.

They were designing and developing a brand new bespoke application to replace all these systems, support higher volumes of bookings, easier accounting, and better reporting.

This new application would:

• support call centre sales, and booking management
• introduce a new version of their website (www.parkbcp.co.uk) that would support
  • direct online sales
  • online sales through affiliate programmes
  • partners to resell BCP parking spaces through a customised version of the BCP site - rebranded as the resellers site
• support central customer maintainence
• allow central supplier management and maintenance
• introduce massively improved financial data
• provide a data warehouse of client, supplier, financial and sales information
• enable central reporting / management information portal

Amjec were soley responsible for the design and build of the ETL (extract, transformation and load) suite from the legacy application to the new database.

This was a data migration from a Microsoft SQL Server 2000 database, to a Microsoft SQL Server 2005 database.

The ETL for the data migration suite was built using Microsoft SQL Server Integration Services (SSIS). Key areas for migration were identified such as

• clients
• suppliers
• bookings
• finance

The migration process for each of these was broken down into as small pieces, and an integration services package was developed to process each piece.

A master package for each area would control the processing of these smaller packages in the correct order, running the sub packages serially or in parallel where applicable.

A global package was then built to allow the automated running of all migration processing, which would:

• move a copy of the production database to a test server
• create a clean database for the ETL processing
• create staging tables to hold data and facilitate the transformation processes.
• transform the data to the new database schema
• load the transformed data into the new database tables
• manage the primary and foreign keys
• manage constraints
• compressand verify the new database

The Integration Server packages were developed to monitor each stage of the migration process as it ran, and any critical errors would halt the whole suite and report suitable diagnostic information

Upon the successful completion of the suite, the new database created by the ETL process would be loaded to another server - were it was used by the front end application.

During the development process - this would be the test server, and the data provided would be used to help test the project, however ultimately this go to the production server.