Nachdem wir im letzten Blog-Kapitel alle Komponenten eines Data Flows durchlaufen konnten und dazu erkennen konnten, ob eine Fehlerbehandlung möglich und noch nicht vorhanden ist, müssen wir nun die Fehlerbehandlung in diesen Fällen einbauen.

Die Fehlerbehandlung soll wie folgt funktionieren:

• Fehlerbehandlung auf Redirect Row einstellen
• Den Error Output in eine neu zu erstellende Derived Column leiten.
• In der Derived Column sollen einige zusätzliche Attribute wie Fehlermeldung, aber auch Primärschlüssel hinzugefügt werden
• Das Ergebnis läuft dann in einen UNION ALL, der bereits im Paket existiert.

Damit ist die Quarantäne noch nicht ganz fertig. Das UNION ALL führt dann zu einem SQL-Server-Ziel. Dort werden die Spalten aus der Union All in die Datenbank geschrieben. Diesen Teil habe ich, da er nur einmal (je Data Flow) zu erstellen ist, aber manuell gelöst. Hier ist der Aufwand geringer als eine automatisierte Lösung zu implementieren.

Fehlerbehandlung auf Redirect Row umstellen

Interessanter Weise gibt es unterschiedliche Arten von Fehlerbehandlungen in SSIS. Bei manchen Komponenten wird die Fehlerbehandlung einzeln für jede Spalte (z.B. derived column) definiert, in anderen nur global für die gesamte Komponente, in manchen auch für beides.

Der Lookup hat beide Fehlerbehandlungen:

Hier ein Beispiel für die Fehlerbehandlung einer Derived Column:

Im Advanced Editor sieht das dann so aus:

Und die globale Komponenten-Fehlerbehandlung wird nicht verwendet:

Wichtig ist zu bemerken, dass diese Einstellungen natürlich nicht in dem Error Output, sondern in dem normalen (bzw. allen normalen) Output(s) und Input(s) gemacht werden müssen.

Deswegen durchläuft der Code alle Inputs und alle Spalten aller Inputs und das gleiche für die Outputs:

For Each outp As IDTSOutput100 In comp.OutputCollection
‚ globale Fehlerhandlung
If outp.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent Then
outp.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow
End If
If outp.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent Then
outp.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow
End If
‚Fehlerbehandlung je Spalte
For Each col As IDTSOutputColumn100 In outp.OutputColumnCollection
If col.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent Then
col.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow
End If
If col.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent Then
col.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow
End If
Next col
Next outp

Und das gleiche für die Inputs:

For Each inp As IDTSInput100 In comp.InputCollection . . .

Am besten merkt man sich noch in einer boolschen Variablen, ob ein Redirect Row eingestellt wurde. Nur dann darf man nämlich den nächsten Schritt machen.

Dies ist wichtig, da die Fehlermeldungen bei der SSIS-API-Entwicklung meist kryptisch sind, da .NET-Wrapper von der API verwendet werden.  

Eine Derived Column-Komponente erstellen

Wir erstellen sie so:

’neue Derived Column
Dim compDerivedCol As IDTSComponentMetaData100 = pipe.ComponentMetaDataCollection.New()
compDerivedCol.ComponentClassID = „DTSTransform.DerivedColumn“
Dim DesignDerivedTransformColumns As CManagedComponentWrapper = compDerivedCol.Instantiate()
DesignDerivedTransformColumns.ProvideComponentProperties()
compDerivedCol.Name = „Fehler_“ & comp.Name
compDerivedCol.InputCollection(0).ExternalMetadataColumnCollection.IsUsed = False
compDerivedCol.InputCollection(0).HasSideEffects = False

Man beachte das Initiate(). Dadurch wird die derived Column wie im Visual Studio auch instantiiert. Das heißt, man kann auf alle Voreinstellungen zugreifen.

Den Fehler-Output und die Derived Column verbinden

Hierzu legt man einen neuen Pfad an, der den Error Output (den wir im letzten Blog gefunden und gemerkt hatten) mit der Derived Column verbindet:

Dim path As IDTSPath100 = pipe.PathCollection.New()
path.AttachPathAndPropagateNotifications(comp.OutputCollection(nrOfErrorOutput), compDerivedCol.InputCollection(0))

Zusätzliche Spalten in der Derived Column anlegen

Da man das immer wieder braucht, habe ich dazu eine Methode erstellt.

Diese erhält als Parameter:

• die Komponente (compDerivedCol)
• den Namen der Spalte
• den Datentyp, also zum Beispiel Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4
  Hier kann man wunderbar den vorhandenen enum verwenden.
