patternsqlMinor
Automatic ETL between similar tables
Viewed 0 times
tablesbetweenetlautomaticsimilar
Problem
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.
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.
Solution
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:
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
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
Context
StackExchange Database Administrators Q#158854, answer score: 4
Revisions (0)
No revisions yet.