Denne opgave var egentlig relativt simpel. Download fra sFTP site, hent filer i 3 mapper, som skifter navn hver måned i format /yyyymm/ og importer det i en tabel.
Lige bortset fra at det er sFTP, så er det jo lige til. Jeg har tidligere lavet en næsten tilsvarende løsning hvor jeg selv skrev sFTP komponenten til SSIS, men ville gerne bruge noget mere ”standard” til denne opgave. Så jeg valgte at bruge PuTTY
Her er det ret lige til, der skal bare køres en .exe file med en stribe parametre, hvor et af dem er en bat file som fortæller hvilke filer, der skal downloades. Bat filen kan jeg så generere via powershell, så stierne bliver genereret dynamisk baseret på datoen for kørslen.
Den samlede pakke ser sådan her ud:
Først slettes temp mappen (datadump)
Så laver jeg en batfile (da stierne til filen skifter hver måned)
Herefter kaldes PuTTY for at downloade filerne med parametre
Da data ikke skal gemmes fra tidligere, så tømmes tabellerne før import
Til sidst importeres filerne ind i de 3 tabeller det handler om
Den interessante del er nu PuTTY download:
Som er en Execute Process Task og den ser sådan her ud:
PuTTY filen
PuTTY filen (psftp.exe) bliver kaldt med disse parametre (Arguments):/C C:\unit_it\psFTP.exe bruger@documentation.site.important.com -pw DetteErIkkeDetRigtigePassword -be -batch -b C:\unit_it\DataDump\download_files.bat.
Og det spiller bare. Man skal dog lige huske at connecte til sFTP sitet en gang fra serveren så der udveksles nøgler mellem SQL Server og sFTP sitet – ellers vil det fejle. Pakken tester jeg lokalt og det fungerer som forventet.
Derefter deployes den til SQL Serveren. Da pakken er afhængig af en bestemt file struktur, så logger jeg på serveren og opretter de mapper der skal være, giver læse/skrive rettigheder til mapperne og hopper så tilbage i SSMS for at sætte SQL Agent jobbet op.
Jeg kører pakken først fra Integration service, det går fint. Derefter afvikler jeg jobbet via SQL agenten her går det også fint.
Jeg opretter en proxy og giver den rettigheder på serveren og sætter den til at afvikle SQL agentjobbet. Jeg tester pakken ved at køre agentjobbet, og så er det at det begynder at blive spændende; for jobbet fejler med denne besked (beskeden er skåret noget ned):
Error execution Putty download: …. At ”C:\unit_it\datadump\” the process exit code was “1” while expecting “0” ..
Det fik mig til at tænke på at det måtte være noget med rettigheder at gøre. Så for at be- eller afkræfte det, blev brugeren (Service acounten) gjort til administrator på serveren (nb: vil aldrig anbefale at det er en permanent løsning, men praktisk i forbindelse med fejlsøgning. Giv kun brugeren de nødvendige rettigheder) det gav dog ingen ændring, jobbet fejler stadig med samme besked som før.
Login på serveren
Næste step er så at logge på serveren, som brugeren og så derfra forsøge at køre de relevante exefiler for at at se hvilken fejl der returneres.
Efter at jeg var logget på som brugeren (som jo er administrator på dette tidspunkt) forsøger jeg at køre processen og det må jeg gerne, filerne skrives i de rigtige mapper, dog beder PuTTY mig om at gemme en ny nøgle – så jeg tænker at den nok er bruger-afhængig, men nu er filen hentet, så nu må det være fint.
Herefter skifter jeg til min egen maskine og via SSMS afvikler jeg agent jobbet igen som service brugeren – og nu kører det igennem uden fejl. Så jeg konkluderer at det må have været nøglen, der ikke eksisterede for service brugeren. Jeg logger brugeren af SQL Serveren og igen fra min egen maskine kører jeg agentjobbet, og til min store overraskelse er den selv samme fejl tilbage.
Så starter en større og ret frustrerende fejlsøgning, for det bliver hurtigt tydeligt at hvis brugeren er logget på serveren og jeg kører jobbet som brugeren, så virker alt fint, men når brugeren ikke er logget på, så kommer fejlen tilbage. Jeg kigger i Windows loggen, men er er ingen logninger af fejlen, så alt jeg har at arbejde med er den fejl jeg kan se i rapporten over SSIS jobbets eksekveringer.
Jeg sætter loggning op i SSIS pakken i håb om at kunne fange et hint om hvad fejlen er, men får også her nøjagtig den samme fejl når brugeren ikke er logget på. Altså denne:
Error execution Putty download: …. At ”C:\unit_it\datadump\” the process exit code was “1” while expecting “0” ..
Efter mange forsøg finder jeg ved et tilfælde et hint om at outputte kommandopromptens resultater i en tekstfile. Dette gøres simpelt ved at tilføje > c:\unit_id\log.txt på argument stringen
/C C:\unit_it\psFTP.exe bruger@documentation.site.important.com -pw DetteErIkkeDetRigtigePassword -be -batch -b C:\unit_it\DataDump\download_files.bat > c:\unit_id\log.txtLettere optimistisk kører jeg pakken igen, får fejlen og logger på serveren for at kigge om der ligger en log file, hvilket der gør:
Jeg åbner filen..
..Som er tom *ARHH!*
Lettere frustreret kigger jeg videre på nettet og finder et andet hint .. nemlig at tage argument stringen og køre den alene som en CmdExec fra SQL agenten i stedet for at køre hele SSIS pakken.
Det retter jeg til.
Igen afvikler jeg jobbet, som helt som forventet fejler, MEN det der sker nu er, at der kommer en anden fejl som jeg kan finde inde under agentjobbet.
Det var en helt anden besked end hvad jeg får retur når jeg kører SSIS pakken.
Denne besked der kommer op her skyldes at nøglen ikke er cached på serveren og da PuTTY kører med -batch switch så bliver vi ikke promptet et spørgsmål om nøglen skal caches, hvilket gør at eksekveringen afbrydes og intet sker, alt der kræves er at fjerne -batch switchen, så er vi i ”interactive mode” og bliver promptet om vi vil cache nøglen og det kan vi svare på direkte fra argumentstringen, men først skal der oprettes en file som kun indeholder et y:
Og så skal argument stringen ændres: -batch switchen skal fjernes og så skal der < c:\unit_it\answer.txt på i enden:
/C C:\unit_it\psFTP.exe bruger@documentation.site.important.com -pw DetteErIkkeDetRigtigePassword -be -b C:\unit_it\DataDump\download_files.bat < c:\unit_it\answer.txt
Jeg deployer pakken og afvikler igen agentjobbet (uden at brugeren er logget på) og se nu der, det hele afvikler som ønsket 😊
Så det jeg endnu engang blev mindet om, er at SSIS ikke altid kommer med den helt rigtige fejlbesked, og at når man arbejder med exe filer fra SSIS, der fejler så kør dem som CMD fra SQL agenten, det gav i mit tilfælde en væsentlig bedre fejlbesked. 😊
Tak fordi du læste med. Som altid så er du velkommen til at kontakte mig eller en af mine dygtige kollegaer i Unit IT på tlf.: 88 333 333, hvis du har spørgsmål eller udfordringer med dit SQL miljø.
