Kürzlich bin ich bei einem Kundenprojekt auf ein unerwartetes Verhalten bei der synchronen Transformationen in der Skriptkomponente gestoßen. Beim Erzeugen einer neuen Spalte in der Skriptkomponente wurde die Ausgabespalte von Typ integer mit 0-Werten befüllt. Dabei hätte ich erwartet, dass die Spalte NULL-Werte enthält. Interessanterweise verhält sich SSIS bei einer asynchronen Datentransformation anders. Um dies zu vermeiden, sollte man stets die entsprechenden Ausgabespalten am Anfang explizit auf NULL setzen.
Betrachten wir beide Fälle anhand zweier Beispiele.
Erzeugen neuer Spalten bei der Transformation in einer synchronen Skriptkomponente
Der Datenflusstask ist in unserem Beispiel ziemlich einfach und besteht aus drei Schritten.
Aus der Tabelle Person (AdventureWorks-Datenbank) werden mit einem einfachen SELECT-Statement Daten für zwei Spalten gelesen:
SELECT [BusinessEntityID]
,[PersonType]
FROM [Person].[Person]
Die synchrone Datentransformation in der Skriptkomponente soll neue Spalten erzeugen (PersonVarchar, PersonInteger, PersonDate, PersonBoolean und PersonNumeric). Somit können wir das Setzen von Standardwerten für Spalten von unterschiedlichen Datentypen testen.
Die Methode Eingabe0_ProcessInputRow() übernimmt die Fachlogik zum Setzen der Werte, falls bestimmte Bedingungen zutreffen. In dem Fall, wenn die Spalte PersonType den Wert „EM“ enthält, soll der Ausgabespalte PersonInteger und PersonVarchar bestimmte Werte zugewiesen bekommen.
public override void Eingabe0_ProcessInputRow(Eingabe0Buffer Row)
{
if (!Row.PersonType_IsNull && Row.PersonType.Equals("EM"))
{
Row.PersonInteger = 123;
Row.PersonVarchar = "testName";
}
}
Die anderen neuen Ausgabespalten werden in der Methode gar nicht behandelt. Somit kann getestet werden, welche Werte erzeugt werden, wenn die Ausgabespalten in dem Skript gar nicht „angesprochen“ werden.
Die erzeugten Daten werden in eine Tabelle geschrieben, die mit folgendem Statement vorher angelegt wird:
CREATE TABLE [dbo].[Person2]( [BusinessEntityID] [int] NULL, [PersonType] [nvarchar](2) NULL, [PersonInteger] [int] NULL, [PersonVarchar] [nvarchar](50) NULL, [PersonDate] [datetime] NULL, [PersonBoolean] [bit] NULL, [PersonNumeric] [numeric](18, 5) NULL )
Es ist ersichtlich, dass auch NULL-Werte zugelassen sind.
Nach der erfolgreichen Ausführung des Datenflusstasks können die Ergebnisse betrachtet werden.
Bei den Zeilen, wo die Spalte PersonType den Wert „EM“ enthält hat SSIS für die Spalten PersonInteger und PersonVarchar die Werte zugewiesen, welche auch angedacht waren. Bei den anderen Spalten mit den PersonType=“EM“ sowie bei den Zeilen, wo PersonType != “EM“ war, hat SSIS Standardwerte je nach Datentyp zugewiesen. So hat Spalte PersonDate den Datumswert „1899-12-30 00:00:00.000“ , PersonBoolean „false“ und PersonNumeric den Wert „0.0000“, PersonVarchar einen Leerstring und PersonInteger den Wert 0.
Bei einer komplexeren Logik kann es ziemlich schwierig sein, so einen Fehler zu identifizieren (wenn bei einem Integer-Wert 0 steht und nicht etwa 1). Deshalb sollte man alle neuen Ausgabespalten vorher auf NULL setzen, bevor die Fachlogik angewendet wird. Falls eine bestimmte Fachlogik nicht zutrifft (z.B. PersonType !=“EM“), dann sollten solche Fälle auch behandelt werden.
public override void Eingabe0_ProcessInputRow(Eingabe0Buffer Row)
{
Row.PersonInteger_IsNull = true;
Row.PersonVarchar_IsNull = true;
Row.PersonNumeric_IsNull = true;
Row.PersonBoolean_IsNull = true;
Row.PersonDate_IsNull = true;
if (!Row.PersonType_IsNull && Row.PersonType.Equals("EM"))
{
Row.PersonInteger = 123;
Row.PersonVarchar = "testName";
}
else
{
Row.PersonInteger_IsNull = true;
Row.PersonVarchar_IsNull = true;
}
}
Nach der Ausführung des Datenflusstasks mit dem angepassten Code, beinhalten die Datensätze für nicht behandelte Spalten statt Standardwerte die NULL-Werte.
Erzeugen neuen Spalten bei der Transformation in einer synchronen Skriptkomponente
Beim Testvorgang für das Setzen von Standardwerten in einer asynchronen Datentransformation in der Skriptkomponente kann die gleiche Person-Tabelle als Quelle verwenden werden.
Mit der nach PersonType sortierten Abfrage werden wir in der Skriptkomponente die Anzahl der Leute in der jeweiligen Gruppe ermitteln.
SELECT [BusinessEntityID]
,[PersonType]
FROM [Person].[Person]
ORDER BY PersonType
Dabei werden in der asynchronen Ausgabe neben den Spalten Persontyp und AnzahlVonDerGruppe zusätzlich noch Spalten mit unterschiedlichen Datentypen für die Testzwecke definiert: PersonVarchar, PersonInteger, PersonDate, PersonBoolean und PersonNumeric (ähnlich wie zuvor bei der synchronen Datentransformation).
Im Skript werden zunächst Variablen definiert, welche für die asynchrone Datentransformation benötigt werden:
private bool ersteZeile = true; private string personType; private int anzahlProGruppe = 0;
In der Methode Eingabe0_ProcessInputRow() bzw. ZeileHinzufuegen() wird die Ausgabe erzeugt. Da der Datenfluss nach Persontyp sortiert ist, wird für jede Zeile überprüft, ob es sich noch um gleichen Persontyp handelt. Wenn ja, wird der Zähler für anzahlProGruppe um 1 erhöht. Wenn nicht, wird eine neue Ausgabezeile für den jeweiligen Persontyp erzeugt. Es wird bei der Ausgabezeile überprüft, ob es sich bei PersonType um den Wert „EM“ handelt. In so einem Fall bekommen Ausgabespalten PersonInteger und PersonVarchar bestimmte Werte zugewiesen. Die anderen Ausgabespalten werden in der Methode zunächst nicht behandelt (ähnlich wie bei der synchronen Datentransformation).
public override void Eingabe0_ProcessInputRow(Eingabe0Buffer Row)
{
if (ersteZeile || personType == Row.PersonType)
{
ersteZeile = false;
personType = Row.PersonType;
anzahlProGruppe += 1;
}
else
{
ZeileHinzufuegen();
anzahlProGruppe = 1;
personType = Row.PersonType;
}
}
public override void FinishOutputs()
{
ZeileHinzufuegen();
base.FinishOutputs();
}
private void ZeileHinzufuegen()
{
AsyncAusgabe0Buffer.AddRow();
if (personType == "EM")
{
AsyncAusgabe0Buffer.PersonInteger = 1234;
AsyncAusgabe0Buffer.PersonVarchar = "testName";
}
AsyncAusgabe0Buffer.AnzahlVonDerGruppe = anzahlProGruppe;
AsyncAusgabe0Buffer.PersonTyp = personType;
}
Die Ergebnisse der Datentransformation werden ebenfalls in einer zuvor angelegten Tabelle gespeichert:
CREATE TABLE [dbo].[Person3]( [PersonTyp] [nvarchar](50) NULL, [AnzahlVonDerGruppe] [int] NULL, [PersonInteger] [int] NULL, [PersonVarchar] [nvarchar](50) NULL, [PersonDate] [datetime] NULL, [PersonBoolean] [bit] NULL, [PersonNumeric] [numeric](18, 5) NULL )
Die asynchrone Datentransformation hat insgesamt sechs Zeilen generiert.
Die Ergebnisse der Ausführung der asynchronen Datentransformation unterscheiden sich zur synchronen Verarbeitung dahingehend, dass SSIS für NULL Werte nicht 0 bei Integer speichert sondern NULL-Werte für nicht behandelte Spalten generiert werden.
Es ist bemerkenswert, dass in einer asynchronen Datentransformation (im Unterschied zur synchronen Transformation) auch bei nicht explizitem Setzen die NULL-Werte generiert wurden. Trotzdem sollte man sich darauf nicht verlassen und die Ausgabespalten am Anfang der Transformation auf NULL setzen. Dementsprechend könnte der Code für die Skriptkomponente für die Methode Zeilehinzufuegen() im o.g. Beispiel so aussehen:
private void ZeileHinzufuegen()
{
AsyncAusgabe0Buffer.AddRow();
AsyncAusgabe0Buffer.PersonDate_IsNull = true;
AsyncAusgabe0Buffer.PersonBoolean_IsNull = true;
AsyncAusgabe0Buffer.PersonInteger_IsNull = true;
AsyncAusgabe0Buffer.PersonNumeric_IsNull = true;
AsyncAusgabe0Buffer.PersonVarchar_IsNull = true;
if (personType == "EM")
{
AsyncAusgabe0Buffer.PersonInteger = 1234;
AsyncAusgabe0Buffer.PersonVarchar = "testName";
}
AsyncAusgabe0Buffer.AnzahlVonDerGruppe = anzahlProGruppe;
AsyncAusgabe0Buffer.PersonTyp = personType;
}
Leider habe ich bei der Recherche im Internet keine Antwort dafür gefunden, warum SSIS bei einer synchronen Datentransformation den Ausgabespalten diese Standardwerte zuweist. Vermutlich wird standardmäßig der erste nicht negative Wert herangezogen. Ich würde mich allerdings sehr freuen, einen Hinweis auf die Ursache des hier dargelegten Verhaltens in den Kommentaren zu bekommen.