Recent heeft Growteq een webinar gegeven waarin we stapsgewijs uitleggen hoe je data uit AFAS kan inladen in Power BI.

Bekijk het webinar hier


In de eerste blog van deze serie (Gebruik Getconnectoren uit AFAS – part 1) zijn de eerste twee stappen beschreven om een Getconnector van AFAS aan te roepen. Op basis van deze stappen hebben we een output bestand opgehaald via de connectie.

In dit tweede deel worden de laatste twee stappen in het proces beschreven met als uitkomst dat de gegevens in de database worden weggeschreven.

Stappenplan
Stap 1: Gegevens AFAS getconnector verzamelen
Stap 2: Connectie AFAS getconnector
Stap 3: Bewerken output getconnector
Stap 4: Wegschrijven data in database

Stap 3: Bewerken output getconnector

Klik met de rechtermuisknop op de Web Service Task in de Control Flow en kies voor Execute Task:

De getconnector wordt nu aangeroepen de resultaten worden weggeschreven naar het output bestand (In dit voorbeeld c:afasoutput.xml).

Als alles goed gaat, zie je een groen vinkje boven de Web Service Task verschijnen:

Als we het output bestand bekijken, zien we dat in de output verschillende tekens zijn vervangen door zogenaamde HTML Entity Names. Het groter dan teken (>) is bijvoorbeeld vervangen door > 

 SSDT kan met deze tekens niet omgaan en we zullen deze tekens dan ook eerst moeten vervangen.

Start met het aanmaken van een variabel met de naam varFilePath, die verwijst naar het outputbestand:

 

Sleep nu een Script Task vanuit de SSIS Toolbox naar de Control Flow. En verbind de groene pijl van de Web Service Task met de Script Task.

Kies als ScriptLanguage voor Visual C# 2012 en kies onder de ReadWriteVariables voor de zojuist aangemaakte variabele:

Open het Script door op Edit Script te klikken.

Zorg dat onder de region NameSpaces de volgende namespaces zijn opgenomen:

Neem onder public void Main() de volgende code op:

