August 14, 2013 at 3:59 pm
Do I:
1. Create a configuration named cfg_AdventureWorkTest and put all my variables in that configuration e.g. OLE_SRC_Test, FF_DEST_Test and then create another configuration cfg_AdventureWorkProd and add all the same variables and then somehow run the package with a variable that chooses on of the two configs
OR
2. Create on cfg_AdventureWorks and put both Prod and test variables and then pick the variables I need based on how I call the package.
Right now I also have the wrench thrown in this mess that is using a Master package to call the child and every time I make a change to a variable it throws an error "the result of expression "@User::pkgConn_Admissions" on property connection is not found. I am guessing it is because I renamed the variable but even so the expression they are showing me is the renamed variable with the correct name.
Thanks,Phil
August 15, 2013 at 11:53 am
It sounds like the location of your package is not changing whether you want to run it for prod or test, is this true?
What we usually do is have 1 config file(same name) in both locations. Let's call that file cfg_Example. The cfg_Example in the test location will have test values, and the cfg_Example in the prod location will have prod values.
August 15, 2013 at 12:56 pm
Khades (8/15/2013)
What we usually do is have 1 config file(same name) in both locations. Let's call that file cfg_Example. The cfg_Example in the test location will have test values, and the cfg_Example in the prod location will have prod values.
When you say config files you mean a *.dtsconfig, correct. You don't use SQL to store your configs? I was going the SQL route since SQL 2012 seems to be going that route. And I want to know how I go about saving Prod and Test configs in SQL. And the switching between the two.
Now I am thinking I should create two separate configs in SQL e.g. TestAdvWorks and ProdAdvWorks and then see if on my call to dtexec can pass a variable to switch between the two configs.
August 15, 2013 at 3:28 pm
Oh ok. I do usually use SQL Server Configurations. I've seen it done 2 different ways.
One way is to make sure the configuration file is in the same path in every environment.
Something like:
C:\SSIS\Cfg_Example.dtsConfig
This configuration points to the server and DB where your SQL Configurations are. So when you move your package to the Prod location, it will use the .dtsConfig file to connect to the prod Configuration DB, and when it's in the test location, it will use the .dtsConfig file to point to the test configuration DB.
Another way to do this is by using Environment variables to point the different server/DBs, just keep in mind that each server can only have 1 environment variable.
August 15, 2013 at 11:05 pm
Khades (8/15/2013)
Oh ok. I do usually use SQL Server Configurations. I've seen it done 2 different ways.One way is to make sure the configuration file is in the same path in every environment.
Something like:
C:\SSIS\Cfg_Example.dtsConfig
This configuration points to the server and DB where your SQL Configurations are. So when you move your package to the Prod location, it will use the .dtsConfig file to connect to the prod Configuration DB, and when it's in the test location, it will use the .dtsConfig file to point to the test configuration DB.
Another way to do this is by using Environment variables to point the different server/DBs, just keep in mind that each server can only have 1 environment variable.
I thought I had it but my package does not seem to read the xml configuration to be able to switch databases
I created a master package with an xml config that stores the database connection "Server\SSIS_TEST" And then on SERVER I created a SSIS_TEST and a SSIS_PROD database. Once my package was complete I added the second config to the master and saved all my settings in to SSIS_TEST. And it works, I can change the directory a file is written to via manually editing the record in SQL. But what isn't working is when I change the XML file to point to SERVER\SSIS_PROD the package still writes to the test location.
I tried setting the connection to the database directly from the config and also tried loading a variable OLE_SRC_SSIS and then setting the connection expression to the variable. no change.
So why do you think my package won't read the SSIS Config database connection from the XML file, it is in the top of the list.
Thanks,
Phil
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply