Indirect Configuration of connection manager

Greetings All;
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.

Indexes and keys are not synced w/ snapshot publication

Hello,
I am having trouble getting the indexes and primary keys copied to the
subscriber in a snapshot publication. I created a snapshot publication and
added an article to it using the wizard. Then I used the SQL Management
Studio to configure the properties of the article and set the following
options to true.
Copy primary key
Copy clustered index
Copy nonclustered indexes
Copy check constraints
Copy foreign keys
I generated the SQL script and got the code listed below to re-create the
article. When I use this script to create the article, the settings mentioned
above are "true" as expected. However, when the snapshot is applied to the
subscriber, none of the keys or indexes are transferred eventough everything
suceeds.
Does anyone know what else I can do to make this work? Did I not configure
it correctly?
Here is the code to recreate the article.
exec sp_addarticle
@.publication = @.PUBLICATON_NAME,
@.article = @.TABLE_NAME,
@.source_owner = N'dbo',
@.source_object = @.TABLE_NAME,
@.type = N'logbased',
@.description = null,
@.creation_script = null,
@.pre_creation_cmd = N'drop',
@.schema_option = 0x00000000080350DD,
@.identityrangemanagementoption = N'none',
@.destination_table = @.DESTINATION_TABLE_NAME,
@.destination_owner = N'dbo',
@.vertical_partition = N'false'
P.S - FYI, the destination table name is different than the source table
name as per our biz requirements. Could this be why?
Thanks!
Johnny
UPDATE: I just did some additional testing and found that if the destination
table stays the same as the source, the PKs and the indexes are copied to the
subscriber as they should be. It seems like this problem only occurs when the
destination table is different.
Does anyone know if and how I can work around this or force it to include
them even though the destination table is different?
Johnny
"Johnny" wrote:

> Hello,
> I am having trouble getting the indexes and primary keys copied to the
> subscriber in a snapshot publication. I created a snapshot publication and
> added an article to it using the wizard. Then I used the SQL Management
> Studio to configure the properties of the article and set the following
> options to true.
> Copy primary key
> Copy clustered index
> Copy nonclustered indexes
> Copy check constraints
> Copy foreign keys
>
> I generated the SQL script and got the code listed below to re-create the
> article. When I use this script to create the article, the settings mentioned
> above are "true" as expected. However, when the snapshot is applied to the
> subscriber, none of the keys or indexes are transferred eventough everything
> suceeds.
> Does anyone know what else I can do to make this work? Did I not configure
> it correctly?
>
> Here is the code to recreate the article.
> ----
> exec sp_addarticle
> @.publication = @.PUBLICATON_NAME,
> @.article = @.TABLE_NAME,
> @.source_owner = N'dbo',
> @.source_object = @.TABLE_NAME,
> @.type = N'logbased',
> @.description = null,
> @.creation_script = null,
> @.pre_creation_cmd = N'drop',
> @.schema_option = 0x00000000080350DD,
> @.identityrangemanagementoption = N'none',
> @.destination_table = @.DESTINATION_TABLE_NAME,
> @.destination_owner = N'dbo',
> @.vertical_partition = N'false'
>
> P.S - FYI, the destination table name is different than the source table
> name as per our biz requirements. Could this be why?
> Thanks!
> Johnny
|||Hi Johnny,
The indexes, constraints should be copied to the subscriber even though the
destination object name is different than source object name. I suspect that
you probably still have the "old" table with the source table name and the
same constraints at the subscriber. What happens in this case is that since
constraint names have to be unique across all tables, the distribution agent
simply cannot create a constraint on the "new" table with the same name as
one on the "old" table.
-Raymond
"Johnny" wrote:
[vbcol=seagreen]
> UPDATE: I just did some additional testing and found that if the destination
> table stays the same as the source, the PKs and the indexes are copied to the
> subscriber as they should be. It seems like this problem only occurs when the
> destination table is different.
> Does anyone know if and how I can work around this or force it to include
> them even though the destination table is different?
> Johnny
>
> "Johnny" wrote:
|||Wow!!!!! You hit it right on the money. That was the problem - I had the old
table with the same index names. After dropping this table, I was able to
successfully distribute the snapshot with a different destination name and
all indexes, primary keys, etc. were successfully transferred.
Thanks Raymond.
- Johnny
"Raymond Mak [MSFT]" wrote:
[vbcol=seagreen]
> Hi Johnny,
> The indexes, constraints should be copied to the subscriber even though the
> destination object name is different than source object name. I suspect that
> you probably still have the "old" table with the source table name and the
> same constraints at the subscriber. What happens in this case is that since
> constraint names have to be unique across all tables, the distribution agent
> simply cannot create a constraint on the "new" table with the same name as
> one on the "old" table.
> -Raymond
> "Johnny" wrote: