One of the SSIS development activities I dreaded was copying multiple source objects to tables in a staging database. The typical SSIS process of methodically mapping the source to target table is very time consuming especially when you have a large number of source objects. Additionally, you have to update packages when there are schema changes.
ADF provides a feature that auto creates the target table from the source schema. So, there is no need for manual mapping. By leveraging this auto create feature and other ADF functionality you are able to create one pipeline per data source connection instead of one package per data source object. For example, if you had 25 objects you were importing from Salesforce you’d need only 1 ADF pipeline. A pipeline is basically equivalent to a SSIS package.
Our design pattern for ADF is to incrementally copy source data to staging tables using this auto create feature. We then use stored procedures to merge the staging tables to the target tables. This process can handle schema changes in considerably less time then SSIS development.
We implement the above design pattern using our ChillETL product. This product is completely meta data driven allowing us to very quickly set up a schedule for multiple tables just by adding rows to the meta data. ChillETL also has tools for auto generating SQL such as creating the merge stored procedures required for this approach.
For more information on ChillETL please visit www.CloudData.Solutions/ChillETL