I'm looking for a basic guideline/best practices for creating an SSIS setup involving one "master" package called with one variable that will link it to a set of configurations based on that variable. Currently we create a new package for each client, each with their own configuration. Now each package is basically the same but we create an entirely new package file for each customer. There are variables that are of the YES/NO variety to enable/disable specific functions based on client need. As it is now, if we improve the package (a new version if you will) we have to go back to all prior packages to update and make the adjustment. These can be anything from a small SQL code change to an entirely new container of tasks. Either way, you get the idea, this is very inefficient and I am attempting to standardize our packages and process. If anyone could post a few links or point me in the right direction it would be very much appreciated. Thank you!

What we do is keep configuration info in a table. A sql job calls the master package and passes a "JobID" which is the unique ID of a row in the table. The first thing the package does is query the table, get the configuration info, and populate all the variables it needs to run. The only thing we keep in the .config file is the connection string to the database where the configuration table lives.-Tab Alleman

Tab Alleman has the right idea. Assuming you want to create one package (or series of packages) that is (are) "Customer Neutral" , you would need a Cusomer Profile Table in a Configuration database. The first thing that would happen is that the package would determine WHO (which Customer) it is being run for (like maybe a Variable that got passed a value from as /SET operation in DTExec command, then the package would need to query the "Customer Profile table" and get all the right data. But here's the rub: Suppose the Profule table has only two 'properties' for each customer, you query for getting that data would look like this: SELECT [Property1], [Property2] FROM CustomerProfile WHERE CustomerID = ? Now what happens when you add a third property to the table? You need to go and edit that SELECT statement. Maybe better to PIVOT all the properties into a vertical list that would be presented in a table with [Property], [Value], [Data Type] columns. That way, you could extend the table at any time without breaking existing packages.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.