Change database and table names on the fly

[Former Member]

Hi,

In the data services designer, I'm importing some tables via an ODBC connection and some flat files. They will go in to new tables in my staging db.

I'll have to do the same jobs for 5 countries. Is it possible to change which source database the job is using? And which destination table they will end up in, considering something like Customers_DE, Customers_NO etc.

Looks like the flat files can use variables in the names, but I will need something similar for the databases/tables.

I did stuff like that with the DTS packages for SQL server, so hopefully it's possible with DS too.

Best regards

Lars

[former_member467822]

You won't be able to change the target table names on the fly. The simplest solution is to create a unique dataflow for each unique target table.

If the transformation logic is complex but standard across all target tables, then you could create an embedded dataflow to encapsulate the logic and then reuse this embedded dataflow across all of the unique dataflow. Or just create the first dataflow and replicate it for each of the addition target tables.

There is probably a way to do what you are asking using some staging tables and scripts - but I think this will be much more complicated than just creating one dataflow for each target table. And long term, it will be much easier to understand and maintain.