One of the time-consuming elements of any migration project is the copying of data between source and destination tables. Typically, after source tables have been identified, SSIS scripts will be used to extract the data, make any necessary transformations, monitor progress through logging and testing, create destination tables for that data type, and then load them with the transformed data.

So even the most straightforward data transfer will take several minutes to set up, and that adds up to many hours if a large number of tables with complex transformations must be migrated and tested.

Analysts at KEY Intelligence have now developed a transfer tool to significantly reduce that time requirement and improve consistency of approach from one table to the next. Using primarily SSIS, but with some additional coding, the tool automatically identifies data types in selected source tables, creates matched destination tables, undertakes the transformations and transfer, and logs the number of rows in source and destination tables on completion, to indicate that the operation has succeeded.

The tool is already being used by KEY Intelligence staff, and being further developed ‘on the job’. We anticipate sharing it with selected clients over coming months to spread the benefits more widely.