CIS Data Integration – SQL Server

Project Overview

While serving as Data Services Manager at Cogsdale, I led a pivotal project for a major water utility provider in Central Alabama. I developed an automated integration process to streamline the import of meter read files into Cogsdale’s Customer Information System (CIS). Files provided by six different counties in CSV format were processed through a nightly job, significantly enhancing the accuracy and efficiency of customer and location data management.

System Service Provider: MS Dynamics GP – CIS (Azure Cloud MS-SQL Server)

Role: Integration Developer

Project Scope

This integration was designed to automate the nightly import of meter read files, which included customer data, into a staging table for subsequent ingestion by the CIS. The integration was critical for updating or creating records for customer accounts, premise (location) addresses, and the rates associated with these accounts.

Key Responsibilities

Integration Development: Developed a robust SQL Stored Procedure using MS-SQL Server Management Studio, designed to handle complex data transformations and ensure the accurate staging of data.

File Handling and Automation: Implemented SmartConnect to bridge CSV files with the SQL Stored Procedure, automating the data flow from external meter read files into the system.

Customization for Varied Data Formats: Configured two separate integration jobs to accommodate differences in CSV table structures requested by one county, contrasting with the standard format used by the other five counties involved.

Nightly Execution: Set up the integration to run on a nightly basis, ensuring that the most current meter read data was always available for system processing and billing cycles.

Data Accuracy and Integrity: Ensured the integration also updated or created essential customer information and service location details, alongside adjusting rates applicable to each account based on the latest meter readings.

Outcome

The automated integration significantly reduced manual data entry errors and improved the timeliness of data updates in the CIS, leading to more accurate billing and enhanced customer service.

Tools and Technologies used

SmartList (Validation Listing)

Jira (Issue Tracking)

Data Conversion Tools (Custom Scripts and SmartConnect)

SQL Server Management Studio (Stored Procedure)