Advice on Transformation

  • Posts: 448
  • Karma: 2
  • Thank you received: 40

Advice on Transformation was created by bruce.gibbins

AETLE/P v6.3.7.xx

Hi

We have an inbound CSV file that has a header row containing some details about the file and its contents such as a Run Date and we need to extract that date to be used in further downstream processing including process flow and transformations.

One method I know works is that I can extract the RunDate by using a Package Script, reading the header row using a fairly simple Regular Expression to find the date and store it in a Package Variable.

But my initial thought was to use a transformation, setup a reader against the CSV file, read the first row and use the same RegEx expression to find the RunDate and store it in a package variable. This transformation would be called in the package rather than the method I suggested first.

The problem I see is that I believe a Transformation always needs a writer and there is no NULL writer that I can see. Am I correct?

Is there a more refined way of doing this? We need to fully transform the file later in the package. We just need to get this RunDate first so that we can check that other dependent data has been loaded into the database for the same date. If it hasn't then we can exit the package and try again later. This dependent data comes from a different source and not at the same time.

Thanks for your time.
1 week 7 hours ago #21439

Please Log in or Create an account to join the conversation.

  • Posts: 235
  • Karma: 1
  • Thank you received: 15

Replied by DeanCovey on topic Advice on Transformation

Does "Keep Value" help?
The following user(s) said Thank You: bruce.gibbins
1 week 37 minutes ago #21440
Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 448
  • Karma: 2
  • Thank you received: 40

Replied by bruce.gibbins on topic Advice on Transformation

Thanks Dean,

I have used Keep Value in many of our transformations and it would help if the process was downstream in the flow. In this case I need simply go into the source file (.csv) read row zero pull out the date, keep it and then use it in the next flow step which is a SQL Query Check on another table. If I don't find any data for that date in this table, I can then cleanly exit the flow and wait for the next flow cycle and try again. Once, I find data in the sibling table that matches the date I extracted from the source file I can then complete the rest of the package by calling a transformation that will process all of the rows in the source file and push them to a table.

So in this case and other similar ones I just need a quick and tidy way of reading row 0 get the date (via a regex) and store it so I can then check via SQL if data is present. I can do via script (already done). But whilst scripting is straight forward and doesn't scare me it is very wordy when using Pascal and lots of syntax things to be aware of. I could use Python but that increases the overhead for what should be a quick data grab.

A simple transformation with just a reader, transformer and a NULL Writer would work perfectly and then give me scope to visually describe what is going on via the transformer designer.

Thanks for the reply
cheers
6 days 23 hours ago #21442

Please Log in or Create an account to join the conversation.

  • Posts: 1194
  • Karma: 3
  • Thank you received: 149

Replied by Peter.Jonson on topic Advice on Transformation

We had a look and I can confirm that at the moment it is not possible to run a transformation without a writer.
This functionality is very easy to implement.
We will add for you to the next release
Peter Jonson
ETL Developer
The following user(s) said Thank You: bruce.gibbins
6 days 10 hours ago #21446

Please Log in or Create an account to join the conversation.

  • Posts: 448
  • Karma: 2
  • Thank you received: 40

Replied by bruce.gibbins on topic Advice on Transformation

Thanks Peter. Always appreciate the great support you offer
Cheers
6 days 9 hours ago #21448

Please Log in or Create an account to join the conversation.

  • Posts: 1194
  • Karma: 3
  • Thank you received: 149

Replied by Peter.Jonson on topic Advice on Transformation

Just letting you know that your suggestion was implemented in the latest beta release



www.etl-tools.com/articles/use-microsoft...fice-365-emails.html
Peter Jonson
ETL Developer
5 days 9 hours ago #21449
Attachments:

Please Log in or Create an account to join the conversation.

 

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies