October 29, 2007 at 11:14 am
Looking for seamless migration of SSIS pkg. My current design looks like this:
Define 2 config files on server A (all using Windows Auth)
- 1 config file containing all DB's for Server A called \\ServerA\ConfigFiles\ServerA_dbs.dtcconfig
- 1 config file containing all DB's for Server B called \\ServerB\ConfigFiles\ServerB_dbs.dtcconfig
Define 2 environment variables on my local machine:
- Name=SSISConfigPathServerA Value=\\ServerA\ConfigFiles\ServerA_dbs.dtcconfig
- Name=SSISConfigPathServerB Value=\\ServerB\ConfigFiles\ServerB_dbs.dtcconfig
Define 2 environment variables on my Servers A and B:
- Name=SSISConfigPathServerA Value=\\ServerA\ConfigFiles\ServerA_dbs.dtcconfig
- Name=SSISConfigPathServerB Value=\\ServerB\ConfigFiles\ServerB_dbs.dtcconfig
Create SSIS pkg's in VS2005 and deploy to ServerA File System____________________________________________________________
I have 3 different environments (Dev, QA, PROD) each containing ServerA and ServerB:
Dev: Dev_ServerA, Dev_ServerB
QA: QA_ServerA, QA_ServerB
Prod: Prod_ServerA, Prod_ServerB
As a production support DBA, I'm used to SQL 2000 DTS pkg execution - where I simply expand Ent. Mgr, my registered server, Local Packages, right mouse click the pkg - and execute.
In SSIS, using the design listed above, and running from Mgmt Studio (w/ registered IS servers) my SSIS pkg execution will inherit my local desktop environment variable defintions.
Is there a way to force the SSIS pkg execution (invoked from my local desktop) to inherit the target SQL Server 2005 environmnet variable settings?
October 31, 2007 at 8:04 am
Has anyone seen SSIS pkg portability (from Server A to Server B) work properly using the following design? Steps I used:
FYI: Server "A" resides in our Development environment; Server "B" resides in our QA environment
I CREATED Server A XML dtsconfig files
I CREATED Server B XML dtsconfig files
I CREATED Server A staticly named Environment System Variables pointing to Server A specific location/files containing the Server A XML dtsconfig files.
I CREATED Server B staticly named Environment System Variables pointing to Server B specific location/files containing the Server B XML dtsconfig files.
I built a simple SSIS pkg on server A.
On Server A's version of the SSIS package, I Enabled Package Configurations to utilize Environment Variables (the Environment Variable NAMES are the same on both Server A and Server B; the Environment Variable VALUES point to the location of the XML dtsconfig files on Server A and Server B, respectively.
The SSIS package executes perfectly on Server A. Now I'm ready to deploy to Server B (using the manifest file method - into msdb)
When I deploy the SSIS Pkg to server B, referencing the same predefined Environment System variable NAMES, and execute --- the Pkg CONTINUES TO OPERATE ON Server A objects. I cannot get the package to automatically inherit the Server B dtsconfig files. (Yes, I can manually override the Configurations at runtime by manually changing the Server Names but the idea here is to avoid manual intervention - especially when were moving dozens SSIS pkg's from Development into QA into PROD)
Any thoughts? Has anyone successfully implemented a seamless deployment process? (seamless meainng you don't have to change ANYTHING during deployment!!)
November 1, 2007 at 12:30 pm
Question is, when will Microsoft provide seamless portability for SSIS pkg's? I thought it would be available by now but apparently not. Unless someone knows the trick (read my attached post), you STILL need to manually change a setting somewhere along the migration path from DEV - to QA - to PROD. Whether it's in your Environment Variable, your config file, or your pkg execution parameters - manual intervention is still required.
November 1, 2007 at 1:11 pm
Can't answer your question(s), yet, as we only have our Data Warehouse development environment at this time. We are still awaiting our QA and Production systems. Here is what i can tell you. We develop our SSIS packages on our workstations against the Development databases (Stage, ODS, DW). We have a config file on our desktops that tells the SSIS packages where to look for the configuration database information that provides the configuration information for the various databases and files. There is also a file on the server for the same purpose. This file on both our workstations and on the server(s) is in the same location.
Once we have our QA and Production environments, I hope to be able to better answer your questions, as we want to ensure that as we move packages through the various systems (Dev, Test, QA, Production) that is done with as little manual intervention as possible. We don't want to have to touch every package as we promote new and updated packages.
😎
November 4, 2007 at 5:00 pm
Your approach should give you a passive deployment process if you use indirect configurations. See the attached screenshot.
Your environmental variable has the same name on your workstation and across environments but their contents are specific to the environment.
The package configuration then looks for an environmental variable with the specified name that holds the environment specific path to the dtsconfig file, finds the file then sets the connection managers and your package should run.
Our deployment process uses an environmental variable to set a connection string that supports the remainder of the configurations which are all SQL Server config types. It requires no intervention and has worked well.
November 4, 2007 at 5:03 pm
One thing I do to test package configurations is to change the connection string property for every connection manager in the package. I usually replace the server name with “NoServer” so when the package is opened the connections will show errors if the configs don’t work. In a package with a lot of connections I usually just open the dtsx file in a text editor and do a find and replace, then save and reopen in BIDS.
That will let you know the configs are working and you are not just getting the server names that persisted from when the connection managers were initially added to the package.
If you change the value of an environmental variable you will need to close and reopen BIDS for it to recognize the changed value.
November 4, 2007 at 6:25 pm
We may (will) have to revisit how we are doing configurations. The process we use now works fine between developing on our workstations and deploying to our development system. The issue we may now be faced with is multiple instances of Sql2K5 on a single server. SSIS is not instance aware, so it could be running a package on any instance, and if the configurations are different, our current process won't work as currently implemented.
Where I work is looking at server consolidation, and this is why I am relooking at our process.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply