Retrieving DTS Information in Visual Basic
Data Transformation Services (DTS) provides features for requesting information about registered components and saved packages and for retrieving the contents of log records.
Registered Components
The Application object provides access to the system, package, and log data. You create it independently of a DTS package.
Use the OLEDBProviderInfos, ScriptingLanguageInfos, TaskInfos, and TransformationInfos collections of the Application object to obtain information about:
Example
The following example creates a DTS Application object, then iterates through the collections named above to retrieve information about the registered components available to DTS.
To register components
- In a Microsoft Visual Basic® development environment, create a new Standard EXE project.
- On the Project menu, click References, and then select the Microsoft DTSPackage Object Library check box.
- Place a textbox on Form1, and then accept the default name Text1.
- Set the MultiLine property to TRUE and set the ScrollBars property to 3 - Both.
- Copy the following code into the code window for Form1, and then run the project:
Note Be sure to include the Form_Resize sub. It allows you to drag the borders of Form1 to view the information.
Private Sub Form_Load()
Dim objDTSAppl As DTS.Application
Dim colScripInfo As DTS.ScriptingLanguageInfos
Dim objScripInfo As DTS.ScriptingLanguageInfo
Dim colOLEDBInfo As DTS.OLEDBProviderInfos
Dim objOLEDBInfo As DTS.OLEDBProviderInfo
Dim colTaskInfo As DTS.TaskInfos
Dim objTaskInfo As DTS.TaskInfo
Dim colTransInfo As DTS.TransformationInfos
Dim objTransInfo As DTS.TransformationInfo
Dim strMsg As String
Set objDTSAppl = New DTS.Application
strMsg = "OLEDB Provider Information" & vbCrLf & "=======================" & vbCrLf
Set colOLEDBInfo = objDTSAppl.OLEDBProviderInfos
For Each objOLEDBInfo In colOLEDBInfo
strMsg = strMsg & vbCrLf & _
vbTab & "ClassID:" & vbTab & objOLEDBInfo.ClassID & vbCrLf & _
vbTab & "Descr:" & vbTab & objOLEDBInfo.Description & vbCrLf & _
vbTab & "File:" & vbTab & objOLEDBInfo.ImplementationFileName & vbCrLf & _
vbTab & "Version:" & vbTab & objOLEDBInfo.ImplementationFileVersionString & vbCrLf & _
vbTab & "Name:" & vbTab & objOLEDBInfo.Name & vbCrLf & _
vbTab & "Parse:" & vbTab & objOLEDBInfo.ParseName & vbCrLf
Next
strMsg = strMsg & vbCrLf & "Scripting Langauge Information" & vbCrLf & _
"=========================" & vbCrLf
Set colScripInfo = objDTSAppl.ScriptingLanguageInfos
For Each objScripInfo In colScripInfo
strMsg = strMsg & vbCrLf & _
vbTab & "ClassID:" & vbTab & objScripInfo.ClassID & vbCrLf & _
vbTab & "Descr:" & vbTab & objScripInfo.Description & vbCrLf & _
vbTab & "File:" & vbTab & objScripInfo.ImplementationFileName & vbCrLf & _
vbTab & "Version:" & vbTab & objScripInfo.ImplementationFileVersionString & vbCrLf & _
vbTab & "Name:" & vbTab & objScripInfo.Name & vbCrLf
Next
strMsg = strMsg & vbCrLf & "Registered DTS Task Information" & vbCrLf & _
"===========================" & vbCrLf
Set colTaskInfo = objDTSAppl.TaskInfos
For Each objTaskInfo In colTaskInfo
strMsg = strMsg & vbCrLf & _
vbTab & "ClassID:" & vbTab & objTaskInfo.ClassID & vbCrLf & _
vbTab & "Descr:" & vbTab & objTaskInfo.Description & vbCrLf & _
vbTab & "File:" & vbTab & objTaskInfo.ImplementationFileName & vbCrLf & _
vbTab & "Version:" & vbTab & objTaskInfo.ImplementationFileVersionString & vbCrLf & _
vbTab & "Icon:" & vbTab & objTaskInfo.IconFile & vbCrLf & _
vbTab & "Index:" & vbTab & objTaskInfo.IconIndex & vbCrLf & _
vbTab & "Name:" & vbTab & objTaskInfo.Name & vbCrLf
Next
strMsg = strMsg & vbCrLf & "Registered DTS Transformation Information" & vbCrLf & _
"===================================" & vbCrLf
Set colTransInfo = objDTSAppl.TransformationInfos
For Each objTransInfo In colTransInfo
strMsg = strMsg & vbCrLf & _
vbTab & "ClassID:" & vbTab & objTransInfo.ClassID & vbCrLf & _
vbTab & "Descr:" & vbTab & objTransInfo.Description & vbCrLf & _
vbTab & "File:" & vbTab & objTransInfo.ImplementationFileName & vbCrLf & _
vbTab & "Version:" & vbTab & objTransInfo.ImplementationFileVersionString & vbCrLf & _
vbTab & "Name:" & vbTab & objTransInfo.Name & vbCrLf
Next
Text1.Text = strMsg
End Sub
Private Sub Form_Resize()
Text1.Move 0, 0, Me.ScaleWidth, Me.ScaleHeight
End Sub
Meta Data Services
Lineage information is saved for DTS packages saved to SQL Server 2000 Meta Data Services if the LineageOptions property of the package specifies that this be done.
Use the GetPackageRepository method of the Application object to return a PackageRepository object. The methods of the PackageRepository object provide access to the package and lineage information.
- Use the EnumPackageInfos method to return information about the DTS packages in Meta Data Services.
- Use the EnumPackageLineages method to return lineage data for a particular package version.
- Use the EnumStepLineages method to return step lineage data for a particular package lineage.
- Use RemovePackageLineages to purge the lineage data for a package version.
Example
The following example uses the GetPackageRepository method to access the Meta Data Services instance in the msdb database (the default instance) on the local server. Then it accesses and displays the package information and the lineage data for those packages.
Note This example displays all the lineage data in the Meta Data Services instance. On a production server, this may be an unmanageably large amount of data which will exceed the capacity of the textbox. Use a test computer that has a few packages stored in the repository that have been configured to write lineage data. Run each package only a few times since the lineage data was last purged.
To run this example, follow the procedure used to run the registered components and use the following code:
Private Sub Form_Load()
Dim objDTSAppl As DTS.Application
Dim objPkgRepositry As DTS.PackageRepository
Dim colPkgInfo As DTS.PackageInfos
Dim objPkgInfo As DTS.PackageInfo
Dim strMsg As String
Set objDTSAppl = New DTS.Application
Set objPkgRepositry = objDTSAppl.GetPackageRepository( _
"(local)", "msdb", "", "", DTSReposFlag_UseTrustedConnection)
Set colPkgInfo = objPkgRepositry.EnumPackageInfos("", False, "")
strMsg = "DTS Package Information" & vbCrLf & "======================" & vbCrLf
Set objPkgInfo = colPkgInfo.Next
Do Until colPkgInfo.EOF
With objPkgInfo
strMsg = strMsg & vbCrLf & _
"Name:" & vbTab & .Name & vbCrLf & _
"Descr:" & vbTab & .Description & vbCrLf & _
"Date:" & vbTab & .CreationDate & vbCrLf & _
"PkgID:" & vbTab & .PackageID & vbCrLf & _
"VerID:" & vbTab & .VersionID & vbCrLf & _
"Owner:" & vbTab & .Owner & vbCrLf & _
"Size:" & vbTab & .PackageDataSize & _
vbTab & "Type:" & vbTab & .PackageType & _
vbTab & "IsOwner:" & vbTab & .IsOwner & vbCrLf
strMsg = strMsg & strPackageLineages( _
.VersionID, objPkgRepositry)
End With
Set objPkgInfo = colPkgInfo.Next
Loop
Text1.Text = strMsg
End Sub
Private Function strPackageLineages(ByVal strPkgVerID As String, _
ByVal objPkgRepositry As DTS.PackageRepository) As String
Dim colPkgLineage As DTS.PackageLineages
Dim objPkgLineage As DTS.PackageLineage
Dim strMsg As String
Set colPkgLineage = objPkgRepositry.EnumPackageLineages(strPkgVerID, "", 0)
strMsg = vbCrLf & vbTab & "Package Lineage: " & strPkgVerID & vbCrLf & _
vbTab & "=================================================" & vbCrLf
Set objPkgLineage = colPkgLineage.Next
Do Until colPkgLineage.EOF
With objPkgLineage
strMsg = strMsg & vbCrLf & _
vbTab & "Name:" & vbTab & .Name & vbCrLf & _
vbTab & "Cmptr:" & vbTab & .Computer & vbCrLf & _
vbTab & "Date:" & vbTab & .ExecutionDate & vbCrLf & _
vbTab & "PkgID:" & vbTab & .PackageID & vbCrLf & _
vbTab & "VerID:" & vbTab & .VersionID & vbCrLf & _
vbTab & "Oprtr:" & vbTab & .Operator & vbCrLf & _
vbTab & "FullID:" & vbTab & .LineageFullID & vbCrLf & _
vbTab & "ShortID:" & vbTab & .LineageShortID & vbCrLf
strMsg = strMsg & strStepLineages( _
.LineageFullID, objPkgRepositry)
End With
Set objPkgLineage = colPkgLineage.Next
Loop
strPackageLineages = strMsg
End Function
Private Function strStepLineages(ByVal strFullID As String, _
ByVal objPkgRepositry As DTS.PackageRepository) As String
Dim colStepLineage As DTS.StepLineages
Dim objStepLineage As DTS.StepLineage
Dim strMsg As String
Set colStepLineage = objPkgRepositry.EnumStepLineages(strFullID)
strMsg = vbCrLf & vbTab & vbTab & "Step Lineage: " & strFullID & vbCrLf & _
vbTab & vbTab & "=============================================" & vbCrLf
Set objStepLineage = colStepLineage.Next
Do Until colStepLineage.EOF
With objStepLineage
strMsg = strMsg & vbCrLf & _
vbTab & vbTab & "Name:" & vbTab & .Name & vbCrLf & _
vbTab & vbTab & "Start:" & vbTab & .StartTime & vbCrLf & _
vbTab & vbTab & "Finish:" & vbTab & .FinishTime & vbCrLf & _
vbTab & vbTab & "Elapse:" & vbTab & .ExecutionTime & vbCrLf & _
vbTab & vbTab & "Result:" & vbTab & .StepExecutionResult & _
vbTab & "Status:" & vbTab & .StepExecutionStatus & vbCrLf
If .ErrorCode <> 0 Or Len(.ErrorDescription) > 0 Or Len(.ErrorSource) > 0 Then
strMsg = strMsg & _
vbTab & vbTab & "Code:" & vbTab & .ErrorCode & vbTab & "x" & Hex(.ErrorCode) & vbCrLf & _
vbTab & vbTab & "Descr:" & vbTab & .ErrorDescription & vbCrLf & _
vbTab & vbTab & "Source:" & vbTab & .ErrorSource & vbCrLf
End If
End With
Set objStepLineage = colStepLineage.Next
Loop
strStepLineages = strMsg
End Function
Private Sub Form_Resize()
Text1.Move 0, 0, Me.ScaleWidth, Me.ScaleHeight
End Sub
Logging to SQL Server
Logging to SQL Server is available to all DTS packages, regardless of where they are stored. Log records are written to the msdb database on the server specified by the package LogServerName property if the package LogToSQLServer property has been set.
Use the GetPackageSQLServer method, specifying server and login information, to return a PackageSQLServer object. The methods of that object provide access to the package and log data on the server.
- Use the EnumPackageInfos method to return information about the packages in SQL Server storage on that server.
- Use the EnumPackageLogRecords, EnumStepLogRecords and EnumTaskLogRecords methods to return log data of the indicated type on the server.
- Use the RemovePackageLogRecords, RemoveStepLogRecords, RemoveTaskLogRecords methods to purge log records of the indicated type. In addition, RemoveAllLogRecords removes all log data for all packages from the server.
Example
The following example uses the GetPackageSQLServer method to gain access to the stored packages and the log data on the local server. Then the example shows how the methods of the PackageSQLServer object access and display information about the packages in SQL Server storage and the package and step log data on that server.
Note This example displays all the log data on the local server. On a production server, this may be an unmanageably large amount of data which will exceed the capacity of the textbox. Use a test computer on which a few packages have been run only a few times each since the log data was last purged.
To run this example, follow the procedure used to run the registered components, and use the following code in step 5.
Private Sub Form_Load()
Dim objDTSAppl As DTS.Application
Dim objPkgSQLServer As DTS.PackageSQLServer
Dim colPkgInfo As DTS.PackageInfos
Dim objPkgInfo As DTS.PackageInfo
Dim strMsg As String
Set objDTSAppl = New DTS.Application
Set objPkgSQLServer = objDTSAppl.GetPackageSQLServer( _
"(local)", "sa", "", DTSSQLStgFlag_Default)
Set colPkgInfo = objPkgSQLServer.EnumPackageInfos("", False, "")
strMsg = "DTS Package in SQL Server Storage" & vbCrLf & _
"==============================" & vbCrLf
Set objPkgInfo = colPkgInfo.Next
Do Until colPkgInfo.EOF
With objPkgInfo
strMsg = strMsg & vbCrLf & _
vbTab & "Name:" & vbTab & .Name & vbCrLf & _
vbTab & "Descr:" & vbTab & .Description & vbCrLf & _
vbTab & "Date:" & vbTab & .CreationDate & vbCrLf & _
vbTab & "PkgID:" & vbTab & .PackageID & vbCrLf & _
vbTab & "VerID:" & vbTab & .VersionID & vbCrLf & _
vbTab & "Owner:" & vbTab & .Owner & vbCrLf & _
vbTab & "Size:" & vbTab & .PackageDataSize & _
vbTab & "Type:" & vbTab & .PackageType & _
vbTab & "IsOwner:" & vbTab & .IsOwner & vbCrLf
End With
Set objPkgInfo = colPkgInfo.Next
Loop
strMsg = strMsg & strPackageLogRecords( _
"", objPkgSQLServer)
strMsg = strMsg & strStepLogRecords( _
"", objPkgSQLServer)
Text1.Text = strMsg
End Sub
Private Function strPackageLogRecords(ByVal strPkgVerID As String, _
ByVal objPkgSQLServer As DTS.PackageSQLServer) As String
Dim colPkgLogRec As DTS.PackageLogRecords
Dim objPkgLogRec As DTS.PackageLogRecord
Dim strMsg As String
Set colPkgLogRec = objPkgSQLServer.EnumPackageLogRecords( _
"", False, "", strPkgVerID, "")
strMsg = vbCrLf & "DTS Package Log Records " & strPkgVerID & vbCrLf & _
"=======================" & vbCrLf
Set objPkgLogRec = colPkgLogRec.Next
Do Until colPkgLogRec.EOF
With objPkgLogRec
strMsg = strMsg & vbCrLf & _
vbTab & "Name:" & vbTab & .Name & vbCrLf & _
vbTab & "Descr:" & vbTab & .Description & vbCrLf & _
vbTab & "Cmptr:" & vbTab & .Computer & vbCrLf & _
vbTab & "Logged:" & vbTab & .LogDate & vbCrLf & _
vbTab & "Start:" & vbTab & .StartTime & vbCrLf & _
vbTab & "Finish:" & vbTab & .FinishTime & vbCrLf & _
vbTab & "Elapse:" & vbTab & .ExecutionTime & vbCrLf & _
vbTab & "PkgID:" & vbTab & .PackageID & vbCrLf & _
vbTab & "VerID:" & vbTab & .VersionID & vbCrLf & _
vbTab & "Oprtr:" & vbTab & .Operator & vbCrLf & _
vbTab & "FullID:" & vbTab & .LineageFullID & vbCrLf & _
vbTab & "ShortID:" & vbTab & .LineageShortID & vbTab & "x" & Hex(.LineageShortID) & vbCrLf
If .ErrorCode <> 0 Or Len(.ErrorDescription) > 0 Then
strMsg = strMsg & _
vbTab & vbTab & "ECode:" & vbTab & .ErrorCode & vbTab & "x" & Hex(.ErrorCode) & vbCrLf & _
vbTab & vbTab & "EDesc:" & vbTab & .ErrorDescription & vbCrLf
End If
End With
Set objPkgLogRec = colPkgLogRec.Next
Loop
strPackageLogRecords = strMsg
End Function
Private Function strStepLogRecords(ByVal strFullID As String, _
ByVal objPkgSQLServer As DTS.PackageSQLServer) As String
Dim colStepLogRec As DTS.StepLogRecords
Dim objStepLogRec As DTS.StepLogRecord
Dim strMsg As String
Set colStepLogRec = objPkgSQLServer.EnumStepLogRecords(strFullID, Null)
strMsg = vbCrLf & "DTS Step Log Records: " & strFullID & vbCrLf & _
"====================" & vbCrLf
Set objStepLogRec = colStepLogRec.Next
Do Until colStepLogRec.EOF
With objStepLogRec
strMsg = strMsg & vbCrLf & _
vbTab & "Name:" & vbTab & .Name & vbCrLf & _
vbTab & "Start:" & vbTab & .StartTime & vbCrLf & _
vbTab & "Finish:" & vbTab & .FinishTime & vbCrLf & _
vbTab & "Elapse:" & vbTab & .ExecutionTime & vbCrLf & _
vbTab & "FullID:" & vbTab & .LineageFullID & vbCrLf & _
vbTab & "ExecID:" & vbTab & .StepExecutionID & vbCrLf & _
vbTab & "ProgCt:" & vbTab & .ProgressCount & vbCrLf & _
vbTab & "Result:" & vbTab & .StepExecutionResult & _
vbTab & "Status:" & vbTab & .StepExecutionStatus & vbCrLf
If .ErrorCode <> 0 Or Len(.ErrorDescription) > 0 Then
strMsg = strMsg & _
vbTab & "Code:" & vbTab & .ErrorCode & vbTab & "x" & Hex(.ErrorCode) & vbCrLf & _
vbTab & "Descr:" & vbTab & .ErrorDescription & vbCrLf
End If
End With
Set objStepLogRec = colStepLogRec.Next
Loop
strStepLogRecords = strMsg
End Function
Private Sub Form_Resize()
Text1.Move 0, 0, Me.ScaleWidth, Me.ScaleHeight
End Sub
Packages Saved as Files
The DTS storage file can contain multiple packages, each with multiple versions. To determine programmatically what packages and versions a storage file contains, the Package2 object GetSavedPackageInfos method returns a reference to the SavedPackageInfos collection. The program can examine the details of each saved package version by iterating through this collection.
Example
The following code example shows the strShowDTSPkgComps function formatting a text string, with the information on each package version in the specified storage file:
Private Function strShowDTSPkgComps( _
ByVal strUNCPath As String) As String
Dim objPackage As DTS.Package2
Dim objPkgInfos As DTS.SavedPackageInfos
Dim objPkgInfo As DTS.SavedPackageInfo
Dim strPackageName As String
Dim strMsg As String
'Display the packages and versions in a DTS storage file.
On Error GoTo ErrorHandler
'Create the package object and get the package information collection.
Set objPackage = New DTS.Package
Set objPkgInfos = objPackage.GetSavedPackageInfos(strUNCPath)
'Iterate thru the package information collection.
strPackageName = ""
For Each objPkgInfo In objPkgInfos
'If this is a different package than the last one, format full information.
If strPackageName <> objPkgInfo.PackageName Then
strMsg = strMsg & vbCrLf & objPkgInfo.PackageName & vbCrLf & _
"PackageID: " & objPkgInfo.PackageID & vbCrLf & _
"Pkg Create Date: " & objPkgInfo.PackageCreationDate & vbCrLf
strPackageName = objPkgInfo.PackageName
End If
'Format version information.
strMsg = strMsg & vbCrLf & vbTab & "VersionID: " & _
objPkgInfo.VersionID & vbCrLf & vbTab & _
"Version Save Date: " & objPkgInfo.VersionSaveDate & _
vbCrLf & vbTab & "Encrypted: " & _
(objPkgInfo.IsVersionEncrypted) & vbCrLf
Next objPkgInfo
strShowDTSPkgComps = strMsg
Exit Function
ErrorHandler:
MsgBox "Error retrieving package information: 0x" & Hex$(Err.Number) & _
vbCrLf & Err.Description, vbExclamation
strShowDTSPkgComps = ""
Exit Function
End Function