Senior Application Developer

Roles: Application Developer – Level 2 Project: SSIS ETL Documentation and Reverse-Engineering Project Anticipated Start Date: May 15th 2024 Anticipated End Date: Aug 15th 2024 Standard working hours: 8:00 AM to 5:00 PM PST Location: Remote ScopeThis is a procurement for an Application Designer/Developer Level 2, to work on the SSIS ETL Documentation and Reverse-Engineering Project for the Mental Health and Substance Use (MHSU) Minimum Reporting Requirements (MRR) Dataset Initiative.The anticipated term of this opportunity is from an available start date of May 15th, 2024 to an anticipated end date of August 15th, 2024, with an option to extend for up to 1 year. All proposed Consultants must be available to start within ten (10) business days of award of this SOW.The scope of this project encompasses the following key areas: 1. MHSU MRR Dataset Data Flows Documentation: • Map out and document all data flows within the SSIS packages related to the MHSU MRR dataset, including source and destination data stores, transformations, and conditional logic. 2. Metadata Documentation for MHSU MRR Dataset: • Document metadata for all SSIS objects related to the MHSU MRR dataset, including package configurations, variables, parameters, connections, and dependencies. 3. Reverse-Engineering SSIS Packages for MHSU MRR Dataset: • Decompile and analyze all SSIS packages related to the MHSU MRR dataset to understand the underlying logic and integrations. • Document findings and recommendations for optimization or refactoring specific to the MHSU MRR dataset. 4. Data Governance Compliance for MHSU MRR Dataset: • Ensure all documentation and SSIS practices align with organizational data governance and data quality standards, with a specific focus on the MHSU MRR dataset. 5. Knowledge Transfer for MHSU MRR Dataset: • Develop comprehensive guides and training materials based on the documentation to facilitate knowledge transfer and training of new staff on the MHSU MRR dataset.The deliverables for this project will include:1. Detailed documentation of SSIS data flows and metadata specific to the MHSU MRR dataset. 2. A report on the findings from the reverse-engineering process of the SSIS packages for the MHSU MRR dataset, including potential areas for optimization. 3. A set of best practices and recommendations for SSIS package development and maintenance with a focus on the MHSU MRR dataset. 4. Training materials and guides for onboarding new team members on the MHSU MRR dataset. 5. A final project report summarizing methodologies, findings, and recommendations for the MHSU MRR dataset.This project requires close collaboration with Island Health's Decision Support Teams and stakeholders to ensure that the recommendations are practical, feasible, and align with the organization's strategic direction and operational needs. The successful proponent will have proven experience in conducting similar assessments and developing technology roadmaps for large organizations, preferably within the public sector or healthcare industry.The maximum number of consultant hours is anticipated to be two hundred fifty (250) with the possibility of adjusting if needed.Successful Proponents are expected to have their own computer equipment to conduct the work and Island Health may provide access to some requirement software as needed.Project ContextThe objective of this project is to document the metadata and data flows within our existing SSIS ETL processes and SSIS objects, specifically focusing on the Mental Health and Substance Use (MHSU) Minimum Reporting Requirements (MRR) dataset. This entails reverse-engineering all SSIS packages associated with the MHSU MRR dataset to gain a comprehensive understanding of the data transformation and loading processes. The goal is to enhance the maintainability, scalability, and efficiency of data integration for the MHSU MRR dataset and to ensure compliance with data governance policies.BackgroundThe MHSU MRR submission has been in place for eight (8) years. The specifications do get revised by the Ministry of Health (MoH) periodically. Island Health’s toolset has changed dramatically, and additional internal requirements have been identified since the technical submission was established. Consultant Qualifications The Application Designer/Developer Level 2 is required to have the following skills and experience: Plan and manage the technical design, development, and implementation of new and/or enhanced business systems applications by: a) identifying and estimating required technical activities; b) defining technical work assignments and priorities; c) monitoring technical work; recommending/implementing solutions to scheduling, logistical and/or operational problems; d) ensuring the involvement of all appropriate technical specialists to meet business requirements; e) lead unit, integration, and system tests to evaluate development outcomes, ensure conformity with user client needs and compatibility with policies and standards;f) ensure quality control performed on all deliverables before handover to Business Consultant; g) ensure development of procedures to facilitate data conversions, data backup and systems security; h) monitor software development work and certify deliverables; and i) plan, lead, and otherwise undertake the development of integrated proof of concept solutions, to verify emerging technologies, products, services, and methodologies, to ensure government understands technical opportunities/exposures, and to ensure deployment minimizes associated risks and threats. In addition to the skills and experience set out above, the Proponents should demonstrate that the proposed Consultant(s) have the following: • Able to meet either physically on Vancouver Island or remotely using virtual meetings utilizing Microsoft Teams, • Be available during Island Health business hours, which are in Pacific Standard Time, • Contractor will need to be able to work in a team environment. Service Requirements The Contractor’s responsibilities will include the following: • Project manage the key areas and deliverables as outlined above. • Summarize progress in a weekly report during the project

Key Skills

• CSS