• die Länge des Datentyps – nur bei Strings (o.ä.) nötig. Bei Integer kann man 0 angeben. Das System macht es automatisch richtig.
• Die Formel, also z.B. „(DT_WSTR,50)@[System::PackageName]“ für den Paketnamen – oder zum Auswerten eigener Variablen. Die Formel darf keine Anführungszeichen (“) enthalten.
• Optional die LineageID – dazu komme ich im Anschluss

Hier der Code:

Private Sub addNewColumn2DerivedComponent(derivedColumnComponent As IDTSComponentMetaData100, name As String, dataType As Wrapper.DataType, dataTypeLaenge As Integer, expression As String, Optional lineageIDKeyColumn As Integer = 0)
Dim neueSpalte As IDTSOutputColumn100 = derivedColumnComponent.OutputCollection(0).OutputColumnCollection.New()
neueSpalte.Name = name
neueSpalte.SetDataTypeProperties(dataType, dataTypeLaenge, 0, 0, 0)
neueSpalte.ExternalMetadataColumnID = 0
neueSpalte.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure
neueSpalte.TruncationRowDisposition = DTSRowDisposition.RD_IgnoreFailure

Dim neueSpalteProp As IDTSCustomProperty100 = neueSpalte.CustomPropertyCollection.New()
neueSpalteProp.Name = „Expression“
If lineageIDKeyColumn = 0 Then
neueSpalteProp.Value = expression
Else
neueSpalteProp.Value = „(DT_WSTR,“ & dataTypeLaenge & „)#“ + lineageIDKeyColumn.ToString()
End If
neueSpalteProp = neueSpalte.CustomPropertyCollection.New()
neueSpalteProp.Name = „FriendlyExpression“
neueSpalteProp.Value = expression

End Sub

Hier einige Erklärungen dazu:

• Am Anfang wird die Spalte angelegt mit dem entsprechenden Namen und Datentyp
• Die derived column hat als Fehlertyp “Ignore Failure”, weil wir ja sonst die Idee der Quarantäne ad absurdum führen würden.
• Dann werden zwei Properties angelegt, die notwendig sind – die Expression und die “friendly expression”. Mit der “Expression” rechnet SSIS, die “friendly expression” wird angezeigt.

Und jetzt die Geschichte mit der lineage-ID, wie versprochen:

Man kann auch in den Formeln (expression) auf bestehende Spalten zugreifen. Das funktioniert über die lineage-Id der Spalte, also beispielsweise so “(DT_WSTR,50)#17”. Die “friendly expression” muss nicht unbedingt angegeben werden.

Die lineage-ID kann man sich ermitteln, indem man alle Spalten des Inputs durchläuft und den Namen der Spalte kennt. Allerdings kommt dabei der Wrapper für managed code zum Einsatz, den ich vorher bereits angedeutet habe. Wir benötigen nämlich hier Designer-Funktionalität, wie sie im Visual Studio auch vorhanden ist. Deswegen sieht der Code etwas hässlich aus:

Private Function findeSpalte(comp As IDTSComponentMetaData100, nameSpalte As String, ByRef nameSpalteGefunden As String) As Integer
Dim inp As IDTSInput100 = comp.InputCollection(0)
Dim virtualInp As IDTSVirtualInput100 = inp.GetVirtualInput()
Dim virtualInpCols As IDTSVirtualInputColumnCollection100 = virtualInp.VirtualInputColumnCollection
Dim designer As CManagedComponentWrapper = comp.Instantiate()

nameSpalteGefunden = „“

For Each virtualCol As IDTSVirtualInputColumn100 In virtualInpCols
If virtualCol.Name = nameSpalte Then
designer.SetUsageType(inp.ID, virtualInp, virtualCol.LineageID, DTSUsageType.UT_READONLY)
nameSpalteGefunden = nameSpalte
Return virtualCol.LineageID
End If
Next

Return 0
End Function

Diese Funktion stellt auch den UsageType der Spalte auf Readonly. Dies erscheint zunächst unnötig, ohne dies funktioniert es  aber nicht. Dies spiegelt wider, was der Advanced Editor für die Spalten anzeigt, die in den Formeln verwendet werden:

In diesem Beispiel wurde die EinrichtungID-Spalte in einer Formel verwendet.

So habe ich die Primärschlüssel, die ich in der Quarantäne-Tabelle mit protokollieren wollte, gefunden.

Die Derived Column mit der UNION ALL verbinden

Das funktioniert genauso wie der Pfad zur derived column, muss also nicht im Detail beschrieben werden.

Damit sind wir nun fast fertig. Es sind lediglich ein paar Besonderheiten zu beachten – s. nächster Blog-Eintrag.