none
Find Unmapped Destination Columns Programmatically

    Dotaz

  • I'm creating a 2005 package programmatically that moves data between an OLEDB Source and Destination.  It works fine, except, when the destination tables contains more columns than the source.  In the OLE DB Destination Editor, the additional destination column can be skipped by setting the Input Column to Ignore.  How can I accomplish this in my script task?

    I've tried searching for unmapped output columns by comparing the items in the OutputColumnCollection to those in the InputColumnCollection.  However, I'm only seeing two columns in the output collection: ErrorCode and ErrorColumn.

    The actual error event logged by the instantiated package is:

    The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "ErrorTypeID" (43)" needs to be removed from the external metadata column collection.

    Here's an excerp of the code block I've been playing with.

            ' Reinitialize the metadata.
            DestinationInstance.AcquireConnections(vbNull)
            DestinationInstance.ReinitializeMetaData()
    
            ' Get the destination's default input and virtual input.
            Dim input As IDTSInput90 = compDestination.InputCollection(0)
            Dim output As IDTSOutput90 = compDestination.OutputCollection(0)
            Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput
    
            ' Iterate through the virtual input column collection.
            For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
                ' Call the SetUsageType method of the destination
                '  to add each available virtual input column as an input column.
                DestinationInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
            Next
    
            ' Map input columns to external columns
            For Each vColumn As IDTSInputColumn90 In input.InputColumnCollection
                DestinationInstance.MapInputColumn(input.ID, vColumn.ID, input.ExternalMetadataColumnCollection(vColumn.Name).ID)
            Next
    
            For Each vcolumn As IDTSOutputColumn90 In output.OutputColumnCollection
                'If input.InputColumnCollection.GetInputColumnByName(output.OutputColumnCollection.Item(0).Name _
                ', vcolumn.Name) Is Nothing Then
                '    output.OutputColumnCollection.RemoveObjectByID(vcolumn.ID)
                'End If
                'For Each icolumn As IDTSInputCollection90 In input.InputColumnCollection
                '    If input.InputColumnCollection.GetInputColumnByName(vcolumn.Name) <> vbNull Then
                '        bFound = False
                '    End If
                'Next
            Next
    
            ' Release the connection
            DestinationInstance.ReleaseConnections()


    Orlanzo

    17. února 2012 21:16

