Hi All,

I am in the process of creating a rough project estimate, to design, develop, test and implement a new SSIS project. The purpose of this project is to completely replace the existing ETL system which is only made up of SQL replications, SQL Jobs and hand written SQL Views.

• To provide an idea of the present environment, we have 3 source databases which reside in the same physical server. There are approximately 100 tables in each of these databases. 
• Data from these databases is copied to a staging area using snapshot replications (yes, they run every day)
• Data is held in this staging area. The target database has several views that read the data from the staging. The views use UNION ALL to combine data from all three staging DBs as their structures are similar.
• Apart from these views, there are a handful of SQL jobs which which involve complex transformations, and are then loaded to the target tables. 

This is just a high level over view. This system isn't robust enough and has several other issues. Few of them being: It is not easily extensible, not easy to Maintain, performance is poor, etc.

Due to these reasons, we are considering replacing this with a proper SSIS system and we are doing an estimate for the same. We would like to keep the changes to the target system as minimum as possible.

I would like to know if these Replication Jobs need to be replaced using an SSIS component? or would they need to remain? In which case, we would just do the estimation to replace the SQL jobs and Views.

We do not have an expert in this field, therefore I would like to do a meaningful estimation with some help. Any suggestions or pointers would be very helpful. Of course this would only be a ball park figure which we will improvise on, as we go. There are other factors which I will also consider for the estimate such as resource availability, skill set of the resource, number of SQL jobs to replace & their complexity, number of views to replace & their complexity.

Currently, since we do not know the number of components we would have to build, we are going based on the number of components we have in the existing system and their complexity. Any suggestions on how to go about this would be appreciated.

Thanks

Dhivya

• Edited by Dhivya Elan Friday, August 28, 2015 10:52 AM Correction