Creating a filename dynamically based on table data

dataservices job design
1

Hello,

I have created a “BATCH_ID” field for my data and assigned it using the method detailed here. Now I need to write this data to flat files in csv format.

However, I need to create the filename dynamically based on this BATCH_ID (something like _<BATCH_ID>.csv), and write each individual batch to a different file. I can hardcode the tablename, but see no way of getting the batch ID dynamically. Can someone please point me in the right direction?

I can of course load this data into a temporary staging table and then query it in a script, but I’d rather avoid doing this as:

  1. It increases the runtime of the job.
  2. I’d have to put the file write in a separate dataflow and loop it, generating the filename for each loop.

Both of the above are listed as bad practices in this thread.

EDIT:

Attempting to push that value into a global variable using a custom function is also not working. I’ve found several sources saying it is possible to set a global variable in a custom function call (such as this and this and this) but I see that the value is not persisting. I can see the value getting set in the custom function, but that value is not reflecting in the dataflow from where the custom function was called. :hb:

graghu1986 (BOB member since 2016-10-03)

2

There is no super easy solution to this. You’ve got to either loop through your batch Id’s at time of creation, or run the output through a script to split them.

We deal with a lot of flat files and I have to do this all the time. :cry:

jlynn73 :us: (BOB member since 2009-10-27)

3

Yeah, that’s what we’re doing right now.

Unfortunately, looping through the records means that I have to load the data into a temporary table. This brings about a whole host of performance issues that I would rather avoid as my data sizes are quite large (>100 million records for some tables). Not to mention, SQL databases are a lot more picky about date formats and not having null values in key fields, whereas ECC doesn’t seem to care too much about those. :roll_eyes:

graghu1986 (BOB member since 2016-10-03)