I'm trying to setup a generic macro to generate TPT Exports that write to a DataConnector to generate delimited flat files. The issue i'm running into is that the Export operator appears to have to have a schema defined since it is a producer. The complicating issue is that most of the requests for this usage has to do with complex queries (joins, aggregations, case statements, and such in the query) and users/applications wanting to offload that data quickly in a delimited format. If this was just a one table dump this would be pretty easy to build (actually i have that built already). I could go back to using FastExport which I can make it do this via some sql trickery to force the delimiting of the data (basically concatenating the entire set of columns into a single column) and then just have to set it up so that the two bytes at the front could be removed via some other process.
So now to my question; is there a way to export data without a schema while using the Fastexport to DataConnector Consumer for the delimited flatfile so it can handle complex sql exports. I want this to be flexible enough that it could work for a single table or for a complex query. I have multiple macros to build TPT scripts to load data from delimited files, to move data between systems, etc. However, this is the first time I have tried to use TPT to actually export complex sql data out to a delimited flat file. From what I'm seeing; it does not appear to be easily doable due to the the fact that it requires a schema definition. There are potential ways around this, but was hoping i could just ignore the schema since I'm not 100pct sure I understand the need for the schema for exporting data to a file, unless that is the only way the data connector operator knows how to delimit the data. If that is the case, then this makes exporting complex SQL more difficult since there wouldn't be a quick and easy way to build the necessary schema from inside a macro.
Ultimately, i want to be able to provide macros to my developers who can then just pass in some variables and get a script that they can then use. I have used this method for loading data and for building simple data movement scripts for getting data from production to dev/test for developers. They plug in some parameters get a script and copy/paste and execute it. So i would like to be able to do the same thing for exporting to a flat file, but its looking like it may be a bit more convoluted when dealing with the more complex sql logic most users which to be able to run.