March 10, 2011 at 8:58 am
My next set of questions are perhaps a bit easier to divulge. :unsure: Are there best practices defined for deploying SSIS packages that utilize configuration databases?
Should there be a "Deployment Staging" BIDS SSIS project on the production server so that the DBA deploying the package can bring it in to view variable initializations and open connection managers to verify connection strings are valid and match what is in the Config DB? Then they can use the Save Copy of Package As... to deploy to final deployment location?
Should the DBA right-click open with BIDS on the .dtsx file to open the package and verify values before saving the package to the final deployment location?
Is there a difference between copying a .dtsx file to a location and saving a .dtsx file (Using afore mentioned Save Copy of Package As...) to its final deployment location?
Karl Lambert
SQL Server Database Administration
Business Intelligence Development
March 10, 2011 at 9:30 am
I would certainly hope the DBA verifies the values and connection before deploying!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 10, 2011 at 10:17 am
I guess I'm more looking at is what happens to the SSIS package when the DBA does that. When the DBA opens the package, the configs are loaded from the configuration database. Now the static data in the package reflects what is in the config database and not from the last time it was saved, in QA for example. There are two events that cause an SSIS package to retrieve values from the config DB. One is on package load, and I thought the other was on package execute. Does this happen for all values in a package that are being controlled by the config? Does it refresh the variable values as well as the connection manager values, or do you have to open the connection manager to refresh those?
We saw some strange things happen in our very first deployments using a config database, and I am trying to get the root cause of what we saw. I think the loading of config values is playing a big role.
Karl Lambert
SQL Server Database Administration
Business Intelligence Development
March 11, 2011 at 6:48 am
I haven't run into that issue, but it seems you could test it pretty easily by logging the values.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2011 at 8:09 am
What I usually do:
* script out a "deployment script" using dtutil
* place the script into a folder that contains the to be deployed SSIS packages
* only the DBA has the necessary rights to execute dtutil to deploy packages to test/production
* first deploy to a test server, so you can check if configurations are picked up correctly. Not by opening the packages, but by running them and checking the logs. You should assume there are no client tools on the server. Check for warnings that display if a configuration couldn't be applied.
* if everything works on test (QA), deploy the packages to production using the same dtutil scripts (but change destination server of course).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 11, 2011 at 10:15 am
I use a script with DTUtil as well. My goals is to not change the package at all once it is released from Dev. I often use config files.. Basically I on board with Koen..
CEWII
March 11, 2011 at 2:14 pm
Elliott Whitlow (3/11/2011)
I use a script with DTUtil as well. My goals is to not change the package at all once it is released from Dev. I often use config files.. Basically I on board with Koen..
I'm more of a config table kind of guy. Because I just really hate XML 😀
(I didn't pay attention in class when they explained it to me, and I guess it never stuck...)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 11, 2011 at 3:01 pm
Tables or files, I think that is largely style and environment.
My big thing is that if I were to compare the DTSX files I released from dev to the one in production, they had better not have 1 byte different.
That is how I develop packages and when I set the rules how I make sure its followed. Editting in production should be a rare event..
CEWII
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply