Export (0) Print
Expand All

Retrieving DTS Information in Visual Basic

SQL Server 2000

Retrieving DTS Information in Visual Basic

  New Information - SQL Server 2000 SP3.

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

  1. In a Microsoft Visual Basic® development environment, create a new Standard EXE project.

  2. On the Project menu, click References, and then select the Microsoft DTSPackage Object Library check box.

  3. Place a textbox on Form1, and then accept the default name Text1.

  4. Set the MultiLine property to TRUE and set the ScrollBars property to 3 - Both.

  5. 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
    
Show:
© 2015 Microsoft