Databases Sync

[jeffw8713]

@BabyAqua

Is there a fast and convenient way to loop through all 300 tables? Or do I have to build out a task for each table?

You would have to build a control flow and data flow for each table. If this was something I had to work on I would look to BIML to generate the SSIS package(s).

@BabyAqua

As for the task itself, do you recommend setting up an OLE DB and write a SELECT statement like my previous posts? If you have a sample package that you can share, I would really appreciate it, like always!

It depends on the source system and what access I have to the source system. I prefer using stored procedures on the source system and using OLEDB Source to execute the procedure (with parameters). If the source system is not SQL Server then I usually go with ADO.NET and the appropriate managed provider for that system. The problem with ADO.NET is that it doesn't work with parameters - so you end up using a variable for the query for dynamic queries.

For the destination - I prefer using the OLEDB Destination and setting the option for fast table load - as well as setting the commit and batch sizes. Setting the commit and batch sizes insures that I don't bloat the transaction log and improves the performance.

With that said - if the vendor is providing this - why do you want to recreate it? I would have to assume that the vendor has a reason for how they set this up...in a lot of cases the vendor would be excluding data (rows) that have no value for reporting.

[BabyAqua]

Hi Jeff,

I had to research on Biml for Beginners: Script and Automate. Found a youtube video. Also, thanks for providing the best practice when it comes to the Source and Destination.

Jeff, you have a good question on the vendor's package. For now, all i know is that the package is not giving us a 1-1 sync. Regardless the reason why it is not syncing and leaving out records, by the end of the day, if we are reporting off of their data, both sources should be the same. As of now, we are connecting our reports to their servers. The numbers are coming out correctly. When we are ready to connect to our internal servers, we are expecting the same results from the reports. I know for sure that their package is not 100% correct/complete because there are key tables, missing key records. They are aware of this.

Thanks Jeff.