September 17, 2009 at 6:32 am
Hi - I have used pkg configs successfully at other employers (configs for each db and then an
environment variable for MACHINENAME to get server name). It all worked beautifully.
Now I am at a new employer and have a problem (I think):
I am using SSIS 2008, and am using SSIS config in SQL Server table (not xml).
Have set up the configs for each db for connection string property, no problem.
But...I have 1 server (SQLDEV is the name) that has 2 instances on it: SQLDEV and SQLQA.
These are VM btw. They set up 2 instances so I could test my deployment and so on and also
have users test the data on QA.
How can I set up a configuration for the server name AND instance so I dont have to
make copies of all my packages and re-set the connections for the dbs?
That's problem 1.
Problem 2 is:
I also have a situation for staging packages that currently live on the SQLDEV box (SQLDEV instance).
The source data is coming from the AS400 dev box whose library name is "LANSADVL"...so far so good.
BUT - when I want to deploy these pkgs to my prod box and pull source data off the PROD AS400 - its library
name is "MBM". So, how do I set up configs in this situation? It seems similar, yet different from
Problem # 1 above.
Any suggestions most helpful...don't want 5000 copies of each SSIS pkg 🙂
September 22, 2009 at 6:35 am
Anyone? I'm getting desperate.
I have looked all over the internet and can't find an answer.
Is any of this do-able? Or am I stuck copying all my SSIS pkgs and editing the connections?
Thanks!
September 22, 2009 at 2:45 pm
All of my SSIS configurations are done via XML file, not table, so I don't know if this will help at all, but Robert Davis did an article about XML named instance configuration:
The syntax of ServerName\InstanceName rather than ServerName alone may still apply, I'm not certain.
September 22, 2009 at 3:36 pm
#1 and #2 are a little different problem (I think)..
#1, You probably need to explicitly force the servername as opposed to using a file or a table. Because the machine has one name but the SQL Servers have two you can't tell which one is which unless you indicate it to the package somehow.. I have done very little with DB stored package configs. But that is how I see the problem..
#2, how is it any different than any other configured item? How is it special? Please elaborate, do you need to change a config string or a path or what?
CEWII
September 23, 2009 at 6:56 am
yes, it appears I will have to manually change that ini file for deployment to each instance
on the SQLDEV box.
However, I am not sure that will solve my problem....once they are deployed and I
want to run them (via sql agent) how will the packages know that the connections
should point to the instance? LIke, I have a connection called "MDS", in the setting I had
to specify the server and instance and db(SQLDEV box, SQLDEV instance, MDS db).
As I said in the 1st posting, in previous life I had an environment variable ON the servers
for the MACHINENAME and was able to reference this in my package configs so It knew
which box to use (and subsequently which db).
The problem #2 is similar, but different.
THe staging packages pull data off the AS400 source system.
On my SQLDEV box I want to pull from the DEV AS400 (whose library name is "LANSADVL", box
name is "DVL").....but for production I want to read from MBM box with library name "MBM".
I dont see how I can achieve this without copying the staging packages and opening
each one and modifying the connections.
September 23, 2009 at 10:57 am
tiffanyjanetblack (9/23/2009)
yes, it appears I will have to manually change that ini file for deployment to each instanceon the SQLDEV box.
However, I am not sure that will solve my problem....once they are deployed and I
want to run them (via sql agent) how will the packages know that the connections
should point to the instance? LIke, I have a connection called "MDS", in the setting I had
to specify the server and instance and db(SQLDEV box, SQLDEV instance, MDS db).
As I said in the 1st posting, in previous life I had an environment variable ON the servers
for the MACHINENAME and was able to reference this in my package configs so It knew
which box to use (and subsequently which db).
The problem #2 is similar, but different.
THe staging packages pull data off the AS400 source system.
On my SQLDEV box I want to pull from the DEV AS400 (whose library name is "LANSADVL", box
name is "DVL").....but for production I want to read from MBM box with library name "MBM".
I dont see how I can achieve this without copying the staging packages and opening
each one and modifying the connections.
For #1, you might add a variable that is filled in by the job indicating what instance. Then in the package you evaluate the variable and adjust the servername as needed. This would allow a great deal of flexibility as to default instances or a named instance. I do understand about MACHINENAME, which unfortunately is not much use when named instances are in play..
For #2. Are these all properties of the connection for AS400, I have to ask because I don't have any experience in that particular tech.. Or are they part of the connection string.. Either way, you could do it either with the config file, or a combination of the config file and a script task. If these are all properties of the connection then you can select them individually in the PAckage Config wizrd and adjust them in the file. If not then you can pass the values in with the config file and assembly them using a script task, then assign the connection string to the connection right there in the script task..
CEWII
September 23, 2009 at 11:50 am
Ah ha! I knew there had to be a way!
So have the SQL job set the varibale value for the pkgs to use and
simply have a script component read the variable and set the connection
string proprties for the connections. Sounds workable.
I'm going to give this a whirl.
And, I believe it will also solve the 2nd issue as well.
Thanks very much!
September 23, 2009 at 11:56 am
I know this can work.. Also, yo might set DelayValidation to True for the package so that it re-validates at run-time instead of now..
CEWII
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply