« Azure IaaS for IT Pros Online Event
Nur ein kleiner Fehler »

SFTP in SSIS Paketen

Während FTP problemlos in SSIS Paketen unterstützt wird, fehlt es hier leider an den Möglichkeiten mit SFTP-Servern zu sprechen. Es gibt hier diverse Fremdanbieter, die ihre Tools zur Verfügung stellen, oder man erstellt sich selber eine Lösung.

Fremdanbieter

An dieser Stelle möchte ich exemplarisch nur einen Fremdanbieter nennen, ohne das hiermit eine Wertung verbunden wäre. Die Firma pragmaticworks stellt in seiner Task-Factory neben SSIS-Tasks wie Download File (via HTTP), Compression Task (Zip or Unzip) auch einen Secure FTP Task zur Verfügung.

Wer nur einen SFTP-Task sucht, oder auch nur wenige Pakte damit entwickeln möchte, kann dies auch mit einer selbst erstellten Lösung tun.

SFTP Anbindung selber realisieren

Download und Installation

Man benötigt hierzu ein Tool, welches die Verbindung durchführt und über den Task 'Prozess ausführen' in einem SSIS-Paket angesprochen werden kann.

In diesem Beispiel verwenden wir das kostenlose Tool WINSCP. Nachdem das Installationspaket heruntergeladen wurde, kann die Software auf dem Server installiert werden. Hierzu habe ich die Custom Installation ausgewählt und alle überflüssigen Optionen abgewählt. Nur hier kann man auch das Verzeichnis wählen, in dem die Software später liegen soll. Ich habe sie in einen Pfad gelegt, der für dieses Projekt auf dem Laufwerk D zur Verfügung steht. Auf meinem Entwickler-PC habe ich auch das Laufwerk D und dort ebenfalls im gleichen Verzeichnis die Software installiert.

Kommunikation mit dem SFTP-Server

Neben der grafischen Variante gibt es auch eine Kommandozeilenversion, die wir im folgenden verwenden werden. Diese ruft man aus seinem SSIS-Paket heraus auf (Task "Prozess ausführen") und gibt als Parameter den Namen einer Text-Datei an, in der die für die Verbindung notwendigen Einstellungen hinterlegt sind.

Zu Punkt 1 in der Grafik:
Der Parameter lautet hier also: -script=d:\<pfad>\sftp\sftp_Inhaltsverzeichnis.txt

In dem Pfad habe ich das Unterzeichnis sftp angelegt, um dort alle benötigten Scripte abzulegen. Dieses erste ist statisch und dient dazu, das Inhaltsverzeichnis des SFTP-Servers abzuholen. Der Inhalt des Skriptes sieht im Prinzip so aus:

option batch on
option confirm off
open sftp://<username>:<password>@<sftp -server>:22 -hostkey="ssh-rsa 1024 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
option transfer binary
ls <dateiname>*.zip
close
exit

Man beachte, dass die Angaben von open bis :xx" in eine Zeile gehören!

Den ssh Schlüssel erhält man, wenn man über die Kommandozeile die Verbindung aufruft und hier den Parameter "-hostkey" weglässt. Der SFTP-Server liefert dann den Key, damit man ihn akzeptieren kann. Wir wollen ihn aber aus der Eingabeaufforderung herauskopieren. Anschließend wird der hostkey oben im Script ersetzt und zukünftig kann die Verbindung direkt aufgebaut werden.

Zu Punkt 2 in der Grafik:
Die Rückgabe von dieser Verbindung lassen wir uns über einen Ereignishandler sowohl in das Ausführungsprotokoll des SSIS-Pakets schreiben, als auch direkt in eine Tabelle SSIS_Protokoll. Wie das im Detail geht, habe ich in meinem Artikel Task 'Prozess ausführen' mit Ereignishandler ausführlich beschrieben.

Die aktuellste Zeile in der Tabelle SSIS_Protokoll enthält nun also eine Liste der Dateien, die unserer Suchmaske entsprechen. Diese Liste muss geparsed werden (z. B. in einer While-Schleife) und für jede gefundene Datei kann eine Aktion erfolgen, z. B. ein Download der Datei.

Download per SFTP

Damit wir eine Datei wieder über WINSCP mit einem Skript gesteuert herunterladen können, benötigen wir erst mal eine Tabelle, die das Grundgerüst unseres Skriptes enthält:

CREATE TABLE [dbo].[CSV_FTP_Script](
   [Zeile] [int] NOT NULL,
   [Befehl] [varchar](500) NOT NULL,
CONSTRAINT [XPKCSV_FTP_Script] PRIMARY KEY CLUSTERED
(
   [Zeile] ASC
));

INSERT [dbo].[CSV_FTP_Script] ([Zeile], [Befehl]) VALUES (1, N'option batch on');
INSERT [dbo].[CSV_FTP_Script] ([Zeile], [Befehl]) VALUES (2, N'option confirm off');
INSERT [dbo].[CSV_FTP_Script] ([Zeile], [Befehl]) VALUES (3, N'open sftp://<username>:<password>@<sftp-server>:22 -hostkey="ssh-rsa 1024 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"');
INSERT [dbo].[CSV_FTP_Script] ([Zeile], [Befehl]) VALUES (4, N'option transfer binary');
INSERT [dbo].[CSV_FTP_Script] ([Zeile], [Befehl]) VALUES (5, N'get <dateiname_remote> D:\<pfad\<dateiname_lokal>');
INSERT [dbo].[CSV_FTP_Script] ([Zeile], [Befehl]) VALUES (6, N'close');
INSERT [dbo].[CSV_FTP_Script] ([Zeile], [Befehl]) VALUES (7, N'exit');

Die Zeile 3 in dem Skript entspricht der open-Anweisung im ersten Skript.

Jedes mal, bevor wir eine Datei herunterladen wollen, aktualisieren wir die Zeile 5 in der Tabelle mit den korrekten Dateinamen. Dann rufen wir ein SSIS-Paket auf, welches zuerst den Inhalt der Tabelle in ein Flatfile-Ziel auslagert, damit wir diese Text-Datei im nächsten Schritt als Skript für WINSCP verwenden können. Der Name der Datei heißt z. B. "sftp_Download.txt", dann ist der Parameter für das Kommando

-script=d:\<pfad>\sftp\sftp_Download.txt

Die weitere Verarbeitung im Task "Paket per SFTP holen" ist genau so, wie beim Inhaltsverzeichnis und im Artikel Task 'Prozess ausführen' mit Ereignishandler beschrieben.

Eine Auswertung der Tabelle SSIS_Protokoll ist hier nur im Fehlerfall notwendig.

  SFTP_Inhaltsverzeichnis.txt
  SFTP_Download.txt
  CSV_FTP_Script.sql

entwicklung, integration services, tools
SQL Server
 Print article This entry was posted by cmu on 06.11.14 at 11:54:00 . Follow any responses to this post through RSS 2.0.