ETL-Alteryx Desktop

[tep_49]

Hi All,

I'm practicing ETL using alteryx designer and stuck at a point and really need help at this point.

 So am extracting data from a database(sqlserver) and loading it to another database(sqlserver) using alteryx input and output tools.

after successfully running the workflow, I want to log the results(messages) from the results window to a different database table.

im having hard time doing it and finding information on that, it needs to be automated( as in everytime i run the workflow the events info get saved in the sql server table). Any help is much appreciated, I have attached the screenshot of my workflow.

Thanks,

Nate

[geraldo]

@tep_49 

I didn't understand. Do you want to record the log in another database or the information extracted from the input etl in another database?

[tep_49]

Hi Geraldo,

Thank you for your response.

I want to record the log in another database.

Thanks,

Nate

[geraldo]

@tep_49 

If you are not using the Alteryx server you can use the Crew Macros tools.

http://www.chaosreignswithin.com/p/macros.html

a suggestion is to download the macros and use these two below and record the message column

no main workflow do this setting if there are macros inside the workflow

[danilang]

Hi @tep_49 

If you can't use the CReW runner macros or you need to run on Alteryx server you can try an approach like this one

The first step is to turn on Alteryx logging

All the messages from the results window will now be written to this directory. Now you just need a way to read them. 

In the attached example I used a chained app where the first workflow is the main one and the second one is the log reader.  You'll need to convert your main workflow to an analytic app on the Workflow tab of the Workflow Configuration window.  On the Properties tab of the Interface Designer set the On Success - Run another Analytic app to the name and path of the logging workflow.  This will cause it run immediately after the main workflow has run. 

The Logging workflow is this

It reads the file info from the logging directory and sorts them in descending order of LastWriteTime.  The next sample tool skips the most recent file, since it's the one being written to by the log writer workflow and causes a read error if your try to access it.  The second sample tool isolates the previous log file record and the dynamic input tool reads it as a .csv with \n as the delimiter.    

Here I push the results to a browse tool for demonstration purposes, but you can replace this with an Output Data tool to write to your logging database.  

In a server environment you'll have the issue where the last log file might not be the correct one since job can run concurrently.  In that case configure the second select tool to read the last X files and find the log file where the first record contains the name of the workflow that you're interested in. 

Another possible server issue is that that you might have multiple log files open simultaneously due to the concurrently running jobs.  In this case, you should be able to use a combination of DateTimeStart() and DateTimeNow() in the main workflow to set a time limit to search for log files.  Write the datetimes to a .yxdb file and read them in the LogReader workflow to limit the possible log files 

Dan 

[tep_49]

Thanks Geraldo and Danilang,

I have understood your solutions and they have worked for me but I decided to move on using a different approach using a batch key concept.

I really appreciate your help because that led me to think of another solution that's more aligned with my use case.