Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS - package config Expand / Collapse
Author
Message
Posted Thursday, September 17, 2009 6:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:06 PM
Points: 329, Visits: 331
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 :)
Post #789589
Posted Tuesday, September 22, 2009 6:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:06 PM
Points: 329, Visits: 331
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!
Post #791707
Posted Tuesday, September 22, 2009 2:45 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:37 AM
Points: 248, Visits: 1,732
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:

http://www.sqlservercentral.com/blogs/robert_davis/archive/2008/12/11/How-Do-I-Configure-SSIS-to-Work-With-a-Named-Instance.aspx

The syntax of ServerName\InstanceName rather than ServerName alone may still apply, I'm not certain.
Post #792195
Posted Tuesday, September 22, 2009 3:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
#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
Post #792233
Posted Wednesday, September 23, 2009 6:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:06 PM
Points: 329, Visits: 331
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.
Post #792588
Posted Wednesday, September 23, 2009 10:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
tiffanyjanetblack (9/23/2009)
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.

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
Post #792807
Posted Wednesday, September 23, 2009 11:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:06 PM
Points: 329, Visits: 331
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!
Post #792842
Posted Wednesday, September 23, 2009 11:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
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
Post #792845
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse