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.

Indexing etiquette

I'm using Idera's SQL Diagnostic Manager and its showing me my index is
using the File Group "Primary" (which I recently read is bad) and I
have 3 index levels with some Data values having in excess of 700K
rows.
Is this bad and should I be worried? Is there some housekeeping I
should do in these situations?
TIA
Robrcamarda (rcamarda@.cablespeed.com) writes:
> I'm using Idera's SQL Diagnostic Manager and its showing me my index is
> using the File Group "Primary" (which I recently read is bad)

There are situations where you can split up databases on several
file groups, and for instance have non-clustered index on a separate
volume. Note that if you relocate the clustered index, you relocate
the data as well.

But this should only be done if you have a clear understand of what you
win. None of our customer's databases have more than the two files
each database is born with. (And thus only one file group.)

> and I have 3 index levels with some Data values having in excess of 700K
> rows.
> Is this bad and should I be worried? Is there some housekeeping I
> should do in these situations?

It's a good idea to run DBCC DBREINDEX on your tables, if they tend
to fragment. Whether they fragment, can be concluded by using
DBCC SHOWCONTIG.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In books online there is an example which checks for fragmentation
above a level of 30% and runs the reindex function. Is this a good
number ot use or is it one of those "depends" kind of things?|||pb648174 (google@.webpaul.net) writes:
> In books online there is an example which checks for fragmentation
> above a level of 30% and runs the reindex function. Is this a good
> number ot use or is it one of those "depends" kind of things?

It's not a bad number. We ship our maintenance job that uses the output
from DBCC SHOWCONTIG, and if a table is fragmented enough, we run DBCC
DBREINDEX. And the bar where we reindex is, as far as I recall, precisely
30%...

What we have adding recently, and me and our admin-kind-of-guy has not
really arrived on the best strategy for, is to run UPDATE STASTISTICS
WITH FULLSCAN on table we don't reindex. Table that don't get defragmented
despite heavy insertion traffic, probably has a monotonic clustered
index, so statistics will be inaccurate after a while.

Then as always there are cases where you may want to deviate. For instance,
clustered index an guids is often said to be recipe for quick fragmentation.
However, SQL Server MVP Greg Linwood pointed out to me, that this can
be used to your advantage. You define the index with a relatively low
fill factor, say 50%. What will happen now is that insertion will happen
all over the place, but page splits will be rare, since all pages have
room to spare. So with design, framgmenation actually decreases as time
goes. Up to a certain point that is, once you are starting to fill up
more and more pages, page split will rage here and there. The idea is
that you monitor the state of the database closely, and that you have a
maintenance window where you again can reindex to 50%.

It goes without saying that this strategy is nothing for the left-hand
DBA, but requires thorough understanding and most of all, daily
monitoring of the state of the database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Do you have an example of that maintenance job somewhere? We don't look
at the database daily and have many installations so it needs to be
scripted and run on its own without any supervision or intervention.

I'm right handed and not a DBA...

Indexes in Enterprise Manager under "Table Info" tab

Does anyone knows what kind of indexes are the ones showing up under certain
tables in the "Table Info" tab? The indexes in question start with "_WA".
Also, I would like to know when they get created? and when are they being
used?It is my understanding that _wa objects are system created statistics.
--
Thomas
"Sal Young" wrote:
> Does anyone knows what kind of indexes are the ones showing up under certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?|||Sal,
They're auto statistics entries. Take a look at the space usage columns and
you'll see the value 0.
HTH
Jerry
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:AB3E0B0C-8CD8-4257-AEF8-FE25C63122F2@.microsoft.com...
> Does anyone knows what kind of indexes are the ones showing up under
> certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?

Indexes in Enterprise Manager under "Table Info" tab

Does anyone knows what kind of indexes are the ones showing up under certain
tables in the "Table Info" tab? The indexes in question start with "_WA".
Also, I would like to know when they get created? and when are they being
used?It is my understanding that _wa objects are system created statistics.
--
Thomas
"Sal Young" wrote:

> Does anyone knows what kind of indexes are the ones showing up under certa
in
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?|||Sal,
They're auto statistics entries. Take a look at the space usage columns and
you'll see the value 0.
HTH
Jerry
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:AB3E0B0C-8CD8-4257-AEF8-FE25C63122F2@.microsoft.com...
> Does anyone knows what kind of indexes are the ones showing up under
> certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?

Indexes in Enterprise Manager under "Table Info" tab

Does anyone knows what kind of indexes are the ones showing up under certain
tables in the "Table Info" tab? The indexes in question start with "_WA".
Also, I would like to know when they get created? and when are they being
used?
It is my understanding that _wa objects are system created statistics.
Thomas
"Sal Young" wrote:

> Does anyone knows what kind of indexes are the ones showing up under certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?
|||Sal,
They're auto statistics entries. Take a look at the space usage columns and
you'll see the value 0.
HTH
Jerry
"Sal Young" <SalYoung@.discussions.microsoft.com> wrote in message
news:AB3E0B0C-8CD8-4257-AEF8-FE25C63122F2@.microsoft.com...
> Does anyone knows what kind of indexes are the ones showing up under
> certain
> tables in the "Table Info" tab? The indexes in question start with "_WA".
> Also, I would like to know when they get created? and when are they being
> used?