SQL Server & ASP .NET Blog

Interessantes und Wissenswertes

Konfiguration von SSIS-Paketen in SQL Server “Denali”

Wie bereits berichtet gibt es für den SQL Server “Denali” eine neue Projektart für SSIS-Projekte. Bisher wurden Pakete immer einzeln bereitgestellt und es gab dabei die Möglichkeit diese im Dateisystem oder in der msdb zu speichern. Konfigurieren konnte man die Pakete über Variablen, die per XML-Datei aus einer Datenbank oder per Umgebungsvariablen gesetzt wurden.

SSIS in SQL Server “Denali” bringt mit dem Project Deployment die SSISDB, Environments und Parameter ins Spiel. Dazu kurze Stichpunkte:

SSISDB – Zentrale Datenbank zum Speichern, Konfigurieren und für das Logging von SSIS Projekten und Paketen
Environment – Container für eine oder mehrere Variablen, die bei der Ausführung von Paketen gesetzt werden können
Parameter – im Grund eine weitere “Variablenart” in SSIS-Paketen, die beim Aufruf eines Paketes oder eines Projektes gesetzt werden kann

Im Grund funktioniert das Zusammenspiel folgendermaßen:

  1. Ein SSIS-Projekt wird angelegt und Parameter werden definiert
  2. Ein Environment mit einigen Variablen wird angelegt
  3. Das SSIS-Projekt wird auf den Server bereitgestellt (direkt aus Visual Studio in die SSISDB)
  4. bei der Ausführung (Execution) wird für das auszuführende Paket die zu verwendete Environment angegeben, die im Paket hinterlegten Parameter werden konfiguriert und das Paket läuft

Die folgende Grafik zeigt dieses Verhalten noch einmal grob:

image

Damit ist es ein Leichtes mehrere Umgebungen mit unterschiedlichen Parameterwerten für bspw. Entwicklungs-, Test- und Produktivumgebungen zu erstellen und während der Ausführung zwischen diesen umzuschalten.

So viel zur Theorie. In der Praxis sieht dieses Vorgehen folgendermaßen aus: Mehr...

SQL Server “Denali”–Neuheiten in SSIS

Ich habe heute mal testweise die CTP 1, SQL Server “Denali” installiert um ein wenig mit den neuen Features zu “spielen”. Vor allem angetan haben es mir dabei die neuen SSIS-Features – daher hier kurz das Berichtenswerte.

Die Optik

Zunächst einmal – ganz wichtig! Abgerundete Ecken :-) Die Elemente aus der Toolbox werden nun alle mit abgerundeten Ecken dargestellt und das aktuell gewählte Element bekommt einen schönen Schlagschatten. Sieht schick aus – mehr aber auch nicht :-)

image

Veränderungen in der Toolbox

Besonders viel Neues ist mir hier auf den ersten Blick nicht aufgefallen. Es gibt zwei neue “Favoriten”. Einen Destination Assistant und einen Source Assistant.

image

Wählt man einen der beiden aus kann in einem neuen Popup aus bereits vorhandenen Verbindungen ausgewählt werden oder man kann weitere Verbindungen hinzufügen.

image

Neuer Projekttyp und SSISDB

Standardmäßig werden neue Projekte nun in einem neuen SSIS-Projekttyp angelegt. Dieser neue Projekttyp erlaubt eine vollkommen neue Art der Paketkonfiguration und der Bereitstellung auf einem SQL-Server.

Zunächst einmal fällt im Management Studio ein neuer Zweig auf – “Integration Services”. Dort kann mit Create Catalog… eine neue Integration Services Datenbank (“SSISDB”) angelegt werden. Diese Datenbank kümmert sich um die Verwaltung der SSIS-Projekte und um deren Konfiguration.

image

Im Knoten Integration Services können nach Installation der Datenbank alle dort enthaltenen Projekte, Pakete und Konfigurationen bearbeitet werden. Für die Installation muss die Option ‘clr_enable’ aktiviert sein. Ansonsten gilt es nur ein Passwort zu vergeben und einmal OK zu klicken. Die Datenbank erscheint dann mit den neuen GUI Funktionen unterhalb von Integration Services und als “normale” Datenbank unterhalb Databases.

image

Im Screenshot sieht man ein bereitgestelltes SSIS-Projekt mit einem Paket und zwei erstellte Environments – also Ausführungsumgebungen.

Wie die “neue” Konfiguration funktioniert, was genau “Environments” sind und was man sonst so alles tolles mit dem neuen Projekttyp machen kann steht im nächsten Beitrag :-)

SSIS Paketvariablen per Konfigurationsdatei setzen

Im Visual Studio ist es ein leichtes für SSIS-Paketkonfiguration zu erstellen. Einen Überblick darüber gibt dieser Artikel.Nun ist es häufig so, dass die Entwicklungskonfiguration nicht mit der Konfiguration eines Live-Systems überein stimmt (andere Server, Datenbanknamen etc.). Daher ist es in SQL Server 2005/2008 möglich im SQL Server Agent dem Paket eine andere Konfigurationsdatei mitzugeben.

image

Wird hier eine Konfigurationsdatei angegeben, dann werden die Werte aus dieser beim Start des Paketes ausgelesen und auf das Paket angewendet. Also kann ich davon ausgehen, dass vorhandene Entwicklungs-Konfigurationen überschrieben werden… JEIN! Bis SQL Server 2005 stimmt diese Annahme - ab SQL Server 2008 wird anders mit den Konfigurationsdateien umgegangen.

Ab SQL Server 2008 werden zunächst die Werte aus der Entwicklungsumgebung geladen, danach die Konfigurationsdatei geladen – deren Werte gesetzt und danach werden wieder die Werte aus der Entwicklungsumgebung verwendet um diese wieder zu überschreiben. Bedeutet also: Egal welche Konfiguration in der Datei steht – wenn es eine (Konfigurations-)Einstellung in der Entwicklung gab, wird diese zur Laufzeit verwendet… Dieses Verhalten ist hier nachzulesen: http://msdn.microsoft.com/en-us/library/bb500430.aspx

Frage: Wozu dann Konfigurationsdateien?
Antwort: Um zusätzliche Konfigurationen für die Laufzeit vorzunehmen. Ist im Paket für eine Eigenschaft keine Konfiguration vorgesehen, kann diese per Konfigurationsdatei gesetzt werden.

Frage: Wie können dann Konfigurationen für das Überschreiben von Entwicklungskonfigurationen gesetzt werden?
Antwort: Per “SET” Option, also bspw. /SET \Package.Variables[User::fileName].Properties[Value]; siehe auch hier

SQLCE und ADO .NET Entity Framework

Möchte man das das ADO .NET Entity Framework und den SQL Server Compact in einer Anwendung zusammen verwenden gibt es einen interessanten Punkt zu beachten – keine IDENTITY Columns in der Datenbank verwenden. Durch die Einschränkung des SQL CE in einem Batch nur einen Befehl ausführen zu können ist es für das Entity Framework bei einem INSERT unmöglich festzustellen, welches der neue IDENTITY Wert ist – daher werden diese Spalten nicht unterstützt.

Einfache Lösung: Selbst erstellen des Schlüssels in der Anwendung. Um zu vermeiden hier auch erst wieder eine MAX(Id) herausfinden zu müssen bieten sich hier auch GUIDs (bzw. uniqueidentifier) an. Beim Erzeugen eines Objektes also einfach:

myObject.myId = Guid.NewGuid();

…dann ist auch das Entity Framework zufrieden.

Dazu noch ein Link (da ich meine Spalten vorher natürlich als INT IDENTITY erstellt habe ;-)) mit dem Opensource-Tool bzw. SSMS Addin http://exportsqlce.codeplex.com/ ist es ein leichtes die SQL CE Datenbank komplett inkl. Daten zu skripten.

SQL Server Version auslesen – Teil 2

Im letzten Beitrag SQL Server Version auslesen habe ich beschrieben, wie die Version des SQL Server ausgelesen werden kann. In den Kommentaren wurde gefragt, wie man nun vom Ergebnis von

EXEC xp_msver

…die so aussehen:

image

…auf einen einzelnen Wert, zum Beispiel die “ProductVersion” kommt.

Hier kann man genau so vorgehen, wie bei jeder anderen Prozedur aus der die zurückgegebenen Daten weiterverarbeitet werden sollen. Dazu benötigt man eine Tabelle, die exakt den selben Aufbau, wie das Ergebnis der Prozedur hat. In diese Tabelle können dann die Ergebnisse per INSERT INTO eingefügt werden. Danach kann mit den Werten aus der Tabelle weitergearbeitet werden und so z.B. hier die Produktversion ausgelesen werden.

Ergebnis wäre dann folgendes SQL-Skript:

-- temp Table
DECLARE @temp TABLE 
(
  [Index] int,
  Name VARCHAR(100),
  Internal_Value INT,
  Character_Value VARCHAR(1000)
)  

-- Ergebnis aus xp_msver speichern
INSERT INTO @temp
EXEC xp_msver

-- abfragen
select Character_Value from @temp
where Name = 'ProductVersion'
 

Und als Wert erhalte ich nur:

image

…und kann den Wert weiter verwenden.

Standard Verzeichnisse im SQL Server

Der SQL Server besitzt in seiner Konfiguration Standard-Verzeichnisse für die Daten-Dateien, Log-Dateien und auch für Backups. Werden diese in einem Skript benötigt, können diese folgendermaßen ausgelesen werden:

-- SQL Data Root
DECLARE @SQLDataRoot nvarchar(512) 
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\Setup'
,N'SQLDataRoot',@SQLDataRoot OUTPUT

SELECT
@SQLDataRoot SQLDataRoot -- Standard-Datenverzeichnis DECLARE @DefaultData nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'DefaultData',@DefaultData OUTPUT
select
@DefaultData DefaultData -- Standard-Logfile-Verzeichnis DECLARE @DefaultLog nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog'
,@DefaultLog OUTPUT
select
@DefaultLog DefaultLog -- Standard Backup-Verzeichnis DECLARE @DefaultBackup nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory'
,@DefaultBackup OUTPUT
select
@DefaultBackup DefaultBackup

Sollte eines der Verzeichnisse nicht explizit gesetzt sein (sondern in der Standardeinstellung, wie bei Installation belassen), dann wird folgende Meldung zurückgegeben:

RegQueryValueEx() returned error 2, 'Das System kann die angegebene Datei nicht finden.'
Msg 22001, Level 1, State 1

Die Standardwerte für nicht explizit festgelegte Verzeichnisse sind:

  • DefaultData => SQLDataRoot + “\DATA”
  • DefaultLog => SQLDataRoot + “\LOG”
  • DefaultBackup => SQLDataRoot + “\Backup”

Nicht nur auslesen, auch Setzen kann man diese Werte per Skript (außer das SQLDataRoot):

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'C:\data' GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'C:\Log' GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, N'C:\Backup'

SQL Server Version auslesen

Um schnell mal auszulesen, welche Version des SQL Server eigentlich installiert ist gibt es mehrere Möglichkeiten. Einerseits (und am schnellsten getippt) ist das Statement:

SELECT @@VERSION

image

Andererseits kann man die Abfrage auch über die Produkteigenschaften machen:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

…gibt als Ergebnis die einzelnen Werte zurück:

image

Die letzte (hier beschriebene Möglichkeit) ist eine interne Prozedur, die noch einiges mehr an Zusatzinformationen zum installierten SQL Server und auch zur Windows-Version etc zurückgibt:

EXEC xp_msver

…Ergebnis:

image

Hier geht es zu Teil 2 des Artikels: Teil 2

SQL CLR Integration – Test

Eine kurze Erweiterung zum letzten Artikel: siehe [hier]

Möchte ich die erstellte Funktion testen und debuggen, dann kann ich das auch direkt aus dem Visual Studio heraus. Dazu gibt es im erstellten Projekt eine bereits vorhandene Test.sql, die dazu dienen soll eigene Funktionen etc. auszuführen, zu testen und zu debuggen.

image

Mit Rechtsklick auf die Test.sql und “Set as default debug script” wird dieses Skript immer beim Debuggen ausgeführt.

image

Nun kann ich im Skript meine Funktion ausführen und ein paar Tests durchführen. Beispiel:

IF dbo.fDNSCheck('MyDNSName') = 1
PRINT 'OK'
IF dbo.fDNSCheck('NotMyDNSName') = 0
PRINT 'OK'  

