February 18, 2009 at 3:14 am
February 18, 2009 at 12:50 pm
No, because the entire package is stored as binary data in sysdtspackages90.
Greg
February 18, 2009 at 1:04 pm
Thanks Greg
I was afraid of that when I saw that column earlier.
there doesn't seem to be any way to query the MSDB-stored packages for any package level information at all, like one could do for DTS packages via sp_OACreate, either.
Oh well. I was hoping to be able to write a script, somehow, to change the server names in the connection managers, in order to avoid manually changing a load of packages in Visual Studio, and then saving them to MSDB, one by one. Looks like I've got an exciting task to look forward to tomorrow :w00t:
Thanks again
February 18, 2009 at 3:38 pm
Have you looked at putting the connections in configuration files? I would think you'd be able to easily modify the config files without having to edit each package.
Greg
February 18, 2009 at 4:56 pm
I have indeed, unfortunately there is not only not enough time to implement this(*), but also our test server is effectively our live server (I know, I know), so that would cause problems in itself, apparently.
(*) I have to do this as soon as poosible,which doesn't allow for any configuratuon-related testing, etc (given I have no experience in the whole thing...)
One for the future (we've been promised a test/dev/live enviroment "soon")
Thanks a bunch for your input, Greg
February 19, 2009 at 7:10 am
You're going to spend a lot less time implementing configurations than you will trying to decipher what's stored in msdb. Configurations are very easy to set up. Once you get on a role, you will probably only spend a few minutes per package.
Regarding having both environments on the same box...here's what I'd do:
Set up multiple versions of configuration files, one for dev, one for test, one for prod (if you have that many levels). Each one will contain the connection strings for that environment. Then when you execute the pacakges with SQL Agent or DTExec, you can specify which config file to use at run time with the /CONFIGFILE filename.
February 19, 2009 at 8:25 am
Thanks for your input Chris.
We were originally planning on setting up Sql Server configuration, rather than XML files (correct me if I'm wrong please, but as far as I can tell passwords would have to be stored in clear text in these files? We can't do this, hence looking into Sql server config)
Also, the connection managers could be called anything at all (even if pointing to the same database on the same server), so I'd still need to go into every package in VS to find out what they are called! And wouldn't one have to enable configurations on a per-package basis in order to use /CONFIGFILE from dtexec/SqlAgent?
Seems like it would be the same amout of manual work either way!
Sql Server config is a problem when your test and live environments are on the same box (details here in case anyone's interested: http://agilebi.com/cs/blogs/jwelch/archive/2007/12/03/fun-with-ssis-configurations.aspx), so we're "saving" that one for when we finally get this promised Dev/Test/Live environment
It turns out that there's only 15 packages that actually get called by any Sql Agent jobs, so not as bad as I thought - and I have just been informed that the task is not as urgent as was initially made out.
Thanks again
February 19, 2009 at 9:28 am
Well ... you can actually change things in maintenance plans if you know exactly what strings you need to replace ... I've attached a script that I used for some corrections in the past. The root cause was changing from dynamic to static port number on an instance. The maintenance plans did not want to play along.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 19, 2009 at 9:38 am
You're correct that if you store the connection strings in a SQL configuration table they won't be encrypted. If using Windows authentication is an option for your connections, you wouldn't need to include passwords at all. If you want to stick with a SQL table, you just need to lock down access to the table with the production connection strings to only your system accounts that are running the packages.
You will indeed need to enable configurations for each package but if the names of the connection managers can be made consistant across all of the packages, you can share the same config-file / table entries across all pacakges.
If you've got some time now, you might want to think about setting a standard for configuration manager names and package configurations for all packages going forward. Changing 15 packages to add configurations and update connection manager names won't be too bad...maybe a day or a couple days depending on how tough it will be to test them and get them migrated. When you have 50 or 100 (or more) packages down the road and you aren't handling configurations (and error logging for that matter) in a standard way, it will all of a sudden become a big headache to manage.
Good luck.
February 23, 2009 at 8:28 am
Apologies for the delayed response, I've had a long weekend due to having amassed a day's worth of hours in-lieu 🙂
Rudy,
Thanks for the suggestion, but I'd really rather not change the system table directly - I'm not enough of a risk-taker, and arranging downtime for a potential msdb restore would be a nightmare in its own right...
Chris,
Don't think for a second that I don't agree with you! I am just starting to look into setting up Sql Server configuration for those packages. I don't know why people gave me the idea that this change was urgent when, in fact, we have till October to change it, which, other projects permitting, should be ample time to come up with standards of some sort!
Thanks again for your input (and I apologise in advance for the flurry of configuration-related threads that will no doubt come from me due to this...)
February 24, 2009 at 2:10 pm
If you're just looking for a list of connection managers (like documentation on your package) have you tried looking at programs that document your ssis packages like ssisdocumenter (http://www.ssisdocumenter.com/). They're not expensive and are quite handy when you want to document your packages.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply