SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Programmatically change PWD in all connections in all DTS packages


Programmatically change PWD in all connections in all DTS packages

Author
Message
bellis
bellis
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 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



Dmerm-128388
Dmerm-128388
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 40

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.


Larry Stein
Larry Stein
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 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
bellis
bellis
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 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!!!





Nicholas Cain
Nicholas Cain
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3090 Visits: 6200

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
Larry Stein
Larry Stein
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 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
noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9508 Visits: 2048
SWEET !!!


* Noel
philcart
philcart
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4080 Visits: 1436

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
Dmerm-128388
Dmerm-128388
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 40

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

....


bellis
bellis
SSC-Addicted
SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)SSC-Addicted (491 reputation)

Group: General Forum Members
Points: 491 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





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search