Saving DTS Packages in Visual Basic
When you use the Package2 object methods, you can save or load a package in the formats available to you through Data Transformation Services (DTS) Designer and the DTS Import/Export Wizard. You can save packages to Microsoft® SQL Server™ 2000, to SQL Server 2000 Meta Data Services and to a COM-structured storage file.
To save a package, use one of the following Package2 methods:
- SaveToSQLServer
- SaveToSQLServerAs
- SaveToRepository
- SaveToRepositoryAs
- SaveToStorageFile
- SaveToStorageFileAs
If the package is run before being saved, call the UnInitialize method first.
To load a package, first create the Package2 object and then invoke one of the following Package2 object methods:
- LoadFromSQLServer
- LoadFromRepository
- LoadFromStorageFile
To delete a package from SQL Server or Meta Data Services, use the Package2 object RemoveFromSQLServer or RemoveFromRepository methods.
Example
The following code example shows a function loading a package in one format and saving it in another:
Enum eDTSPkgFormat
REPOSITORY
SQL_SERVER
STORAGE_FILE
End Enum
Public Function blnCopyDTSPackage( _
ByVal strReposServerName As String, ByVal strReposDBName As String, _
ByVal strReposUserName As String, ByVal strReposPassword As String, _
ByVal blnReposNTAuth As Boolean, ByVal strSQLServerName As String, _
ByVal strSQLSvUserName As String, ByVal strSQLSvPassword As String, _
ByVal blnSQLSvNTAuth As Boolean, ByVal strPackageID As String, _
ByVal strPackageVerID As String, ByVal strPackageName As String, _
ByVal strPkgOwnerPwd As String, ByVal strPkgUserPwd As String, _
ByVal strPkgUNCPath As String, ByVal dpfPkgSource As eDTSPkgFormat, _
ByVal dpfPkgDestination As eDTSPkgFormat) As Boolean
'Copy the DTS package source to the destination format.
Dim objPackage As DTS.Package2
Dim rsfFlags As DTS.DTSRepositoryStorageFlags
Dim ssfFlags As DTS.DTSSQLServerStorageFlags
Dim strPhase As String 'load/save phase for error msg
On Error GoTo ErrorHandler
'Copying the source to the destination in the same format is not supported.
If dpfPkgSource = dpfPkgDestination Then
MsgBox "Same format for source and destination not supported", _
vbExclamation
Exit Function
End If
'Create the package object and calculate the storage flags.
Set objPackage = New DTS.Package
rsfFlags = IIf(blnReposNTAuth, DTSReposFlag_UseTrustedConnection, _
DTSReposFlag_Default)
ssfFlags = IIf(blnSQLSvNTAuth, DTSSQLStgFlag_UseTrustedConnection, _
DTSSQLStgFlag_Default)
'Load the package from the specified storage type.
strPhase = "loading"
Select Case dpfPkgSource
Case REPOSITORY
objPackage.LoadFromRepository _
strReposServerName, strReposDBName, strReposUserName, _
strReposPassword, strPackageID, strPackageVerID, _
strPackageName, rsfFlags
Case SQL_SERVER
objPackage.LoadFromSQLServer _
strSQLServerName, strSQLSvUserName, strSQLSvPassword, _
ssfFlags, strPkgOwnerPwd, strPackageID, _
strPackageVerID, strPackageName
Case STORAGE_FILE
objPackage.LoadFromStorageFile _
strPkgUNCPath, strPkgOwnerPwd, strPackageID, _
strPackageVerID, strPackageName
End Select
'Save the package to the specified storage type.
strPhase = "saving"
Select Case dpfPkgDestination
Case REPOSITORY
objPackage.SaveToRepository _
strReposServerName, strReposDBName, strReposUserName, _
strReposPassword, rsfFlags
Case SQL_SERVER
objPackage.SaveToSQLServer _
strSQLServerName, strSQLSvUserName, strSQLSvPassword, _
ssfFlags, strPkgOwnerPwd, strPkgUserPwd
Case STORAGE_FILE
objPackage.SaveToStorageFile _
strPkgUNCPath, strPkgOwnerPwd, strPkgUserPwd
End Select
blnCopyDTSPackage = True
Exit Function
ErrorHandler:
MsgBox "Error " & strPhase & " DTS package: 0x" & Hex(Err.Number) & _
vbCrLf & Err.Description, vbExclamation
Exit Function
End Function