RESPEC Project

The New Hampshire Department of Health and Human Services (DHHS) needed to migrate its data on sexually transmitted diseases from the Center for Disease Control and Prevention’s (CDC’s) outdated and inefficient STDMIS system to the new database application called PRISM. Because of RESPEC staff’s experience with migrating data in the healthcare arena, the DHHS’ vendor STC subcontracted with RESPEC for all of the planning, analysis, design, coding, testing, and implementation for the extract, transform, load (ETL) processes for the legacy data conversion. RESPEC analyzed and modified the business rules of a set of conversion scripts supplied by other states that had undergone a similar migration. The PRISM system is a case management and investigation system for supporting patients with a sexually transmitted disease, and for a project of this size, RESPEC used SQL Server 2008R2 with SQL Server Integration Services (SSIS) and SQL Server transaction. Source files were in FoxPro and imported with SSIS into a SQL Server 2008R2 database for staging. The files were then converted into the PRISM database by means of SQL Server transaction files. The project team used WebEx sharing to review work as it was performed. They created data exception reports and summary reports in Excel using SQL Server query language to document data differences and to track the success of each data conversion.