Hi All

I've written a package that gets all packages on a server, then runs through each of the packages, then enumerates the connections in each package, and attempts to translate certain properties to facilitate moving packages between servers

my code seems to happliy replace server names, user ids and catalogs, but fails dismally to do anything with the password property. in fact, when I break the code in the activex script debugger, all the password properties seem to be blank? is this property 'non viewable' once it has been set, or am I overlooking something blindingly obvious? any help from the DTS uber-Gurus would be greatly appreciated, here's a portion of the offending code

Thanks in advance


Function Main(

dim DTSApp, pkgSQL, pkgInfos, inf

Dim pkg, con, tsk, lkp, dpta, dto
Dim fso, fld, fi

Dim txtPkgNam

Dim EnvironmentI
Dim PackageI
Dim ConnectionI
Dim TaskI

'--

'Get values for global

gvDataSource=DTSGlobalVariables("DataSource").valu
gvCatalog=DTSGlobalVariables("Catalog").valu
gvUserID=DTSGlobalVariables("UserID").valu
gvPassword=DTSGlobalVariables("Password").valu

'--

CreateADOConnectio

'--

set DTSApp = CreateObject("DTS.Application"

'set pkgSQL = DTSApp.GetPackageSQLServer(gvDataSource,gvUserID,g vPassword, DTSSQLStgFlag_UseTrustedConnection
set pkgSQL = DTSApp.GetPackageSQLServer(gvDataSource,gvUserID,g vPassword,DTSSQLStgFlag_Default

set pkgInfos = pkgSQL.EnumPackageInfos("", True, ""

set info = pkgInfos.Nex

do until pkgInfos.EO

'got the package nam
txtPkgName = info.nam

'Get each package in tur
set pkg = CreateObject("DTS.Package"
' pkg.LoadFromSQLServer gvDataSource, gvUserID,gvPassword,DTSSQLStgFlag_UseTrustedConnec tion,"","","", txtPkgNam
pkg.LoadFromSQLServer gvDataSource, gvUserID,gvPassword,DTSSQLStgFlag_Default,"","","" , txtPkgNam

'we've got a reference to each package on the server
'msgbox pkg.nam

'--

For Each con in pkg.Connection

'replace the values in connections !

sto

''con.ConnectionProperties("Persist Security Info") = GetTranslation(con.ConnectionProperties("Persist Security Info")
'con.ConnectionProperties("User ID") = GetTranslation(con.ConnectionProperties("User ID")
'con.ConnectionProperties("Initial Catalog") = GetTranslation(con.ConnectionProperties("Initial Catalog")
'con.ConnectionProperties("Data Source") = GetTranslation(con.ConnectionProperties("Data Source")
''con.ConnectionProperties("Application Name") = GetTranslation(con.ConnectionProperties("Applicati on Name")


con.Name = GetTranslation(con.Name)
con.Description = GetTranslation(con.Description)

con.DataSource = GetTranslation(con.DataSource)
con.UserID = GetTranslation(con.UserID)
con.Password = GetTranslation(con.Password)

You cannot grab the password

http://www.microsoft.com/technet/tre...n/MS00-041.asp

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"James McMorrough" <james.mcmorrough (AT) sqlpersonnel (DOT) com> wrote

package, and attempts to translate certain properties to facilitate moving
packages between servers.
break the code in the activex script debugger, all the password properties
seem to be blank? is this property 'non viewable' once it has been set, or
am I overlooking something blindingly obvious? any help from the DTS
uber-Gurus would be greatly appreciated, here's a portion of the offending
code.
fault)