[code language=”csharp”] String ErrInfo = "";
String FilePath = Dts.Variables["User::varFilePath"].Value.ToString();
try
{
String FileContent; //Variable to store File Contents
FileContent = ReadFile(FilePath, ErrInfo);
if (ErrInfo.Length > 0)
{
Dts.Log("Error while reading File " + FilePath, 0, null);
Dts.Log(ErrInfo, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}

FileContent = FileContent.Replace("<", "<"); FileContent = FileContent.Replace(">", ">");
FileContent = FileContent.Replace("&", "&");
FileContent = FileContent.Replace("encoding="utf-16"", string.Empty);

FileContent = FileContent.Replace("<string>",string.Empty);
FileContent = FileContent.Replace("</string>", string.Empty);

WriteToFile(FilePath, FileContent, ErrInfo);
if (ErrInfo.Length > 0)
{
Dts.Log("Error while writing File " + FilePath, 0, null);
Dts.Log(ErrInfo, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
}
catch (Exception e)
{
Dts.Log(e.Message, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}

public String ReadFile(String FilePath, String ErrInfo)
{
String strContents;
StreamReader sReader;
try
{
sReader = File.OpenText(FilePath);
strContents = sReader.ReadToEnd();
sReader.Close();
return strContents;
}
catch (Exception e)
{
MessageBox.Show(ErrInfo);
ErrInfo = e.Message;
return "";
}
}

public void WriteToFile(String FilePath, String strContents, String ErrInfo)
{
StreamWriter sWriter;
try
{
sWriter = new StreamWriter(FilePath);
sWriter.Write(strContents);
sWriter.Close();
}
catch (Exception e)
{
MessageBox.Show(ErrInfo);
ErrInfo = e.Message;
}
}
[/code]

Download het script door op de volgende link te kliken: afas script.

Voer de Script Task uit door er met de Rechtermuisknop op te klikken en te kiezen voor Execute Task:

Als de Script task succesvol is voltooid zijn alle “gekke” tekens vervangen.

 

Stap 4: Wegschrijven data in database

Sleep een Data Flow Task naar de Control Flow.

Open de data flow task door dubbel te klikken.

Sleep nu vanuit de SSIS Tool box een XML Source naar de Data Flow.

Open de XML Source Editor en kies bij de XML location voor het output bestand uit de connector.

Klik op Generate XSD om een definitie bestand voor het XML document te maken.

Als je op de tab Columns klikt krijg je een overzicht van alle beschikbare kolommen in het XML bestand. De waarschuwingsmelding van SSDT omtrent de lengte van de velden kun je negeren door op OK te klikken.

Sleep nu een OLE DB Destination naar de Data Flow en verbind de blauwe pijl vanuit de XML Source hiermee.

Open de OLE DB Destination Editor en maak een nieuwe OLE DB Connection Manager aan door op New te klikken.

Maar verbinding met een SQL database waar de gegevens in moeten worden weggeschreven:

Maak een Nieuwe tabel aan in de OLE DB Destionation Editor door op new te klikken.

Klik vervolgens op het tabje Mappings om de kolommen en de tabel op elkaar aan te sluiten.

Ga nu terug nar de Control Flow en klik met de rechtermuisknop op de Data Flow Task en kies voor Execute Task. De gegevens uit het XML bestand worden nu uitgelezen en weggeschreven naar de database.

Als je de Data Flow Task opent zie je zelfs hoeveel regels er zijn verwerkt:

Afsluiting

In bovenstaand stappenplan is beschreven hoe de gegevens vanuit een AFAS getconnector kan worden uitgelezen en de gegevens kunnen worden weggeschreven naar een database voor verdere verwerking en analyse.

Een eerste stap voor het ontginnen van de goudmijn is gelegd.

Veel succes.


Recent heeft Growteq een webinar gegeven waarin we stapsgewijs uitleggen hoe je data uit AFAS kan inladen in Power BI.

Bekijk het webinar hier

Reacties

24 reacties. Reactie plaatsen

  • Richard van Schie
    4 juli 2016 12:56

    Super artikel. Precies wat ik zocht. Na veel puzzelen bleef de XML waardeloos. Nu werkt het.
    Overigens is in je code het groter dan teken en het kleiner dan teken ook gescrambled, maar dat had ik snel genoeg gevonden.

    Beantwoorden
  • J. carpentier
    24 november 2016 15:44

    Mooi Artikel! Ik loop alleen nog een beetje vast op de code. Als ik deze er in plak, ontstaan er scriptfouten, helaas.. Ik probeer deze op te lossen maar helaas:

    Problemen ontstaan in de regels:
    if (ErrInfo.Length > 0)
    en
    FileContent = FileContent.Replace(“encoding=”utf-16″”, string.Empty);

    Hopelijk heeft iemand een oplossing hiervoor. Ik werk verder met dezelfde versies visual studio.

    Beantwoorden
    • Gideon Folkers
      24 november 2016 16:52

      Beste J.Carpentier,

      M.b.t. de FIleContent: dit zou te maken kunnen hebben met de ” tekens.
      Het script zal deze namelijk interpreteren als afsluiting van code.
      Je kunt proberen om er een \ voor te zetten:
      FileContent = FileContent.Replace(“encoding=\”utf-16\″”, string.Empty);

      Hopelijk werkt dat wel.

      Het zou kunnen dat het andere probleem m.b.t. de ErrInfo.Length ook is opgelost.

      Beantwoorden
      • J. carpentier
        24 november 2016 16:58

        Beste Gideon,

        Bedankt voor je reactie! De regel FileContent = FileContent.Replace(“encoding=\”utf-16\″”, string.Empty);
        is dan ook opgelost, erg fijn. Als ik de code uitvoer, dan zie ik ook een goed resultaat in de XML. Helaas lijkt ook het verwijderen van < en het vervangen van de < en > niet te werken. Hopelijk heeft u daar nog een kleine tip voor! Ontzettend bedankt, dan ben ik er bijna 🙂

        Beantwoorden
        • Gideon Folkers
          24 november 2016 17:04

          Hi,

          Sorry, ik begrijp het nog niet helemaal waar het mis gaat. Kan je misschien een stukje code plaatsen wat niet doet wat het moet doen?

          Ik zie trouwens in de code hier boven iets staan wat volgens mij niet elemaal goed is. Voor het vervangen van < in een > teken heb je de volgende code nodig:

          FileContent = FileContent.Replace(“<“, “<"); Helpt dat?

          Beantwoorden
  • Hoi Gideon,
    Geweldig blog, dank!
    Ik loop ook een beetje vast op het script. Heb de bovenstaande oplossingen meegenomen, maar bij mij zijn de eerste “” al rood (String ErrInfo = “”;). Heb jij een idee?
    Heb je stappenplan nauwkeurig gevolgd.

    Beantwoorden
    • Gideon Folkers
      23 december 2016 10:32

      Beste SakeC,
      Bedankt voor jouw reactie.

      Als de eerste String ErrInfo al rood zijn, dan is dat wel apart.
      Een paar ideeën:
      – Heb je wel een c# script aangemaakt en geen Visual basic script?
      – Heb je onder Names spaces de verschillende usings opgenomen?
      – kan het zijn dat je per ongeluk teveel van de standaard code die SSIS aanmaakt hebt weggeooid? Bovenstaande code moet gelijk onder public void Main()

      Kan je hier iets mee?

      Beantwoorden
      • J.Carpentier
        28 december 2016 10:49

        Hoi Gideon,

        Ik loop er helaas ook nog tegen aan dat het script toch nog niet goed werkt. Ik heb ook even gekeken naar de oplossingen, maar alles ziet er verder goed uit. Kan het zijn dat de code die jij hierboven in het artikel plaatst, er anders uit ziet in sommige browsers, waardoor er fouten meege-copy/paste worden? Misschien zou je de code eens naar me kunnen mailen in een .txt file ofzo :-). Ben benieuwd, misschien lost dat alles op.

        Beantwoorden
  • Raymond Linschoten
    9 januari 2017 16:14

    Beste Gideon, ik wil graag jouw script gebruiken! En ervaar met het gekopieerde script ook wat problemen. Zou jij mij het script ook kunnen toesturen als txt??

    Vast hartelijk dank!

    Beantwoorden
  • Gideon Folkers
    12 januari 2017 00:07

    Het script is nu ook te downloaden via de volgende link: http://blog.smartinsight.nu/wp-content/uploads/2016/03/afas.txt

    Beantwoorden
  • Arjen Abma
    9 juni 2017 11:12

    Goedemiddag Gideon,

    Ik krijg het niet voor elkaar met AFAS online er komen namelijk geen tabellen naar voren bij XML source.
    Zowel de webservice als het script zijn groen maar geen data enige idee wat het kan zijn?

    Beantwoorden
    • Gideon Folkers
      9 juni 2017 11:58

      Beste Arjen,

      Als het goed is is er een bestand gedownload na het uitvoeren van de Webservice taak. Dit is een xml bestand. Kan je zien of dit bestand wel gegevens bevat?

      Anders zou het ook nog aan het definitie bestand (.xsd) kunnen liggen.

      Beantwoorden
  • Alles gaat goed tot het maken van de xsd.
    Daarbij krijg ik een foutmelding:
    The supplied xml instance is a schema or contains an inline schema. This class cannot infer a schema for a schema.
    Hoe los ik dat op?

    Beantwoorden
    • Gideon Folkers
      22 februari 2018 10:02

      Beste Piet,

      Dat betekent dat in het XML bestand al een definitie is opgenomen.
      Kan het zijn dat je bij het aanmaken van de getconnector (in de webservice task) bij de options de MetaData op 1 hebt staan ipv op 0? Zie deel I van deze blog serie.

      Alternatief zou zijn om in het xml bestand zelf te kijken, dan zie je aan het begin van het bestand de tag staan. De tekst tot en met moet je verwijderen en dan nogmaals proberen.

      Beantwoorden
      • de Metadata had ik helemaal niet, maar met deze waarde op 0 krijg ik nog steeds de definitie mee in de xml.
        Als ik deze handmatig weg haal kom ik wel verder en krijg dan de xsd en de kolommen.
        Hoe kan ik die switch wel goed gebruiken, ik zie namelijk ook al verschillende schrijfwijzen van de Tag. Metadata of MetaData?

        Beantwoorden
  • Johan Kruize
    17 april 2018 08:22

    Ik heb een aanpassing gedaan in Visual studio zodat de gegevens niet meer decrypted zijn.

    Daardoor waren plots alle scripts uit mijn project weer leeg. Ik heb het script weer toegevoegd maar krijg op twee plaatsen een foutmelding. Namelijk bij de regel :public String ReadFile(String FilePath, String ErrInfo)
    en
    public void WriteToFile(String FilePath, String strContents, String ErrInfo)

    De regel public void Main() geeft geen foutmelding.

    Ik kan niet verklaren waarom het script nu ineens niet meer klopt.

    Beantwoorden

Geef een antwoord

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *

Fill out this field
Fill out this field
Geef een geldig e-mailadres op.

Menu