[SSIS] Alle Tabellen einer SQL Server Datenbank einzeln als CSV Datei exportieren

[Thomas Darimont]

Hallo,

hier mal ein kleines Beispiel wie man mit SSIS (SQL Server Integration Services)
alle Tabellen einer SQL-Server Datenbank automatisch mittels des bcp Tools
als einzelne .csv-Dateien exportieren kann.

Als Beispiel-Datenbank nehme ich hier die AdventureWorks Version 2008:
http://msftdbprodsamples.codeplex.com/releases/view/37109

1) Wir erstellen ein neues SSIS-Projekt.

2) Neue Ado .Net Verbindung anlegen zur gewünschten Zieldatenbank anlegen. In meinem Fall ist
das die AdventureWorks2008 DB auf localhost.

3) In der Ablaufsteuerung definieren wir einen "SQL Ausführen" Task und stellen dort
unter "Allgemein" folgendes ein:
ResultSet: Vollständiges ResultSet
ConnectionType: Ado.Net
Connection: ist unsere vorhin erstellte Connection (bei mir: LocalHost.AdventureWorks2008)
SQLSourceType: Direkteingabe
SQLSource: Hier kommt unsere Abfrage für die Datenbank Metadaten:

select 
    TABLE_CATALOG
  , TABLE_SCHEMA
  , TABLE_NAME
  , TABLE_CATALOG +'.'+ TABLE_SCHEMA +'.'+ TABLE_NAME CatalogSchemaTable 
FROM 
    INFORMATION_SCHEMA.TABLES

Anschließend stellen wir unter "ResultSet"
ErgebnisName: 0
Variable: -> Neue Variable
Container: Package
Name: v_DatabaseTableMetadataCollection
Namespace: user
Werttyp: Object

Wir schließen die Eigenschaften ...
Dieser Task liefert uns in einer Variable die Metadaten (Catalog- / Schema- / Tabellennamen) die wir für den dynamischen CSV Export benötigen.


4) und legen einen neuen Foreach-Schleifen-Container in der Ablaufsteuerung an. Die Komponentenausgabe
von "SQL ausführen" wird die Eingabe des Foreach-Schleifen-Containers.

Wir klicken doppelt auf den Foreach-Schleifen-Container und stellen dort folgendes
ein:

"Auflistung"
Enumerator: Foreach-ADO-Enumerator
ADO-Objectquellvariable: hier wählen wir unsere zuvorerstelle
ResultSet Variable aus: User::v_DatabaseTableMetadataCollection

und wählen beim Enumerationsmodus:
Zeilen in der ersten Tabelle.

Unter "Variablenzuordnungen" legen wir nun folgende variablen an:
Die Variablen legen wir nun nach dem folgenden Stil an:
Container: Foreach-Schleifencontainer
Name: v_CurrentCatalogName
Namespace: User
Werttyp: String

-> Index 0

Container: Foreach-Schleifencontainer
Name: v_CurrentSchemaName
Namespace: User
Werttyp: String

-> Index 1

Container: Foreach-Schleifencontainer
Name: v_CurrentTableName
Namespace: User
Werttyp: String

-> Index 2

Container: Foreach-Schleifencontainer
Name: v_CurrentCatalogSchemaTableName
Namespace: User
Werttyp: String

-> Index 3

5) Nun erstellen wir noch eine globale Variable namens v_Export_Location
über die Variablen ansicht:
Variable hinzufügen:
Name: v_Export_Location
Typ: String
Wert: C:\temp\export

6) So nun legen wir innerhalb des Foreach-Schleifencontainers einen
"Prozess ausführen"-Task an.

Dort stellen wir dann folgendes ein:
Unter "Verarbeiten":

RequireFullFileName : false
Executable: bcp (findet man aber auch in C:\Program Files\Microsoft SQL Server\100\Tools\Binn -> bcp.exe)
WindowStyle: Hidden

Unter "Ausdrücke":
Expressions:
Eigenschaft: Argument
Ausdruck:

@[User::v_CurrentCatalogSchemaTableName] + " out " +  @[User::v_Export_Location] + "\\"+ REPLACE(  @[User::v_CurrentCatalogSchemaTableName] ,".","_") +  ".csv -q -h -c -t; -T -Slocalhost"

... hier könnte man natürlich noch mehr Parameterisieren... ;-)

Wenn wir nun unseren Datenflusstask starten sehen wir nach und nach wie die CSV Dateien im Verzeichnis:
c:\temp\export erscheinen

Gruß Tom

 

[Nico Graichen]

Danke Tom
Ist vielleicht in der Tutorials-Section gut aufgehoben!

 

[deathdragon]

Hallo Thomas,
erstmal danke für deine Beschreibung, eine Frage hätte ich aber trotzdem noch. Ich hätte es gern das jede CSV Datei mit einen Zeitstempel versehen ist. Das sollte doch über die Parameter bei out klappen aber ich weiß nicht wie?! ich kenne mich nicht aus mit den Parametern. Kannst du helfen? Ich würde gern den Zeitstempel im Scheduling verwänden sprich nach 2-3 Tagen das "Backup" löschen. Bis jetzt überschreibt er ja nur die Daten.

Wäre schön wenn du helfen könntest.