I had to work on providing an effort estimate to convert multiple DTS packages into corresponding SSIS packages. An automated conversion tool was not available to us so the SSIS packages were to be built from scratch based on the existing DTS packages.
I took this opportunity to catalog all our DTS packages and created an Excel workbook to aid in the estimation. The formulas in the workbook calculate the effort in person-days for the rewrite of the current DTS logic into a SSIS package.
I approached this requirement as follows –
- Open each DTS package and used the Excel workbook to make an inventory of the type and number of all the tasks and connections in the package. Obviously, this is a repetitive and the most time consuming activity in the whole process.
- Decide on an estimated number of minutes required to recreate each type of task/connection in SSIS. This time could vary depending on the developer’s experience and skill.
- Lastly, put in standard time additions for activities like analysis, error handling, logging and documentation.
You can try that estimation workbook by downloading it at the link give below and tweaking the values to suit your needs. While you are there, don’t forget to rate it! 🙂 Please let me know in the comments section of this post if there are any suggestions or questions.
Download from the Technet Gallery link –
DTS to SSIS Conversion Effort Estimation Template.xlsx approx size: 30 KB
http://gallery.technet.microsoft.com/DTS-to-SSIS-Conversion-7153a957
Do you have some of your own SSIS estimation tips? Please share in the comments section here or at this SSIS Development Effort Estimation Methodologies Wiki on TechNet.