July 22, 2011 at 8:14 am
I have a question on best practice for package configuration. I understand how configurations can help you when moving your package from dev server to qa and then to production. The situation I have is that we have many different sites where the database structure is the same but the data is different. Basically, there is a seperate server and database for each site of our company. We are performing very similar processes at each site that involves moving data and doing transformations on that data. On the database servers at each one of those sites we don't have the rights to set up SQL Server Agent jobs to run the packages. Instead, we have one SQL Server where we do have rights for that and so we've put all our packages on that sever and they go out and do what we need on all of the servers. Because of this, we basically have created identical packages that do certain tasks for each of the different servers/environments. The reason for this is because we are running all the packages from the one seperate SQL server that we have rights on to set up jobs.
I am now in the process of creating some new packages and reworking many of the old ones. If possible, I'd like to consolidate the packages for a group of taks that need to be completed and have one package and then set up a job in SQL Server Agent with individual steps for each enviorment. It just seems silly to continue copying packages to be used for each of our different sites. It's also a real hassle when changes need to be made because you have to go into several packages and make the same changes over and over again. This wastes time and opens the door to mistakes. There has to be and is an easier way.
I'd like to control what server the package is run on with configurations. Is it just as simple putting a configuration setting/file to use on the configuration tab in SQL Server Agent? The other thing I could do is just alter the connection strings on the Data Sources tab of the step in the Agent. Would wither of these options be a suggested best practice over the approach that was taken in the past? Is there another method I may be missing? We are running both SQL 2005 and 2008. Thanks.
July 22, 2011 at 9:56 am
so what you are saying is you want to run the same package on the same server but hit different servers/databases depending on which job step is run?
If so then using the standard config setup would not work since all runs of that package would hit the same connection.
But what you mentioned as far as changing the conn strings in the job step I think would work.
What I would do is specify an OLE connection manager (CONFIG_MASTER) in each package just used for accessing config data. Dont set that one up with a config (you will specify connection string in the job step).
Then in your SQL server where your configs are stored, I would create duplicate config databases one for each Server and store the server specific connection strings there. Aside from the DB name everything else must be duplicated.
In the job step setup I would provide the connection string for CONFIG_MASTER pointing to the proper config database for the server you need to hit.
This way you only need to worry about setting the connection string for that one connection manager.
August 4, 2011 at 10:14 am
I have specified the configurations in SQL and the connection to the Database with an environment variable. For the step in SQL Server Agent I have changed the data source for the DB conneciton but each time it runs it doesn't use that. It continues to use what is in the environment variable and doesn't overrite it. What am I doing wrong/missing?
August 4, 2011 at 10:43 am
I reread Tom's post and went in and removed the environment variable config and am just setting the DB Connection in the job and now it is running and finding the seperate config tables in the different databases and is working. Whoo hoo!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply