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 - Dynamic XML Configuration File Path while running the Package Expand / Collapse
Author
Message
Posted Friday, August 1, 2014 11:17 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:12 PM
Points: 22, Visits: 119
Hi,

I have done with the SSIS Package and it's working as expected in my local machine, I have one problem is that how I can give my XML configuration file path as input to my SSIS package dynamically during runtime, because in local I’m having in C:\Extract\Config.dtsconfig. But in higher environment the path will be different. Is there any way to set this configuration file path?

When I tried with .\Config.dtsconfig in Package Configuration Wizard on the cursor focusing out it's giving the pull path again.

Kindly Guide me.


Known Is A Drop,UnKnown Is An Ocean....
Njoy Programming
:)
Post #1598775
Posted Friday, August 1, 2014 11:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 2,200, Visits: 5,939
windows_mss (8/1/2014)
Hi,

I have done with the SSIS Package and it's working as expected in my local machine, I have one problem is that how I can give my XML configuration file path as input to my SSIS package dynamically during runtime, because in local I’m having in C:\Extract\Config.dtsconfig. But in higher environment the path will be different. Is there any way to set this configuration file path?

When I tried with .\Config.dtsconfig in Package Configuration Wizard on the cursor focusing out it's giving the pull path again.

Kindly Guide me.


First a quick question, which version are you on?

The config is initialized before the execution so no luck there. That means that unless you dynamically create/modify the package, it cannot be done. A workaround would be storing the config in a database and use the likes of sys variables to choose which config to use.
Post #1598786
Posted Friday, August 1, 2014 12:23 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:12 PM
Points: 22, Visits: 119
I'm using VS 2008 BIDS.

Known Is A Drop,UnKnown Is An Ocean....
Njoy Programming
:)
Post #1598791
Posted Friday, August 1, 2014 12:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 2,200, Visits: 5,939
windows_mss (8/1/2014)
I'm using VS 2008 BIDS.


No option to do dynamic file config from within the package then, either dynamically modified/created package or database configuration are your best bets. If you need any help with these, just ping back (and ask for Koen).
Post #1598793
Posted Friday, August 1, 2014 4:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:26 PM
Points: 20,732, Visits: 32,496
At a previous employer we established a fixed directory on the c: drive of our development and production Data Warehouse servers. This fixed directory contained an XML configuration file that was used by our SSIS packages to point to where the actual configuration files resided on each server since this was different between development and production. All our packages looked in this directory to determine where to look for the actual configuration files.

You could also use a system variable to contain the same information regarding the location of configuration files. We went with the XML file as it was easier to maintain and allowed us to expand locations depending on information in the package as well. IIRC when using a system variable you can only point to one location. You would have to verify that to be sure.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1598832
Posted Friday, August 1, 2014 5:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 4:43 AM
Points: 5,401, Visits: 7,512
Lynn Pettis (8/1/2014)
You could also use a system variable to contain the same information regarding the location of configuration files. We went with the XML file as it was easier to maintain and allowed us to expand locations depending on information in the package as well. IIRC when using a system variable you can only point to one location. You would have to verify that to be sure.


This is correct. We use that in our environments as we use a dedicated SSIS Server with the configuration table on that. You can, however, have multiple system variables setup and have the configuarions link differently. I don't use XML configs however, but I know I've setup multiple data sources for the configurations via 3 different environmental variables, then had the rest of the configs read from different sources based on which data source they inherit from.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1598835
Posted Saturday, August 2, 2014 6:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:26 PM
Points: 20,732, Visits: 32,496
Evil Kraig F (8/1/2014)
Lynn Pettis (8/1/2014)
You could also use a system variable to contain the same information regarding the location of configuration files. We went with the XML file as it was easier to maintain and allowed us to expand locations depending on information in the package as well. IIRC when using a system variable you can only point to one location. You would have to verify that to be sure.


This is correct. We use that in our environments as we use a dedicated SSIS Server with the configuration table on that. You can, however, have multiple system variables setup and have the configuarions link differently. I don't use XML configs however, but I know I've setup multiple data sources for the configurations via 3 different environmental variables, then had the rest of the configs read from different sources based on which data source they inherit from.


The only XML file we had was the one on the C: Drive (actually in its own directory on the C: drive). All the other configuration information was stored in a dedicated SQL database on each server. One thing we hadn't implemented was encryption for the passwords. Most of the connections in the configs, though, were trusted configs. Not sure if that was any better, but the only people who knew the passwords were the DBAs. Network servers had no access the any of the SQL Server instances on the servers we supported. Granted, they could change a password or add themselves to the DBA group in AD and gain access, but I wasn't go to tell them that.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1598924
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse