Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Programmatically change PWD in all connections in all DTS packages Expand / Collapse
Author
Message
Posted Wednesday, February 25, 2004 2:25 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 8, 2012 12:53 PM
Points: 429, Visits: 6

I have a snippet of code (Active X Script Task) that I use WITHIN a DTS package to read in my password and then programmatically change the connection password to what I have set in my variable.

 

            ' Get reference to the ODS Connection

            Dim objConnection

            Set objPkg = DTSGlobalVariables.Parent

            Set objConnection = objPkg.Connections("Other (ODBC Data Source)")

            objConnection.Password = DTSGlobalVariables("myPwd").Value

            Set objConnection = Nothing

            Set objPkg = Nothing

 

Does anyone know if I can script out the following task?

I want to programmatically open each DTS in SQL Server, check to see if it has a connection to an external source, and if so, change the password of the connection object.  After changing the password of the connection object, I want to save the DTS package in SQL Server.

 

By scripting out this task, I can update the connection objects in all DTS packages without having to manually go into each and every one of the DTS packages and change the password in the connection objects.

 

Does anyone have any knowledge concerning such a script?

 

Thanks in advance!!!

 

Brian



Post #102397
Posted Wednesday, February 25, 2004 11:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 1:00 AM
Points: 12, Visits: 38

The easiest way to do that is ti save the DTS in VB code and see how you can manipulate the connection object through VB. More or less in the same way you can do it through an activeX script.

 

Post #102468
Posted Thursday, February 26, 2004 7:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 13, 2011 12:37 PM
Points: 125, Visits: 40

It depends upon what your goal is.

Because our development, QA and production environments differ greatly in configuration, we started putting a set of dynamic properties and ActiveX tasks at the start of every package.  The only thing the package has to know is the location of a parameters file.  From there, all server names, passwords, database names, directories, OLAP object names, etc are read from the file and automatically changed for the appropriate environment.  This allows all updates to be performed on reasonably secure text files without having to change the DTS package simply because a password changed.

 

Our method does not remove the dependency on needing a single piece of information - the ini file path and name.  If that changes on a global level, we would have the same issue as you would with password.

I can send or post an ActiveX code example that finds all dynamic properties tasks and points them to the correct values from the ini files.

 

Larry



Larry

Post #102586
Posted Thursday, February 26, 2004 7:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 8, 2012 12:53 PM
Points: 429, Visits: 6

So you're using an .ini file in a dynamic properties task to retrieve all of your information?  Is the .ini file saved on the server I'm guessing?

Yes -- if you could post (or send me) the code, I would greatly appreciate it. 

It sounds like we're doing something similar, I just chose to save the information in a table and query the table to get the values of interest.  I'd be interested in looking at your solution though.

Thanks!!!




Post #102597
Posted Thursday, February 26, 2004 8:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 9:52 AM
Points: 2,006, Visits: 6,078

Another way to do it, instead of leaving the data in an .ini file is to put it into the registry. You can then use regread functionality to pull the information from the registry, this way you can change the appropriate registry entry for each environment using a standard naming convention which will then change the connection string information on the fly depending on the server that the dts resides. The advantage is that this can also be done for text, excel, db files, in fact for pretty much anything.....

Example code below


'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Private Const DTS_KEY = "HKEY_LOCAL_MACHINE\SOFTWARE\DTSVariables\"

Function Main()
 Dim oConn, oWSH, oText
 Set oConn = DTSGlobalVariables.Parent.Connections("self")
 Set oWSH = CreateObject("WScript.Shell")
 oConn.DataSource = oWSH.RegRead(DTS_KEY & "SelfConnection")
 
 
 
 
'TEXT CONNECTION 
 
 Set oText = DTSGlobalVariables.Parent.Connections("random text file")

 
 oText.DataSource = oWSH.RegRead(DTS_Key & "TextConnection") & ("allnewtest.txt")

 

'BULK INSERT TASK

    'Get Package Object
    Set oPKG = DTSGlobalVariables.Parent
    ' Set new ProcessCommandLine value for "DTSTask_DTSBulkInsertTask_1"
    oPKG.Tasks("DTSTask_DTSBulkInsertTask_1").CustomTask.Properties._
      Item("DataFile").Value =oWSH.RegRead(DTS_KEY & "ServerName") & ("D$\testfiles\test.csv")

    ' Clear Up
    Set oPKG = Nothing

 

 
 Main = DTSTaskExecResult_Success
End Function

 





Shamless self promotion - read my blog http://sirsql.net
Post #102603
Posted Thursday, February 26, 2004 11:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 13, 2011 12:37 PM
Points: 125, Visits: 40

Depending upon the environment, our DB, OLAP, DTS, logging, and files used may be placed on a single box, all on separate boxes, or somewhere in between.  I wish I could say that was an intentional design choice.   We now have an ActiveX script as the first step in every package, immediately followed by any/all dynamic property tasks.  Using stacenic's registry trick, we could eliminate even the dependence upon the INI file name/location and be completely dynamic.

Here is the ActiveX code and a sample INI file.  Note we had to break the INI file into more sections than intended due to limitations on section length.

Hope this helps.

----------------------------------------------------

Function Main()

 'Set the INI file name in all the dynamic properties tasks to the value from the global variable.

Dim gvINIFileName, oAssignments, oAssignment

 gvINIFileName = DTSGlobalVariables("gvINIFileName").Value

 set oAssignments = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSDynamicPropertiesTask_1").CustomTask.Assignments

 'Set all dynamic tasks that are INI File Type (see below) to point to the same file.
  'DTSDynamicPropertiesSourceType_IniFile  0 Source is the value of a key within an .ini file.
  'DTSDynamicPropertiesSourceType_Query  1 Source is a value returned by an SQL query.
  'DTSDynamicPropertiesSourceType_GlobalVariable 2 Source is the value of a Data Transformation Services (DTS) global variable within the package.
  'DTSDynamicPropertiesSourceType_EnvironmentVariable 3 Source is the value of a system environment variable.
  'DTSDynamicPropertiesSourceType_Constant  4 Source is a constant.
  'DTSDynamicPropertiesSourceType_DataFile  5 Source is the contents of a data file.

 for each oAssignment in oAssignments
  if oAssignment.SourceType = 0 then oAssignment.SourceIniFileFileName = gvINIFileName
 next

 set oAssignment = Nothing
 set oAssignments = Nothing

 Main = DTSTaskExecResult_Success
End Function

----------------------------------------------------------------------------------------------

[Logging]
LoggingServer=LoggingServerName
LoggingUsername=UserName
LoggingPassword=MindUrOwnBeezwax

[Connections]
DBServer=DBServerName
DBUserName=UserName2
DBPassword=MindUrOwnBeezwax2

[WorkFiles]
FileDir=\\FileServerName\ShareName\MyDir
HoldFileDir=\\FileServerName\ShareName\MyDir\Hold
ProcessedFileDir=\\FileServerName\ShareName\MyDir\Processed

[GlobalVariables]
gvMaxAge=100
gvPreFileNameFormat=PreYYMMDD.xls
gvPostFileNameFormat=PostYYMMDD.xls

[Dims]

' TK stands for TreeKey, used in OLAP processing
OLAPServer=CubeServer
TKFiscal=CubeServer\Inventory\DimFolder\Time.Fiscal
TKCalendar=CubeServer\Inventory\DimFolder\Time.Calendar
TKPromo=CubeServer\Inventory\DimFolder\Promotion
TKProduct=CubeServer\Inventory\DimFolder\Product
TKMarket=CubeServer\Inventory\DimFolder\Market

[Cube]
OLAPServer=CubeServer
TKItem=CubeServer\Inventory\CubeFolder\Sales
TKInventory=CubeServer\Inventory\CubeFolder\Inventory
TKVirtual=CubeServer\Inventory\CubeFolder\InventorySales



Larry

Post #102704
Posted Thursday, February 26, 2004 1:37 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:06 AM
Points: 6,259, Visits: 2,030
SWEET !!!


* Noel
Post #102770
Posted Thursday, February 26, 2004 7:27 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 2,693, Visits: 1,210

I keep parameters like these in a table within my locked down DBA only database. Then in executing the package I pass in necessary global variables (server name, user id, password, etc..) to allow the package to connect to that table and extract the required parameters.

Makes it pretty simple to move from environment to environment without changing anything within the DTS package. It also has the secondary affect of being able to limit who has access to the parameters and audit changes to them.

 



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #102855
Posted Friday, February 27, 2004 12:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 1:00 AM
Points: 12, Visits: 38

I agry with you. Having global DTS variables is the best solution. Reading the registry sometimes might fail because of inadequate security permissions. Reading from ini files might have the same problem. Either way exposing unencrypted server's credentials is not something we want. From the other hand storing the needed credentials into a database table means that first you have to conenct to the server and then to the database and then retrieve the needed credentials.

The best way i can think of is to use the DTSRUN utility (dtsrunui.exe) and set the global variables of the DTS packages which change programatically the connections and credentials within the package, then use the encrypt button located at the advanced section, then get the encrypted string and paste it in a job. Afterwards you can schedule the job as you like. In this way NO credentials are visible to ANYONE.

Hope this helps

Dimitris

....

Post #102884
Posted Friday, February 27, 2004 7:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, February 8, 2012 12:53 PM
Points: 429, Visits: 6

This is exactly what I have done!!!

I have my information in a table on the server and then I query the table to retrieve the connection password and save this to a global variable.  I then set the password of the connection object to what is in the global variable via ActiveX Script.

So essentially, I'll need to just add this piece of code to all of the DTS packages.  After that is accomplished, all I'll have to do is set the password (which changes every 60 days) in one place and all of the DTS packages will automatically be updated.

Are there other things that anyone can suggest that this type of solution be applied to???  If I've got to update each DTS, I might as well do it right the first time.

Thanks,

Brian




Post #102998
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse