3

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 this sync one way? if so SSIS can auto detect and map columns, Why not just move the data over to staging tables and then use procs to update the data. Or if the tables can be unavailable while syncing just truncate them and fill with SSIS. – JasonBluefire Dec 21 '16 at 21:42
  • Amount of data I have is huge and its monthly data so I can not truncate my source. My sync is one way. – mouliin Dec 22 '16 at 11:03
4

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

  • Will have to check with DBAs to let me install this on the server. – mouliin Dec 22 '16 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.