Implementing and maintaining customer specific variances in source code

I have an ETL application which extracts data from a source database (for which I only have read access) and loads it into my SQL Server database. The extract is run using ODBC queries from a C# application with hardcoded SQL statements. The load is performed by stored procedures in my SQL Server database. This all worked fine initially, but as the number of clients has grown I have had to introduce a set of client specific changes to the extract (c# code) and the load (stored procedures).

开发者_运维百科

My question is what is the best way of dealing with these client specific differences?

Below are my initial thoughs on possible solutions, I'd welcome any comments. It feels like perhaps there a better way to do this via source control? Creating branches for different clients? (current source control is SourceSafe, but I am learing GIT with a view to implementing that soon)

Application Code (C#)

My current thoughts are to add an app.config setting to identify the client and use this to run different methods in the extract.

Stored Procedures (SQL Server)

I could maintain duplicate copies of any procedures which could be client specific, suffixed with the client name. Then on deployment I could delete out the procedures for the other clients and rename the one I want to keep.

So say I have client A and client B I could end up with 3 versions of a procedure:

LoadDepartment()

LoadDepartmentClientA()

LoadDepartmentClientB()

On deployment for client A I delete LoadDepartment() and LoadDepartmentClientB() then rename LoadDepartmentClientA() to LoadDepartment().

This means keeping duplicate copies of procedures which could lead to maintenance complications.

---

Create a dll per customer where each class is defined by an Interface so that the specification is clear.

In this dll you would put the classes/methods which contain the variances.

The loading of the customer specific assembly has to be done by reflection (assembly.loadfrom)

Example:

you have a CustomerA.dll and a CustomerB.dll, both have a class Department with a method Load. The class Department implements IDepartment which is in CustomerHelper.dll.

---

In general a "code/plugin/config" solution is more clear and easier to mantain but sometime the stored procedure way has the advantage of being easier to change on the fly in a production environment.

It's easy to make a fix in a stored procedure via Enterprise Manager or similar tool, but it can be difficult to change a dll on a remote server.