SSIS: Rückgaben aus SQL-Commands entgegennehmen
avatar

Das Problem

In der letzten Woche stand ich bei der Entwicklung eines Datenimports vor der Aufgabe Personaldaten aus einem Warehouse in die Fachanwendungsdatenbank zu synchronisieren.
Ansich ist das ja nichts besonderes, allerdings gab es die Problemstellung, dass die Adressen der Personen aus einem Adress-Katalog (ebenfalls im Warehouse) bezogen wurden, es aber auch Adressen gab, die nicht in diesem Adress-Katalog enthalten waren.
Folglich mussten die nicht vorhandenen Adressen in die Fachanwendungsdatenbank eingefügt und mit dem Personen-Datensatz verknüpft werden.
Da das Anlegen der Adress-Datensätze ein wenig mehr Logik beinhaltete, entschied ich mich dafür eine Stored-Procedure zu verwenden.

Das Hauptproblem hierbei war, dass die ID des neu angelegten Adress-Datensatzes direkt an den SSIS-Datenfluss weitergereicht werden musste, damit diese in dem anzulegenden Personen-Datensatz gesetzt werden konnte.

Die Lösung

Zum Nachvollziehen des Beispiels bitte folgende Tabelle anlegen:

CREATE TABLE [dbo].[Address](
	[AddressId] [int] IDENTITY(1,1) NOT NULL,
	[Streeet] [varchar](100) NOT NULL,
	[HouseNo] [int] NOT NULL,
	[HouseNoAdditional] [varchar](50) NULL,
	[ZipCode] [varchar](5) NOT NULL,
	[Town] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
(
	[AddressId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Der grundlegende Aufbau des SSIS-Pakets sieht folgendermaßen aus:

image

Die OLE-DB-Source “Quelle” muss mit folgendem SQL-Command-Text gefüllt werden:

SELECT *, 0 AS 'AddressIdReturn'
FROM
(
    -- Dummy Data
    SELECT 'Teststraße 1' AS 'Street', 1 AS 'HouseNo', 'a' AS 'HouseNoAdditional', '20148' AS 'ZipCode', 'Hamburg'  AS 'Town' UNION
    SELECT 'ABC-straße 2', 1, 'b', '20148', 'Hamburg' UNION
    SELECT 'Lindenweg 3', 1, 'c', '20148', 'Hamburg' UNION
    SELECT 'Bahnhofsstraße 4', 1, 'd', '20148', 'Hamburg' UNION
    SELECT 'Mühlenstraße 5', 1, 'e', '20148', 'Hamburg'
) As t

Die UNION-SQL-Statements innerhalb der Klammer sind Dummy Daten (damit spart man sich das Anlegen der “Warehouse-Adress-Tabelle”).
Die Zeile „0 AS ‚AddressIdReturn‘“ fügt eine leere Spalte zur späteren Aufnahme der “AddressID” hinzu. Sie könnte aber auch über ein separates „Derived Column“ angelegt werden.

Das “Dummy-Sort” habe ich nur eingefügt, damit ein Datenviewer nach dem Hinzufügen des Address-Datensatzes gesetzt werden konnte.

Der wirklich interessante Teil findet in dem OLE-DB-Command-Task “Add Address-DS” statt.

Hier gibt es nun 2 Möglichkeiten die Address-ID zu übergeben:

Möglichkeit 1: Stored-Procedure mit Output-Parameter

Hierbei bekommt die Stored-Procedure einen zusätzlichen Parameter namens “AddressId” der mit dem Zusatz „OUTPUT“ versehen ist. Innerhalb der Procedure wird dann diesem Parameter der gewünschte Rückgabewert zugewiesen.
Hierfür muss folgende Stored-Procedure in der Datenbank erstellt werden:

CREATE  PROCEDURE dbo.createAddressDataViaOutputParameter
	@street				VARCHAR(100),
	@houseNo			INT,
	@houseNoAdditional	        VARCHAR(50),
	@zipCode			VARCHAR(5),
	@town				VARCHAR(50),
	@addressId			INT OUTPUT
AS
BEGIN
    INSERT INTO [dbo].[Address]
	(
		Streeet, HouseNo, HouseNoAdditional, ZipCode, Town
	)
	SELECT
		@street, @houseNo, @houseNoAdditional, @zipCode, @town
        SET @addressId = (SELECT SCOPE_IDENTITY())
END   -- Tests
-- exec dbo.createAddressDataViaOutputParameter @street=?, @houseNo=?, @houseNoAdditional=?, @zipCode=?, @town=?, @addressId=? OUTPUT

Im SSIS-SQL-Command wird dann der Parameter zusätzlich mit angegeben in der Form „@addressId=? OUTPUT„. Das nachfolgende „OUTPUT“ ist von entscheidender Bedeutung.

Der vollständige Code für das SQL-Command lautet:

exec dbo.createAddressDataViaOutputParameter @street=?, @houseNo=?, @houseNoAdditional=?, @zipCode=?, @town=?, @addressId=? OUTPUT

Die Rückgabe erfolgt wie die Zuweisung eines normalen Parameters:
image

 

Möglichkeit 2: OLE-DB-Zuweisung

Dies ist die universellste Möglichkeit, denn sie kann auf jegliches SQL und nicht nur auf Stored-Procedures angewandt werden.
Hierbei wird die Stored-Procedure ohne jegliche OUTPUT-Parameter erstellt und lediglich am Ende der Procedure eine RETURN-Anweisung eingefügt, welche die Adress-ID zurückgibt.
Hierfür bitte folgende Stored-Procedure in der Datenbank erstellen:

ALTER PROCEDURE dbo.createAddressDataViaAssignment
	@street				VARCHAR(100),
	@houseNo			INT,
	@houseNoAdditional	VARCHAR(50),
	@zipCode			VARCHAR(5),
	@town				VARCHAR(50)
AS
BEGIN
    INSERT INTO [dbo].[Address]
	(
		Streeet, HouseNo, HouseNoAdditional, ZipCode, Town
	)
	SELECT
		@street, @houseNo, @houseNoAdditional, @zipCode, @town
 
	RETURN SCOPE_IDENTITY()
END
 
-- Tests
-- exec ?=dbo.createAddressDataViaAssignment @street=?, @houseNo=?, @houseNoAdditional=?, @zipCode=?, @town=?
 
 

Innerhalb des SQL-Command-Aufrufs muss dann nach dem „EXEC“ ein „?=“ folgenden.
Der Code für das SQL-Command muss folgendermaßen lauten:

exec ?=dbo.createAddressDataViaAssignment @street=?, @houseNo=?, @houseNoAdditional=?, @zipCode=?, @town=?

Bei dieser Art der Rückgabe erscheint in den “Column Mappings” dann die Spalte “@RETURN VALUE”:

image

Fazit

Beide Methoden haben ihre Vor- und Nachteile und es kommt, mal wieder, darauf an in welchem Kontext sie verwendet werden sollen.

In meinem Fall war es relativ egal, welche der beiden Methoden ich genutzt habe und ich habe mich für die Methode 2 entschieden.
In Fällen in denen keine Stored-Procedure erstellt werden, sondern mit einzelnen SQL-Statements gearbeitet werden soll, ist nur Methode 1 anwendbar.

Links

[1] http://www.sqlsafety.com/?p=649
[2] http://www.josefrichberg.com/journal/2011/6/30/ssis-oledb-command-and-procedure-output-params.html
[3] http://bidn.com/blogs/BillBrannen/bidn-blog/612/stored-procedure-return-values-and-output-parameters-in-ssis

Schreibe einen Kommentar