I am having trouble getting an indirect configuration to work. Can someone offer a hand?
Here's the deal....
I have a package the currently has an OLE DB Connection manager. I have an XML configuration file set up to acquire the connection string. That works fine. I have a package variable that contains the absolute path to the .dtsconfig file.
How can I get the connection manager to read the package variable (which has the path to the *.dtsconfig file) instead of the hard coded value?
Thanks in advance.
I don't think you can use a variable to set a configuration path. Indirect configurations refer to the use of environment variables to hold the path to the configuration.|||Perhap Indirect Configuration is not what I need then. What I am trying to do is set up my package such that the path to the dtsconfig file is dynamic. Ideally I'd like to not have to open up the package up in the designer and modify the path if I need to run the package on another machine (assuming the other machine directory structure is different).
How can I do this?
Thanks.
|||Use the /CONFIG switch on DTEXEC. That lets you override the configuration paths at runtime.|||Thanks for your suggestion. I got the indirection to work by specifying the path to the XML config file in the ENV variable instead of the package variable. My intention was to only have one ENV variable and it would only point to the root directory. All other directories, files, etc. I wanted to create on package startup relative to that root dir. Now I have more ENV variables to babysit. Not very pretty.
Looking at Kirk Haselden's book titled "Microsoft SQL Server 2005 - Integration Services", in chapter 14, page 303 he introduces 10 Configuration Type Identifiers when discussing the Package Configuration Object. One of which is of type IConfigFile which is described as an Indirect XML file configuration.
Can someone explain to me what this is for and how it may be used?
Thanks.
|||I think that is what you have implemented - an env variable that holds the path to the config file.|||Now I'm really confused. If I understand the table of Configuration Type Identifiers referenced in the book, ConfigurationType 2 is described as a ENVVARIABLE. Which is what I thought I implemented. See code below.
What I'm curious about is the ConfigurationType 5, ICONFIGFILE.
Code Snippet
<DTS:Configuration><DTS:Property DTS:Name="ConfigurationType">2</DTS:Property><DTS:Property DTS:Name="ConfigurationString">MIGRATOR_SOURCEDBCONFIG</DTS:Property><DTS:Property DTS:Name="ConfigurationVariable">\Package.Connections[SourceConn].Properties[ConnectionString]</DTS:Property><DTS:Property DTS:Name="ObjectName">SourceConnection</DTS:Property><DTS:Property DTS:Name="DTSID">{3B4BEC8B-04C1-44BA-809A-B734CAB0FD6C}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Configuration>
|||Configurations can come directly from an environment variable, or they can come indirectly from an environment variable. If you want to see examples of each, create a new package in BIDS, right-click in the designer form, and choose configurations. Create a few different configurations (using both direct and indirect setups) and take a look at the XML created in the package.
