DTS Packages

[DBAWinnipeg]

Hello everyone!!!

Here's a doooooooosey question. Just starting in a new environment and one of the first tasks on the hit list is to change the "sa" password.

Now they've been good at most of the setup
SQL SERVER runs under a different account
SQL SERVER AGENT runs under a different account
LINKED SERVERS are setup under a different account

Now the DTS Packages are owned by a different account but the SQL Server connections INSIDE the package are using sa. There are HUNDREDS of these packages... so here's the question

Q: Does anyone have a script that will go to all DTS packages and change SQL Server Connection user/pwd?



Thanks in advance!!!

Colin in da 'Peg

 

[aalmeida]

Never seen anything like that before.
May be it would be a good time to review all these DTS pacs and have the inside connections using the same of the outside connection.

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[DBAWinnipeg]

Well if I could start back at square 1 then I would of done all of it differently but I'm coming into a new company with hundreds of DTS packages where the SQL Server connections within the DTS packages are using sa.

:S

So other then scripting out all the DTS packages and doing a search and replace I think I'm outta luck.



Thanks in advance!!!

Colin in da 'Peg

 

[aalmeida]

It seems that the only thing left is to wish you Good luck.

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[DBAWinnipeg]

OK with that being said.

Does anyone have a script that will export all DTS packages to .bas files?

I know you can manually do it by opening the package and choosing "save as" but for hundreds of DTS packages hehehe not the most desireable solution




Thanks in advance!!!

Colin in da 'Peg

 

[DrAardvark]

I know it is not helpful but that is why we do Windows logon authority. I never allow an 'out of the box' username to stick around for long. Too many security issues.

 

[aalmeida]

I'm with u DrAardvark.

DBAWinnipeg, as for your lest question I guess it is possible but you will have more chances to find an answer opening a new thread for it.

Sorry again

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[iBe]

DBAWinnipeg, this is a kludge and a long-shot, but within enterprise manager perhaps you could use a record-a-macro app like macromaker to record the simple action of changing the account settings on a DTS package and moving on to the next package, then set the macro to replay for a number equal to the amount of packages to fix.

Quick fix when you don't have time or don't want to code...

It's free...

http://members.ij.net/anthonymathews/MacroMaker.htm