Indirect configurations ROCK!

Hi Jamie,

You wrote In your blog which I pasted below:

"I have built my packages in such a way that different packages can use the same configuration file. For example I have a "Master" configuration file that stores information that all of my packages will need (e.g. connection strings for my log files, warehouse database and metadata database). Our project makes use of many source systems so I also have a configuration file for each source system as well - that way every package that accesses a certain source system can use the appropriate configuration file. .."

How do you create a configuration for each source? I thought the concept of environmental variable is to have one configuration file in a location and defined the path to it in your system environmental variable value?

I have only one config file file for all my 9 packages and I have been having problems.

Maybe I am doing something wrong but what? Can you be more specific about this great idirect configuration?

Thanks

Omon

Putting multiple values in one configuration file is fine if you always have all targets in all packages to which you apply the configuration file to. This means if you have 5 connection strings in a configuration file, then all packages for which you use that file, must have all 5 connections. Very often this is not the case. Normally you would have x connections, but of that maybe one or two are used in every package, the rest are only used in one or two packages. So I suggest you have one configuration file for the one or two common connections, and then one file per additional connection. This gives flexibility for you to pick and choose the configuration files you require per package.

You then need one environment variable per configuration file. That is what Jamie and I use.

|||

Interesting. This might solve my nightmare. I am exploring this senerio now because I have 6 connections in total but all mt packages are using 2 or 3 connections each. I don't have all my 6 connections in all my packages. I am going to add the rest connections to my packages and see what happened.

Thanks

Omon.

Indexing non-unique data

I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.

The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).

Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.|||I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.

The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).

Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?
I think custured index should do the trick,its my opinion...I feel clustered index are best for low selectiviy columns,i.e. the column which have many duplicates values. But see what the gurus suggest...|||You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.
But can't we make the foreign key column clustered index? I mean making the unique key not a clustered index...only a unique key column|||Hi Istaks

Welcome to the forum

Not a guru but some musings:

Well - a clustered index determines the physical order storage of data. So - it is useful if placed on an incrementing field as far as insertion of data is concerned as there will be no page splits based on insertion. It is also useful if you are likely to use >, < or between comparisons in a where condition on the clustered field.

The former is not the case. Inequality operators are rarely used on identities so Id go with no too :)|||So on table(B) create a clustered index on the unique key and a non-clustered index on the foreign key?

All selections from this table will be based on the foreign key.|||If ALL selects on this table will reference the foreign key and you will not be searching for individual records, then you would get a performance boost from using a clustered index on your foreign key.