******************************************************************************** * Method : Dtsmodify.Prg() * Author : Kurt Huebner * Copyright : (c) 2003 Blue Diamond Growers * Date : 8/7/2003 3:01:54 PM * Parameter(s) : <none> * Modified : 1. mm/dd/yyyy - * Notes : This procedure is used to modify all of the DTS packages. It * : changes the source database from test to production (or vice versa) * : Two variables need to be set when running this program: * : lcFilter = Which DTS Pkgs to change * : lcDBName = Name of the AS/400 database to change the select * : statement to. ******************************************************************************** LOCAL loDtspkg AS 'DTS.Package2',; loConn AS 'ADODB.Connection',; loRecordSet AS 'ADODB.RecordSet',; loCommand AS 'ADODB.Command',; lcDtsName AS String,; lcFilter AS String,; lcDBName AS String,; lcOldDbName AS String #INCLUDE C:\GIS7\LIBS\adovfp.h CLEAR RETURN * Set the filter for which DTS packages to change lcFilter = 'Import%' lcDBName = 'GISSPRDDTA' * Determine the old DB name to search for in the DTS package lcOldDbName = IIF(lcDbName = 'GISSPRDDTA','GISSSYSTST','GISSPRDDTA') * Read the names of all DTS packages loConn = CREATEOBJECT('ADODB.Connection') loConn.Open('Provider=SQLOLEDB.1;Password="XXXX";User ID=XXXXX;Initial Catalog=bdggis;Data Source=BDGGIS') loRecordSet = CREATEOBJECT('ADODB.RecordSet') loRecordSet.CursorType = ADOPENDYNAMIC loRecordSet.LockType = ADLOCKOPTIMISTIC loRecordSet.ActiveConnection = loConn loRecordSet.CursorLocation = ADUSECLIENT loCommand = CREATEOBJECT('ADODB.Command') loCommand.ActiveConnection = loConn loCommand.CommandText = 'SELECT DISTINCT t0.name FROM msdb.dbo.sysdtspackages t0 WHERE t0.name LIKE ?' loCommand.Parameters(0).Value = lcFilter loRecordSet.Open(loCommand) loRecordSet.MoveFirst() DO WHILE NOT loRecordSet.EOF() * Get the name of the DTS package lcDtsName = loRecordSet.Fields('name').Value loDtsPkg = CREATEOBJECT('DTS.Package2') loDtsPkg.LoadFromSQLServer('BDGGIS','dtspkg','dtspkg',,,,,lcDtsName) FOR EACH loTask IN loDtsPkg.Tasks FOR EACH loProperty IN loTask.Properties IF loProperty.Name = 'SourceSQLStatement' lcSourceSqlStatement = loProperty.Value lcNewSqlStatement = STRTRAN(lcSourceSqlStatement,lcOldDbName, lcDBName) IF lcSourceSqlStatement <> lcNewSqlStatement loProperty.Value = lcNewSqlStatement loDtsPkg.SaveToSQLServer('BDGGIS','dtspkg','dtspkg') ? 'Updated '+lcDtsName EXIT ENDIF ENDIF ENDFOR ENDFOR loRecordSet.MoveNext() ENDDOHope this helps,