Configuration Approach

Background

I use four database connections strings.

I have about 30 packages that will use one or more of the connection stings.

I store the connection strings in XML configuration files.

I know that I can share configuration files across packages. Should I ?

Have one configuration file with all 4 connection strings? If I use this approach will I get errors in the packages that only use 1 of the connection strings OR

Create four separate configuration files (CnnString1.dtsconfig, CnnString2.dtsconfig, CnnString3.dtsconfig, CnnString4.dtsconfig) and use the appropriate ones for each package

Take another approach

Check this thread, It discusses the similar kind of issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1853477&SiteID=1

Thanks

|||For me, I just create all of the connection managers that are in the config file across all packages for a given project. It's a pain to setup, but it works for me.|||

That is the pain I am striving to avoid by using a modular approach. I know that having a configuration file for each package works but it is not reusable. That can cause a maintenance nightmare when moving 30+ packages between Dev, QA & Prod environments.

I have a requirement to use XML Configuration files. Returning to the original question, should I use 1 file with all database connections or should I use 4 files with a connection in each?

|||

Danny Crowell wrote:

That is the pain I am striving to avoid by using a modular approach. I know that having a configuration file for each package works but it is not reusable. That can cause a maintenance nightmare when moving 30+ packages between Dev, QA & Prod environments.

I have a requirement to use XML Configuration files. Returning to the original question, should I use 1 file with all database connections or should I use 4 files with a connection in each?

If it's such a pain, then use a configuration file for each connection manager object. Then each package only uses what it needs.

Concurrency?

How do you handle this today?
I am mapping out a new system and was interested in a fresh approach.
__StephenThat's a big topic. In SQL Server 2005, concurrency has been enhanced with
snapshot isolation, as well as database snapshots. With older versions, you
can use dirty reads, optimistic locking and READPAST.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:esteMwm%23FHA.924@.TK2MSFTNGP12.phx.gbl...
> How do you handle this today?
> I am mapping out a new system and was interested in a fresh approach.
> __Stephen
>|||Is there a specific problem you're having? Why aren't SQL Server's built-in
facilities for handling concurrency good for your situation?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:esteMwm%23FHA.924@.TK2MSFTNGP12.phx.gbl...
> How do you handle this today?
> I am mapping out a new system and was interested in a fresh approach.
> __Stephen
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uSTN9Bn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> Is there a specific problem you're having? Why aren't SQL Server's
> built-in facilities for handling concurrency good for your situation?
>
What I have is the statement created in my biz layer and presented back to
the db with an "and" stipulation.
update
MyTable
set myChangedColumn = NewValue , ..
where Pkey = ThisKey
AND myChangedColumn = OldValue , ..
Thus I'm only changing what the user has changed, and it won't whack someone
elses update who also changed the same value.
Is there a better way to do this?
My GUI is ASP or ASP.NET and mostly ASP today :(
__Stephen|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OvmQ4Bn%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
> snapshot isolation, as well as database snapshots. With older versions,
> you can use dirty reads, optimistic locking and READPAST.
Do you mean, "pessimistic" locking?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Nope, optimistic. Optimistic locking allows you to release the lock right
after you read it. Then, when you go to update it, if the timestamps
(rowversions) don't match, then you can retrieve the current version. That
provides more concurrency than when you keep the row locked until the user
has finished their update.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23sN69Kn%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OvmQ4Bn%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
>> snapshot isolation, as well as database snapshots. With older versions,
>> you can use dirty reads, optimistic locking and READPAST.
> Do you mean, "pessimistic" locking?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>|||"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:u2re6Jn%23FHA.1028@.TK2MSFTNGP11.phx.gbl...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uSTN9Bn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
>> Is there a specific problem you're having? Why aren't SQL Server's
>> built-in facilities for handling concurrency good for your situation?
> What I have is the statement created in my biz layer and presented back to
> the db with an "and" stipulation.
> update
> MyTable
> set myChangedColumn = NewValue , ..
> where Pkey = ThisKey
> AND myChangedColumn = OldValue , ..
> Thus I'm only changing what the user has changed, and it won't whack
> someone elses update who also changed the same value.
> Is there a better way to do this?
>
You can add a timestamp column to the table to simply the stipulation, but
basically you've got it right. It's not generally practical to use
pessimistic concurrency from clients like ASP.NET, so client-generated
optimistic concurrency is the norm.
David|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23eeN1Nn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
> Nope, optimistic. Optimistic locking allows you to release the lock right
> after you read it. Then, when you go to update it, if the timestamps
> (rowversions) don't match, then you can retrieve the current version.
> That provides more concurrency than when you keep the row locked until the
> user has finished their update.
Right, but the important point is, SQL Server 2000's isolation levels are
all pessimistic. Applications can implement an optimistic scheme -- but
this is the application doing so, not SQL Server.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Right. I was assuming he was asking for ideas on how to minimize
concurrency problems.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O$6XNao%23FHA.3296@.TK2MSFTNGP10.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23eeN1Nn%23FHA.2520@.TK2MSFTNGP15.phx.gbl...
>> Nope, optimistic. Optimistic locking allows you to release the lock
>> right after you read it. Then, when you go to update it, if the
>> timestamps (rowversions) don't match, then you can retrieve the current
>> version. That provides more concurrency than when you keep the row locked
>> until the user has finished their update.
> Right, but the important point is, SQL Server 2000's isolation levels are
> all pessimistic. Applications can implement an optimistic scheme -- but
> this is the application doing so, not SQL Server.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>