Dazu muss auf dem SQL Server allerdings der Remote Debugger installiert sein (msvsmon.exe). Weitere Informationen dazu gibt es [hier]Außerdem müssen einige Berechtigungen als Voraussetzung vorhanden sein (siehe ebenso der Link). Sind alle Voraussetzungen vorhanden, dann kann per F5 das Skript gestartet werden, es können Haltepunkte im .NET Code gesetzt werden etc.

SQL CLR Integration - .NET Code im SQL Server

Der SQL Server bietet die Möglichkeit CLR-Code direkt auszuführen und somit erweiterte Funktionen zu erfüllen. Ein kleines Beispiel: Ich möchte prüfen, ob mein SQL Server auf einen bestimmten DNS-Namen im Netzwerk hört. Dazu ist mir mit Bordmitteln keine Möglichkeit bekannt.

Zunächst einmal muss die CLR-Integration aktiviert werden.

sp_configure 'clr_enable', 1
RECONFIGURE

Ergebnis ist:

Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

Jetzt können wir loslegen. Ich erstelle im Visual Studio ein neues Datenbankprojekt und nenne es DNSChecker:

image

Im darauf folgenden Dialog muss eine Verbindung zu einer Datenbank hergestellt werden (die Datenbank, in der die Assembly letztendlich liegen soll und aus der sie ausgeführt wird).

image

Nachdem die Verbindung eingetragen ist, habe ich über einen Rechtsklick auf das Projekt und “Add” die Möglichkeit verschiedene Objekte zu erstellen:

image

Die einzelnen Möglichkeiten sind:

  • User-Defined Function – Eine Funktion mit Eingabeparametern und einem Ausgabeparameter
  • Stored Procedure – Ähnlich einer T-SQL Prozedur
  • Aggregate – Eine Aggregatfunktion, wie SUM(), AVG() etc.
  • Trigger – Ein CLR Trigger
  • User-Defined Type – Ein eigener “Datentyp”

Für das Beispiel beschränke ich mich auf das Erstellen einer Funktion, die mir nach Eingabe eines DNS-Namens zurückgibt, ob mein Server auf diesen Namen hört, oder nicht. Also => User-Defined Function

Nach Erstellen der Funktion erhalte ich einen bereits funktionsfähigen Rumpf mit einer Art “Hello World”.

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString fDNSCheck()
{
// Put your code here
return new SqlString("Hello");
}
};

Würde ich die Funktion nun aufrufen würde ich ganz einfach “Hello” als Ergebnis erhalten. Ich möchte allerdings “wahr” oder “falsch” als Bit-Wert zurückgeben und benötige als Eingabeparameter noch einen string (den DNS-Namen)

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean fDNSCheck(string sDnsName)
{
// Put your code here
return new SqlBoolean(false);
}
};

 

 

Nun fehlt noch die eigentliche Überprüfung, ob der Server auf den Namen hört.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean fDNSCheck(string sDnsName)
{
bool result = false;
try
{
//Get all ips for DNS-Name
IPHostEntry ip = Dns.GetHostEntry(sDnsName);
IPAddress[] IpA = ip.AddressList;
//check if a local ip for this name exists
foreach (IPAddress ipDNS in IpA)
{
foreach (IPAddress ipLocal in Dns.GetHostEntry(Dns.GetHostName()).AddressList)
{
if (ipDNS.ToString() == ipLocal.ToString())
{
result = true;
}
}
}
}
catch (Exception ex)
{
result = false;
}
return new SqlBoolean(result);
}

