April 13, 2010 at 4:18 am
Hi I've created an SSIS package in my test Environment. Now i'd like to put it live.
I've 2 questions,
1. Should i have 2 seperate packages one test and one live ? and make change to both.
2. Or should i have one package and make changes to it and then deploy it to put it live as MSDB or Files system.
Thanks
April 13, 2010 at 6:02 am
I believe it is best that you only make changes to packages in the test environment and then deploy them to production. Making changes to production packages is asking for problems.
In my opinion, a good set-up could be:
* packages developed on a test server and they are configured through a config table which resides on the dev server. (and similarly on the production server)
* the connection string in the packages to the config table is set-up through an environment variable (which is different in production then on dev)
* when a package is ready in dev, just copy it to the production server. Since the environment variable on the production server points to the production config table, the package is automatically reconfigured.
(so option 2 of your answer :-))
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2010 at 12:55 pm
I generally agree with da-zero. Packages should be designed in such a way that they work in ALL environments. This is usually handled by config tables or files. Having to change the package in every environment is generally considered poor source control and in medium to large organizations is rarely allowed.
I have mixed feelings on da-zero's setup though. I like the config table because it brings the config into the database but you have to have to config connection setup correctly just to read it. The use of environment variables is sketchy in my view, if you have a formal install process that includes ALWAYS setting this then it might be ok, otherwise it comes off as a one off for the server and I generally try to avoid one offs. All of your config data should be in one place. Either as config files or in a config table, don't mix and match. Data that must be read from a table to make the package work is excluded from that rule in my book.
As far as msdb or file system. That depends on your source control and backup scheme. If you have good source control and deployment management then you know what versions are on the server. In that case the loss of the packages in a DR scenario are limited. However, in either case is pays to have a backup of where they are stored. If you use msdb then when msdb is backed up your packages are too. Otherwise you have to backup the package directory and any config files you might have on the file system.
If you ALWAYS have a particular drive on your SQL servers I would tend to store file based configurations and packages in the same structure. What I mean by that is:
D:\SSIS
D:\SSIS\Config
D:\SSIS\Packages
I would also further segragate the packages by project or logical seperation.
D:\SSIS\Packages\DataWarehouse
D:\SSIS\Packages\DataWarehouse\Fact
D:\SSIS\Packages\DataWarehouse\Dim
I would do this seperation in msdb as well since it allows you to create "directories". I wouldn't want to end up in the same boat as DTS where I have a single list of packages and don't have a solid key as to what each belongs to.
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply