Programmatically change PWD in all connections in all DTS packages
-
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
-
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.
-
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
-
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!!!
-
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
-
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
-
SWEET !!!
* Noel -
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.
--------------------
Colt 45 - the original point and click interface
-
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
....
-
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply