Project Model Organization Best Practices?

Question

Hello all,

I'm trying to find best practices on organizing ETL with the newer project deployment model. We have a large amount of packages in 2008r2 and some of my coworkers have expressed concern about having to always deploy entire projects instead of just packages that change. We do have TFS source control but it is fairly new to us. Our ETL is currently organized by destination subject area, such as Finance, HR, Legal, etc. Some of the loads are fairly small consisting of a few packages. A few of the loads are much larger such as 60 - 80 or more packages. It's these larger ones that often have multiple developers working on different aspects at the same time. One approach I've thought of is to split these larger groups into multiple projects. Maybe something like a stage load, DW load, etc. However, the SSIS catalog is now limited to having only one level of folders without any subfolders. I understand a project is like a subfolder but I can't find any examples on how to organize multiple large projects in SSIS. Is there a recommended approach? How granular should each project be? If it's a good idea to split a load into multiple projects are there any naming conventions to follow?

Thanks for your help! 

Answer 1

The best practice is probably a package per project, this is solely based on what I see happening when you modify one package in a project but need to deploy the rest in it, just like you said.

---

Arthur

MyBlog

Twitter

Answer 2

Our ETL is currently organized by destination subject area, such as Finance, HR, Legal, etc. Some of the loads are fairly small consisting of a few packages. A few of the loads are much larger such as 60 - 80 or more packages. It's these larger ones that often have multiple developers working on different aspects at the same time. One approach I've thought of is to split these larger groups into multiple projects.

In order to take the advantage of the SSIS\SSDT 2012, the entire project needs to be deployed to the IS catalog. I suggest that you can create specific SSIS projects (HR SSIS project, Finance SSIS project etc) based on subject areas. Then you would only have to redeploy the specific project, if any package inside project changed. BTW, deploying the project to the IS catalog is not all cumbersome and time consuming.

---

Thanks, hsbal