June 29, 2005 at 5:44 am
Having migrated Our SQL dbs on NT to another server on win2k3.
The SQL instance names are different bother servers so it necessary to edit all the local packages under DTS (200 of em)on the new server and replace all references pointing to the old server instance name by the new name.
I know the easiest solution is to call the same server instance names but we are dictated by new naming standards convention.
Is there such a tool or best guidance. Thanks in advance.
June 29, 2005 at 9:50 am
I'm afraid there is not.
However, to stop this happening in the future you need to point your connections to a configuration file. See this article:
http://www.databasejournal.com/features/mssql/article.php/3073161
The one flaw with this article is that the path of the ini file is set to the c: drive. Therefore, running it on the server is OK, but running it from a client looks at the client c: drive. Hence you need to store the information in two places.
I am currently finishing off a solution that will get around this problem and will post back when I have documented in full.
June 30, 2005 at 9:59 am
The above solution is the best for the long term, but if you have hundreds of pkgs to quickly edit try the following VBScript task. It will need to be edited for pkgs with passwords as none of mine have that. It will change all SQL db connections and pkg log locations to the server you specify according to g.v. SQLDbConnectionServer.
You'll need to create/initialize the following global var's before running this: DTSPkgDestinationServer (the name of the SQL server which contains the DTS pkgs to edit), SQLDbConnectionServer (the db server name to change all SQL db connections to), DTSPkgNamePattern (% for all pkgs), and ShowPkgsCopiedProgress (Y if you want to see progress with MsgBox display of pkgs edited while the pkg is running).
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Option Explicit
Function Main()
' Initialize variables.
Dim cn
Dim ActiveConnection
Dim ConnectionLoop
Dim TaskLoop
Dim PkgRec
Dim provStr
Dim SelectStr
Dim DTSPkgDestinationServer
Dim SQLDbConnectionServer
Dim objPkg
Dim objExecPkgTask
Dim PkgName
Dim DTSPkgNamePattern
Dim PkgGUID
Dim PkgVersionID
Dim Q
Dim pVarPersistStgOfHost
Dim ShowPkgsCopiedProgress
Q = "'"
pVarPersistStgOfHost = 256
DTSPkgDestinationServer=DtsGlobalVariables.Item("DTSPkgDestinationServer").Value
SQLDbConnectionServer = DtsGlobalVariables.Item("SQLDbConnectionServer").Value
DTSPkgNamePattern = DtsGlobalVariables.Item("DTSPkgNamePattern").Value & "%"
ShowPkgsCopiedProgress = DtsGlobalVariables.Item("ShowPkgsCopiedProgress").Value
Set cn = CreateObject("ADODB.Connection")
' Specify the OLE DB provider.
cn.Provider = "sqloledb"
' Specify connection string on Open method.
provStr = "Server=" & DTSPkgDestinationServer & ";Database=MSDB;Trusted_Connection=yes"
cn.Open provStr
set PkgRec = CreateObject("ADODB.Recordset")
With PkgRec
Set .ActiveConnection = cn
'.Open "select DISTINCT name,id FROM sysdtspackages where name LIKE " & "'" & DTSPkgNamePattern & "'"
.Open _
"select T1.* from " & _
" dbo.sysdtspackages AS T1 " & _
" INNER JOIN " & _
" (SELECT name, id, MAX(createdate) AS createdate " & _
" from dbo.sysdtspackages " & _
" WHERE name LIKE " & Q & _
DTSPkgNamePattern & Q & _
" GROUP BY name, id ) AS T2 " & _
" ON T1.id=T2.id and T1.createdate=T2.createdate " & _
" ORDER BY T1.name"
Set ActiveConnection = Nothing
End With
While (Not PkgRec.EOF)
PkgName = PkgRec.Fields("name").Value
PkgGUID = PkgRec.Fields("id").Value
PkgVersionID = PkgRec.Fields("Versionid").Value
Set objPkg = CreateObject("DTS.Package")
'objPkg.LoadFromSQLServer "","","","256",,PkgGUID,PkgVersionID, PkgName
objPkg.LoadFromSQLServer DTSPkgDestinationServer,"","","256",,"","", PkgName,-1
If ShowPkgsCopiedProgress="Y" Then
MsgBox "Pkg name is: " & PkgName
End If
' Change pkg log location to the DTS pkg destination.
objPkg.LogServerName = DTSPkgDestinationServer
' Change data source property of all SQLOLEDB connections to the sql db connection server.
For Each ConnectionLoop in objPkg.Connections
If UCASE(ConnectionLoop.ProviderId)="SQLOLEDB" Then
ConnectionLoop.DataSource = SQLDbConnectionServer
End If
Next
objPkg.UnInitialize()
Set ObjPkg = Nothing
PkgRec.MoveNext
Wend
PkgRec.Close
Set PkgRec = Nothing
Set ObjPkg = Nothing
cn.Close
Set cn = Nothing
Main = DTSTaskExecResult_Success
End Function
Jeff B.
June 30, 2005 at 10:16 am
Many thanks Jeff. I will give it a try and let you know. Best Regards
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply