Remote afvikling af SSIS-pakker

Hvis man vil afvikle SSIS-pakker på en server “on-demand”, findes der forskellige mere eller mindre besværlige løsninger. I dette blog-indlæg skal vi se på en lille konsolapplikation, DtExecRemote, skrevet i c#/.NET 4.0. Applikationen er et godt eksempel på hvordan man kan bruge SQL Server Management Objects (SMO) til at udføre administrative opgaver på en SQL Server. Helt konkret skal vi se på hvordan man opretter et SQL Server Agent Job, tilføjer et step til afvikling af en SSIS-pakke, kører jobbet og sletter det igen. DtExecRemote er indrettet sådan at alle kommandolinjeparametre bliver videreført til DTEXEC, så man bl.a. kan sætte værdien af variable, konfigurationsstrenge, osv. fuldstændig som hvis man afviklede DTEXEC lokalt fra en kommandoprompt. DtExecRemote kan afvikles på en hvilket som helst PC hvorfra der kan oprettes forbindelse til en server med SSIS, og derfor kræver DtExecRemote ikke en lokal SQL Server installation.

Hvorfor afvikle SSIS-pakker remote?

Jeg fik en opgave fra en kunde, der skulle migrere nogle budgetter fra et ældre system, baseret på en Access database, til et nyere system. Det nye system tillod at man kunne uploade budgetter i et særligt CSV-format. Det tog ikke mange minutter at bygge en SSIS-pakke der kunne trække data ud fra Access databasen, transformere dem på passende vis og spytte tallene ud i det påkrævede CSV-format. Det tog heller ikke mange minutter at køre pakken for at migrere de eksisterende data. 3 måneder senere kontakter den samme kunde mig igen – man havde besluttet at blive ved med at bruge Access databasen til budgetindtastning, men tallene skulle fortsat læses ind i det nye budgetsystem. Den bedste løsning måtte dermed være, at min kunde selv kunne køre SSIS-pakken, efterhånden som nye tal blev indtastet i Access databasen. Men for at afvikle en SSIS-pakke lokalt, kræves en minimumsinstallation af SQL Server Integration Services – med hvad dertil hører af komplicerede licensregler, osv. Det var heller ikke praktisk at give kunden Remote Desktop adgang til vores server, hvorfra jeg selv havde afviklet SSIS-pakken. Hvad pokker skulle man så gøre?

SQL Server Management Objects (SMO) to the rescue…

En hurtig søgning på nettet afslørede at der ikke er nogen standardmåde at afvikle SSIS-pakker remote. Der var dog mange fantasifulde og mere eller mindre praktiske løsninger:

  • Sæt en webserver op på den maskine der har SSIS installeret og lav et websted hvorfra kunden kan afvikle pakken.
  • Benyt SQL Serverens xp_cmd_shell til at afvikle DTEXEC.exe på serveren.
  • Opret et SQL Server Agent Job der afvikler pakken og benyt SQL Serverens sp_start_job til at køre jobbet.

Sidstnævnte inspirerede os til at skrive en lille konsolapplikation i c#, der vha. SMO tager sig af følgende:

  1. Opret et SQL Server Agent Job
  2. Indsæt et step i jobbet, der afvikler SSIS-pakken med samme kommandolinjeparametre som konsolapplikationen afvikles med.
  3. Kør jobbet.
  4. Slet jobbet.

SMO er et .NET library der gør det meget let at udføre de opgaver som man normalt benytter SQL Server Management Studio til. Som kildekoden til DtExecRemote viser, skal der kun bruges ganske få linjers kode til at oprette et job, indsætte et step, køre jobbet, osv.

DTEXEC kommandolinjeparametre

Da budgetterne var delt op på forskellige afdelinger, var det et krav at kunden selv kunne angive hvilken afdeling der skulle dannes en CSV-fil for. I SSIS-pakken var afdelingsnumre specificeret i en variabel, og det var derfor vigtigt at værdien af denne variabel kunne angives inden pakken blev kørt. DTEXEC giver mulighed for at benytte kommondalinjeparameteren /set til at sætte værdien af samtlige properties på pakkens tasks og komponenter, ikke mindst værdien af variabler. Har man f.eks. en variabel ved navn “OutputFile”, kan værdien af denne sættes på følgende vis:

dtexec /F "D:\SSIS\MyPackage.dtsx" /Set \package.variables[User::OutputFile].Value;"D:\output.csv"

Dermed er det selvfølgelig oplagt at lade DtExecRemote tage imod kommandolinjeparametre, der sendes videre til DTEXEC.

DtExecRemote

Kildekoden og en kompileret udgave af DtExecRemote er vedhæftet dette indlæg. Lad os se nærmere på den del af DtExecRemote der har med SMO at gøre:

Først oprettes en forbindelse til SQL Serveren på sædvanlig vis (linje 2 – 4). Herefter oprettes der en SMO forbindelse (linje 7 – 8) og SMO objekterne oprettes (linje 11 – 12). I linje 15 genereres der et nyt, unikt navn som i linje 16 bliver tildelt det nye SQL Server Agent job. Jobbet oprettes og tildeles den lokale jobserver i linje 20-21. Der oprettes et nyt step i linje 24-31. Variablen command indeholder de kommandolinjeparametre som DtExecRemote er blevet kaldt med.

Endelig startes jobbet i linje 34. Løkken i linje 38-42 afventer at jobbet afsluttes, og i linje 44-53 outputtes bl.a. jobbets udfald (“Succeeded” eller “Failed”) samt xml-outputtet fra DTEXEC. Endelig slettes jobbet i linje 60. That’s all folks!

Afvikling af DtExecRemote

For at gøre det så nemt som muligt for vores kunde, lavede jeg til sidst en kommandoscriptfil (.cmd), som afvikler DtExecRemote (og dermed DTEXEC) med de rette parametre. En sådan .cmd-fil kunne f.eks. se således ud:

Nu var alt næsten flyvende! Det eneste der manglede var at installere nogle små filer på min kundes PC, for at sætte maskinen i stand til at bruge SMO. Det drejede sig om følgende to filer fra SQL Server 2008 R2 Feature Pack:

Man kunne muligvis inkludere de pågældnede .dll-filer i DtExecRemote, som en mere standalone-løsning, men det havde jeg ikke held med i skrivende stund.

Download kildekode til DtExecRemote (Visual Studio 2010 projekt, 5 KB .zip)

Download eksekverbar version af DtExecRemote (9 KB .zip, kræver SQL CLR Types samt SMO)

Daniel Otykier
Member since November 20, 2016

Comments

    Your email address will not be published. Required fields are marked *