Trying to do ETL to move source data to target db. I don’t want to create a pipeline for every single table. There are hundreds of tables.
To start I want it to do this simply and assume a truncate and reload of all the tables. I will worry about incremental loads later.
I want to run a SQL Execute snap with this code to get a list of all the tables.
s.name AS srcSchema,
t.name AS srcTbl,
‘mynewschema’ AS targSchema,
t.name AS targTbl
JOIN MyDb.sys.tables t ON
s.schema_id = t.schema_id
s.name in (‘dbo’)
I want to then run the pipeline to go through the list of tables, truncate the target, select the source data, BCP the data into the target staging area. Once there, I have a SQL Merge routine already built that will pull in the differences to our lake and I can easily call that with a SQL Execute.
I’m struggling to find an example of this in any documentation or online.
Creating hundreds of pipelines is not going to work.