Retrieving DTS Information in Visual Basic

DTS Programming

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:

OLE DB providers.
Microsoft® ActiveX® scripting languages.
DTS task classes and DTS transformation classes that are registered on the computer and can be used by DTS.
The DTS task and transformation classes include those supplied with Microsoft SQL Server™ and custom tasks and transformations implemented by other vendors and users.

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

previous page start next page