Change Pwd inside DTS Package

Question

Change Pwd inside DTS Package

Shas3

Hall of Fame

Points: 3671
More actions
April 26, 2006 at 9:44 am

#169239

Hi all, i have a SQL standard ID used inside couple of DTS Packages and i need to change the password for this ID once every month. Now my question is there any way that you can update the password inside the DTS Package automatically when you change the password with out having to open each and every package ?
Shas3

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I don't think so. You could use UDL files for your connections then you'd only have to change the password in the file without opening the packages at all. You'd have to edit the packages initially to specify the UDL files, but that's all.

Greg

philcart SSC-Forever Points: 47795 More actions · Answer 2

Be aware that if you use UDL files to store the username and password they will be in plain text and available for anyone that has read access to the file

--------------------
Colt 45 - the original point and click interface

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

This can be accomplished through COM. If you look at the object model for "DTS.Package2", you will find that you can programmatically open, modify, and save packages.

You should be able to do it with VB or C#. It might even be possible to use T-SQL and the OA_xxxx sprocs.

The solution is a bit too complicated to post here, but If you are familiar with COM programming, then google "DTS.Package2" and you'll get plenty of sample code that will get you going in the right direction.

hth jg

Jeff Gray SSChampion Points: 10667 More actions · Answer 4

I got bored, so I'm showing a C# example. This changes the password for the first connection object in a DTS package called DumbTestPackage.

static void Main(string[] args)

{

DTS.Package2Class package = new DTS.Package2Class();

Object MIA=System.Reflection.Missing.Value;

package._Package_LoadFromSQLServer("CLSQLOPS\\SQLOPS","sa","",DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"","","","DumbTestPackage",ref MIA);

package.Connections.Item(1).Password = "MyNewConnectionPassword";

package._Package_SaveToSQLServer("CLSQLOPS\\SQLOPS","sa","",DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"","","",ref MIA,false);

}