Dazu iteriere ich über alle für den DNS-Namen gefundenen IP-Adressen und vergleiche mit allen auf dem Server gefundenen IP-Adressen. Wird eine Übereinstimmung gefunden wird “true” zurück gegeben. Nun ist die Funktion fertig und könnte arbeiten. Da die Funktion allerdings die Berechtigungsstufe “unsafe” benötigt (näheres siehe unter http://technet.microsoft.com/de-de/library/ms131071.aspx) muss die Datenbank in der diese laufen soll die Eigenschaft “TRUSTWORTHY ON” haben. Also…

ALTER DATABASE CLRTestDB SET TRUSTWORTHY ON

…und für die Assembly muss eingestellt werden, dass der Zugriff in der Stufe “unsafe” erfolgen soll (in den Projekteigenschaften).

image

Nun kann ich die Assembly per “Deploy” auf dem Server installieren. Führe ich die Funktion nun aus und mein Server hört z.B. auf den Namen “MyDNSName”, dann gibt die Funktion “1” für “wahr” zurück.

image

Dieses kleine Beispiel hat gezeigt, wie einfach es ist den SQL Server mit eigenen .NET Funktionen zu erweitern.

ASP .NET - Bild aus Datenbank abrufen und anzeigen

In einem anderem Beitrag [hier]habe ich bereits beschrieben, wie einfach es ist, ein Bild auf einer Webseite hochzuladen und mit wenigen Zeilen C#-Code in einer SQL Server Datenbank zu speichern. Interessant ist natürlich auch der Rückweg: Also wie kommt das Bild wieder aus der Datenbank heraus und kann im Browser angezeigt werden?

Auch das geht wieder in einfachen Schritten.

Schritt 1 – Eine ASPX-Seite zur Ausgabe eines Bildes

Um ein Bild aus der Datenbank zu lesen und im Browser anzuzeigen, erstelle ich eine neue Seite “GetPicture.aspx”. Diese Seite hat keinen eigentlichen Inhalt, sondern nur den Zweck ein Bild anhand des Namens (oder eines beliebigen anderen Parameters, z. B. einer ID) aus der Datenbank zu holen und an den Browser zu senden.

protected void Page_Load(object sender, EventArgs e)
{
//Variable für die Bilddaten
Byte[] myFile;
//der Dateiname aus dem QueryString
string myFileName = Request.QueryString["FileName"];
if (myFileName != null)
{
//Verbindung zum Server
SqlConnection con = 
new SqlConnection("Data Source=192.168.1.13;Initial Catalog=BildDB;User ID=xxx;Password=xxx");
//Insert Statement mit den entsprechenden Parametern
SqlCommand cmd = new SqlCommand("SELECT picture FROM pictures WHERE name = @name", con);
//Parameter für den Bildnamen
SqlParameter paramName = new SqlParameter("@name", System.Data.SqlDbType.VarChar);
paramName.Value = myFileName;
cmd.Parameters.Add(paramName);
//Daten lesen
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read() ) //im Beispiel interessiert nur der erste Satz
{
myFile = (Byte[])reader["picture"];
Response.ContentType = "image/jpeg"; //ContentType setzen
Response.BinaryWrite(myFile);        //Bild ausgeben
}
con.Close();
}      

Ruft man nun diese Seite mit einem in der Datenbank vorhandenen Bildnamen auf, dann wird das Bild direkt im Browser angezeigt.

image

Schritt 2 – Bilder in einem Gridview anzeigen

Um nun mehrere Bilder aus der Datenbank anzuzeigen kann bspw. ein GridView verwendet werden. Auf einer zusätzlichen Seite “Anzeigen.aspx” habe ich eine neue SQLDatasource und ein GridView erstellt.

        <!-- Die Datenquelle -->
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:BildDBConnectionString %>" 
SelectCommand="SELECT picture, name, addedat FROM [pictures]">
</asp:SqlDataSource>
<!-- Anzeige im Gridview -->
<asp:GridView ID="GridView1" runat="server" 
DataSourceID="SqlDataSource1" 
AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
<asp:BoundField DataField="addedat" HeaderText="addedat" 
SortExpression="addedat" />
<asp:ImageField DataImageUrlField="name" 
DataImageUrlFormatString="~/GetPicture.aspx?Filename={0}">
</asp:ImageField>
</Columns>
</asp:GridView>

Das “Besondere” an diesem GridView ist nun, dass es ein ImageField hat, welches direkt die in Schritt 1 erstellte ASPX-Seite aufruft und den aus der Datenbank gelieferten Dateinamen übergibt.

Das Endergebnis sieht dann so aus:

image 

Das Projekt (Visual Studio 2010) hängt zum Anschauen direkt hier am Beitrag. Fragen sind natürlich immer gern gesehen ;)

Bildverwaltung.zip (32,13 kb)