Dynamic Table Loads

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.
SELECT
TOP 1
s.name AS srcSchema,
t.name AS srcTbl,
‘mynewschema’ AS targSchema,
t.name AS targTbl
FROM
MyDb.sys.schemas s
JOIN MyDb.sys.tables t ON
s.schema_id = t.schema_id
WHERE
s.name in (‘dbo’)
ORDER BY
s.name,t.name

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.