Všechny reakce

  • I've been looking at this for some time now.  It turns out the error is actually occuring at the OLE DB Source.  The source and destination components retrieve meta data from similar databases on different servers.  One is production and the other is development.  The source component is set to pull from production, but the column referred to in the error only exists in the destination!  What the heck??

    The package is created programmatically so I saved it to disk.  The error is visible in the ole db source component.  Double-click the component and I'm presented with a dialog where SSIS would fix the issue automatically.  I'm puzzled though.  How did the column find its way into the source.

    Here's the full code block...

        Public Sub Main()
            Dim package As New Package()
            Dim logFolder As String = Dts.Variables("LogFileFolder").Value.ToString
            Dim pkgName As String = Dts.Variables("PackageName").Value.ToString
            Dim DestinationTable As String = Dts.Variables("DestinationTable").Value.ToString
            Dim logPath As String = logFolder + pkgName + "_TransferTable_" + _
                    DestinationTable + ".txt"
    
            Dim e As Executable = package.Executables.Add("DTS.Pipeline.1")
            Dim thMainPipe As TaskHost = CType(e, TaskHost)
            Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
    
            ' Add OLEDB connection managers to the package.
            Dim cmSource As ConnectionManager = package.Connections.Add("OLEDB")
            Dim cmDestination As ConnectionManager = package.Connections.Add("OLEDB")
    
            cmSource.Name = "Source"
            cmSource.ConnectionString = Dts.Connections("Source").ConnectionString
            cmDestination.Name = "Destination"
            cmDestination.ConnectionString = Dts.Connections("Destination").ConnectionString
    
            ' Add an OLE DB source to the data flow.
            Dim compSource As IDTSComponentMetaData90 = _
              dataFlowTask.ComponentMetaDataCollection.New()
            compSource.Name = "OLEDBSource"
            compSource.ComponentClassID = "DTSAdapter.OleDbSource.1"
    
            ' Get the design time instance of the component.
            Dim SourceInstance As CManagedComponentWrapper = compSource.Instantiate()
    
            ' Initialize the component.
            SourceInstance.ProvideComponentProperties()
    
            ' Specify the connection manager.
            If compSource.RuntimeConnectionCollection.Count > 0 Then
                compSource.RuntimeConnectionCollection(0).ConnectionManager = _
                  DtsConvert.ToConnectionManager90(package.Connections(0))
                compSource.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections("Source").ID
    
            End If
    
            ' Set the custom properties.
            SourceInstance.SetComponentProperty("AccessMode", 2)
            SourceInstance.SetComponentProperty("SqlCommand", _
              Dts.Variables("SourceTable").Value.ToString)
    
            Dts.Events.FireInformation(-1, "Transfer Table Data", "Processing table: " + _
                Dts.Variables("DestinationTable").Value.ToString, "", 0, False)
            Dts.Events.FireInformation(-1, "Transfer Table Data", "Source Query: " + _
                Dts.Variables("SourceTable").Value.ToString, "", 0, False)
    
            ' Reinitialize the metadata.
            SourceInstance.AcquireConnections(vbNull)
            SourceInstance.ReinitializeMetaData()
            'SourceInstance.ReleaseConnections()
    
            ' Add other components to the data flow and connect them.
            ' Add an OLE DB destination to the data flow.
            Dim compDestination As IDTSComponentMetaData90 = _
              dataFlowTask.ComponentMetaDataCollection.New()
            compDestination.Name = "OLEDBDestination"
            compDestination.ComponentClassID = "DTSAdapter.OleDbDestination.1"
    
            ' Get the design time instance of the component.
            Dim DestinationInstance As CManagedComponentWrapper = compDestination.Instantiate()
    
            ' Initialize the component.
            DestinationInstance.ProvideComponentProperties()
    
            ' Create the path.
            Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()
            path.AttachPathAndPropagateNotifications(compSource.OutputCollection(0), _
              compDestination.InputCollection(0))
    
            ' Specify the connection manager.
            If compDestination.RuntimeConnectionCollection.Count > 0 Then
                compDestination.RuntimeConnectionCollection(0).ConnectionManager = _
                  DtsConvert.ToConnectionManager90(package.Connections(0))
                compDestination.RuntimeConnectionCollection(0).ConnectionManagerID = package.Connections("Destination").ID
            End If
    
            ' Set the custom properties.
            DestinationInstance.SetComponentProperty("AccessMode", 3)
            DestinationInstance.SetComponentProperty("OpenRowset", _
              Dts.Variables("DestinationTable").Value.ToString)
            DestinationInstance.SetComponentProperty("FastLoadKeepIdentity", True)
            DestinationInstance.SetComponentProperty("FastLoadOptions", "TABLOCK")
            'compDestination.ValidateExternalMetadata = False
    
            ' Reinitialize the metadata.
            DestinationInstance.AcquireConnections(vbNull)
            DestinationInstance.ReinitializeMetaData()
    
            ' Get the destination's default input and virtual input.
            Dim input As IDTSInput90 = compDestination.InputCollection(0)
            Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput
    
            ' Iterate through the virtual input column collection.
            For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
                ' Call the SetUsageType method of the destination
                '  to add each available virtual input column as an input column.
                DestinationInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
            Next
    
            ' Map input columns to external columns
            For Each vColumn As IDTSInputColumn90 In input.InputColumnCollection
                DestinationInstance.MapInputColumn(input.ID, vColumn.ID, input.ExternalMetadataColumnCollection(vColumn.Name).ID)
            Next
    
            For Each eColumn As IDTSExternalMetadataColumn90 In input.ExternalMetadataColumnCollection
                Dim bFound As Boolean = False
                For Each iColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
                    If iColumn.Name = eColumn.Name Then
                        bFound = True
                    End If
                Next
                If bFound = False Then
                    input.ExternalMetadataColumnCollection.RemoveObjectByID(eColumn.ID)
                End If
            Next
    
            ' Release the connection
            DestinationInstance.ReleaseConnections()
    
            ' Enable logging
            Dim loggingConnection As ConnectionManager = package.Connections.Add("FILE")
            Dim provider As LogProvider = package.LogProviders.Add("DTS.LogProviderTextFile.1")
    
            loggingConnection.ConnectionString = logPath
            loggingConnection.Name = "Log"
            provider.ConfigString = loggingConnection.Name
            package.LoggingOptions.SelectedLogProviders.Add(provider)
            package.LoggingOptions.EventFilterKind = DTSEventFilterKind.Inclusion
            package.LoggingOptions.EventFilter = New String() {"OnPreExecute", _
               "OnPostExecute", "OnError", "OnWarning", "OnInformation"}
            package.LoggingMode = DTSLoggingMode.Enabled
    
            ' Save the package.  This is useful for debugging.
            Dim app As Application = New Application
            app.SaveToXml("C:\" + Dts.Variables("DestinationTable").Value.ToString + ".dtsx", package, Nothing)
    
            ' Run the package.
            Dim result As DTSExecResult
            Dim eventListener As EventListener
    
            'result = package.Execute(Nothing, Nothing, eventListener, Nothing, Nothing)
            'If result = DTSExecResult.Failure Then
            '    Dim pkgError As DtsError
            '    For Each pkgError In package.Errors
            '        Dts.Events.FireError(-1, "Transfer Tables", pkgError.Description _
            '            , pkgError.HelpFile, pkgError.HelpContext)
            '    Next
            '    Dts.TaskResult = Dts.Results.Failure
            'Else
            '    ' Drop the package log file
            '    DropFile(logPath)
            '    Dts.TaskResult = Dts.Results.Success
            'End If
    
            Dts.Events.FireInformation(-1, "Transfer Tables", "Package.Execute: " + result.ToString, "", 0, False)
    
        End Sub


    Orlanzo

    18. února 2012 5:40
  • Hi Orlanzo,

    Please refer to the following related links:
    http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/aca6514f-7cc0-4e35-9f4d-7405ab830de7 
    http://munishbansal.wordpress.com/2009/06/09/dynamic-columns-mapping-%E2%80%93-script-component-as-destination-ssis/ 

    Please feel free to let me know if my understanding is wrong.

    Thanks,
    Eileen

    TechNet Subscriber Support

    If you areTechNet Subscriptionuser and have any feedback on our support quality, please send your feedbackhere.

    20. února 2012 15:00
  • Hi Eileen,

    Thank you for supplying the links.  I had run across the first in earlier research.  Neither resolves the issue I'm seeing.

    I'm refreshing table (T) in our development (D) database from production (P).  There can be some differences between the table.  In this case, (D).T has an additional column when compared with (P).T.  The databases are hosted on different servers S1 and S2.

    (P).T = {"colA", "colB", "colC"}

    (D).T = {"colA", "colB", "colC", "colD"} -- Note the additional column.

    In my code sample, I didn't think this would be an issue as I assumed the virtual input collection for the OLE DB destination would only contain the columns defined as (P).T.  My assumption isn't correct.  I was surprised it also contained (D).T.colD

    Is my understanding of the virtual input collection incorrect?  Should it contain the destination columns as well? 

    I looped over the collection in the ForEach loop and was able to see the additinal column.  It didn't duplicate columns i.e. it didn't contain two columns named colA.  The sets for (P).T and (D).T are equally named.  The only difference is (D).T contains an additional column that does not exist in (P).T.

    According to BOL, the virtual input collection contains all columns from the source that are available to the destination.  The destination can select upon one or more of them.  The Advanced Editor for an OLE DB Destination would show map unused destination columns as "<Ignore>".   How can I accomplish the same?

    The package is being created programmatically.  It was trivial to save it to disk.  Upon opening the package, I saw the error was occuring within the OLE DB Source - not the destination.  Visual Studio offered to and was able to fix the package automatically.  I'd assume I'd be able to accomplish the same within my code.

    If I open the OLE DB Source and do not fix the problem, the mapping tab contains the extra column, "colD", from (D).T.  It's frustratingly puzzling at present.


    Orlanzo

    20. února 2012 16:19