March 5, 2002 at 11:04 am
I have about 200 DTS packages which are developed in the test sever. What's the most effiecitnt way to move these DTS packages to production? Are there any issues while moving DTS packages between SQL7.0 and SQL2000?
March 5, 2002 at 1:37 pm
Normally the easiest and fastest way in my opinion is to open the DTS package and do save as, there you can even specify another server to save to. Once done then go to the other server and make any adjustments needed for whatever reason.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 5, 2002 at 3:39 pm
March 5, 2002 at 3:51 pm
Gotta say thats ugly. Who wants to open 200 packages? Maybe a small app or proc that will do this for you. Other options, can you bcp the data, or does it end up with pointers to wrong server if you do?
Andy
March 5, 2002 at 4:02 pm
I guess DMO would work. Haven't tried it.
Could write something in VBScript to load each package and then resave it.
Steve Jones
March 5, 2002 at 4:24 pm
Hey Andy, I would appreciate that code too if you'll write it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 5, 2002 at 4:49 pm
I'll give it a shot later tonight.
Andy
March 5, 2002 at 8:41 pm
I have created and regularly use the package which basically consists of one DDQ Task - as source I just use EXCEL file (you can make it anything you like) I preferred it this way as I can write back to it as well as easily manipulate data in it outside the SQL Server. As a destination I use a log table.
The script bacically opens each package using LoadFromSQLServer method
and then depending on the different parameters ( either from the source - the Excel file - or GV in the package itself) I reset different properties of the package and it's steps and then save it somewhere else ( with same or different name) using objPackage.SaveToSQLServerAs
I'll do it regularly when I need to move ( rename) many packages in one go.
My script is quite long as it deals with a lot of specific details I need to change in each package and all its tasks but if you just need to move the packages it should be pretty straightforward - your code should go through each record in the source - it should contain at least the name of the package - open it and then save it somewhere else.
If you want I can send you my code stripped off the unnecessary details.
Cheere
Tom
March 6, 2002 at 6:13 am
Tom, I'd be interested to see your code, never hurts to see more than one solution. Written but untested (sorry, had removed the named instances I use for testing and have not reinstalled yet) this will only work with packages saved to SQL, not to the file system...so far anyway!
Sub MoveDTSPackages(SourceServer As String, DestinationServer As String)
'3/6/02 Andy Warren
'Code to copy DTS packages using the "save as" functionality built into DTS
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim oPackage As DTS.Package
On Error GoTo Handler
If SourceServer = "" Or DestinationServer = "" Then
MsgBox "Must provide both source and destination server names."
Exit Sub
ElseIf UCase$(SourceServer) = UCase$(DestinationServer) Then
MsgBox "Source and destination server names cannot be the same."
Exit Sub
End If
'establish a trusted connection to source server
Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MSDB;Data Source=" & SourceServer
'iterate through all sql packages, open and then save to the destination server
Set rs = cn.Execute("select * from msdb..sysdtspackages")
Do Until rs.EOF
Set oPackage = New DTS.Package
With oPackage
Debug.Print "Processing " & oPackage.Name
DoEvents
.LoadFromSQLServer SourceServer
.SaveToSQLServer DestinationServer
End With
rs.MoveNext
Loop
'clean up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Exit Sub
Handler:
MsgBox Err.Description
Resume Next
End Sub
Andy
March 6, 2002 at 6:32 pm
OK
Here is my code (tested) adapted to the need of saving somehere else:
'**********************************************************************
' Visual Basic ActiveX Script
' MovePackages.vbs 07 Mar 2002 12:04 AEST
'************************************************************************
' I only use LoadFromSQLServer method as so far I only worked
' with the packages saved as local packages
'
' The only problem I have when the package is saved this way it does not
' preserve the layout and I loose any text annotations I may have.
' Otherwise it works fine.
' I edited my long version to do loading and saving functionality here
' Tested - works OK.
'************************************************************************
CONST DTSSQLStgFlag_Default = 0
CONST DTSSQLStgFlag_UseTrustedConnection = 256
Function Main()
Dim objPackage ' As DTS.Package2
Dim strPackageName
Dim strNewPackageName ' can be same as original
Dim pUnkPersistStgOfHost
Dim pVarPersistStgOfHost
strPath2DTSFiles = DTSGlobalVariables("gvPath2DTSFiles").Value
strLogin = RTRIM(DTSSource("Login"))
strPassword= RTRIM(DTSSource("Password"))
IF DTSSource("Process") = "Yes" THEN
blnProcessRecord = TRUE
ELSE
blnProcessRecord = FALSE
END IF
IF blnProcessRecord THEN
if DTSSource("Save") = "Yes" THEN
' this I use (in my more elaborate version when I do automatic changes
' and then save them back to the same location as a local package)
' here it would just save another version of the package
blnSave = TRUE
ELSE
blnSave = False
END IF
if DTSSource("ToStorageFile") = "Yes" THEN
' this one I use so I can save them in the Visual Source Safe
blnToStorageFile = TRUE
ELSE
blnToStorageFile = False
END IF
strNewPackageName = Rtrim(DTSSource("SaveAs"))
strPackageName= RTRIM(DTSSource("PackageName"))
intDTSSQLStgFlag = CInt(DTSSource("Authentication")) ' 0 by user/password 256 - Windows
strServerName = DTSGlobalVariables("gvServer" ).Value
strServerUserName = DTSGlobalVariables("gvServerUserName" ).Value
strServerPassword = DTSGlobalVariables("gvServerPassword" ).Value
strNewServerName = DTSGlobalVariables("gvNewServer" ).Value
strNewServerUserName = DTSGlobalVariables("gvNewServerUserName" ).Value
strNewServerPassword= DTSGlobalVariables("gvNewServerPassword" ).Value
IF vartype(intDTSSQLStgFlag) <> vbInteger THEN
intDTSSQLStgFlag = DTSSQLStgFlag_Default
ELSEIF intDTSSQLStgFlag <> DTSSQLStgFlag_Default and intDTSSQLStgFlag <> DTSSQLStgFlag_UseTrustedConnection THEN
intDTSSQLStgFlag = DTSSQLStgFlag_Default
END IF
strPackagePassword = NULL
strPackageGuid = NULL
strPackageVersionGuid= NULL
pVarPersistStgOfHost= NULL
Set objPackage = CreateObject("DTS.Package2")
IF intDTSSQLStgFlag = DTSSQLStgFlag_UseTrustedConnection THEN
objPackage.LoadFromSQLServer strServerName, , , intDTSSQLStgFlag,,,, strPackageName, pUnkPersistStgOfHost
ELSE
objPackage.LoadFromSQLServer strServerName, strServerUserName, strServerPassword, intDTSSQLStgFlag,,,, strPackageName, pUnkPersistStgOfHost
END IF
IF blnToStorageFile THEN
' if the dts file already exists it will add another package to the file
blnReusePasswords = TRUE
strUNCFile = strPath2DTSFiles & RTRIM(DTSSource("PackageName")) & ".DTS"
objPackage.SaveToStorageFile strUNCFile, , , pUnkPersistStgOfHost, blnReusePasswords
END IF
IF blnSave = TRUE THEN
IF intDTSSQLStgFlag = DTSSQLStgFlag_UseTrustedConnection THEN
objPackage.SaveToSQLServer strServerName, , , intDTSSQLStgFlag
ELSE
objPackage.SaveToSQLServer strServerName, strServerUserName, strServerPassword, intDTSSQLStgFlag
END IF
END IF
IF (NOT blnSave) AND (NOT blnToStorageFile) THEN
IF intDTSSQLStgFlag = DTSSQLStgFlag_UseTrustedConnection THEN
objPackage.SaveToSQLServerAs strNewPackageName, strNewServerName, , , intDTSSQLStgFlag
ELSE
objPackage.SaveToSQLServerAs strNewPackageName, strNewServerName, strNewServerUserName, strNewServerPassword, intDTSSQLStgFlag
END IF
END IF
varArray = DTSLookups("ResetProcess").Execute( _
Now, _
DTSSource("PackageName"), _
DTSSource("Owner") _
)
'this look up updates my excel source file with date and changes the Process column to `Processed`
'UPDATE [UNCNameOfMyExcelSoucreFile.xls].[WorkSheet$]
'SET Process = 'Processed', DSDateTime = ?
'WHERE (PackageName = ?) AND (Owner = ?)
Set objPackage = Nothing
END IF
Main = DTSTransformstat_SkipRow
End Function
Cheers
Tom
March 6, 2002 at 7:21 pm
Yeah, new toys...I am gonna have to play with these.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply