Automatic ETL between similar tables I have 43 tables on 2 different servers which I have to sync regularly. Because of our network policy and IG reasons, we can't have link server object between these two servers. Standard procedure so far is to create a SSIS package that will do the transfer however, I have come across situation where I need 43 of those. As much as I would love to get on with the task, there has to be a smarter way of doing this. I have seen some articles about generating packages dynamically using C# but C# ain't my strongest suit.

Is there a way from within SSIS to perform this task dynamically?

I was thinking like maintaining mapping tables, one with source and destination tables and another with source and destination column mappings and looping through it but obviously metadata needs to be refreshed to achieve the successful execution.

Any thoughts or ideas will be highly appreciated.
This kind of situation is exactly what BIML (Business Intelligence Markup Language) is made for.

From Introduction to Business Intelligence Markup Language (BIML) for SSIS, this solves exactly the problem you are facing:


  With Microsoft SQL Server Integration Services (SSIS), you can build
  powerful and flexible packages to manage your enterprise-wide ETL
  solutions. However, every ETL project has some repetitive tasks: you
  need to import 20 different flat file exports from the ERP system, you
  need to load 15 dimensions into the data warehouse and so on. With
  SSIS out-of-the-box, you are forced to create multiple individual
  packages which is very time consuming due to the lack of easy code
  reuse. In a typical BI project, the ETL implementation can easily take
  up 60% of the project time, so it is beneficial if the development
  time of similar SSIS packages can be cut down.


Some tools you should definitely check out are:


BIDS Helper which has an add-in for BI Development studio or SQL Server Data Tools
SSIS Package generator


If you want to create a metadata mapping table and use that to generate SSIS packages you can have a look at Generate multiple SSIS packages using BIML and metadata