Setting up a new SSIS 2012 development team

  • Hi guys, First the challenge, then some background, then a request for advice.

    I am working as a contractor for a company that is finally embracing SSIS as it ETL tool of choice - YAY!

    So far we have done two projects, both of which have had pretty informal development and deployment strategies.

    The first was an SSIS2005 solution for a temporary project. The SSIS development was done in-house by a team of three developers (1 main - me and 2 part time). Development was done on local machines and package builds deployed to the server. The DEV database was shared between developers but each developer pretty much worked on different packages. Each package was in a different project and each project was in a different solution. The SQL code to support the SSIS was not booked into Source Code Control so deployment of all the artefacts was time consuming.

    The second project is a permanent solution and has been written by offshore resource by Remote Desktop Connection into the DEV server and developing in-situ. All packages are under a single project and this is the only project in the solution. I introduced Mercurial Source Code Control for the packages only (i.e. not the project or solution) and use the Redgate Toolbelt plugin for Mgmt Studio to manage the SQL elements into Source Code Control.

    I am an SQL/SSIS grunt (senior, but still a grunt) and am now being asked to head up a team of two internal junior SSIS developers and an outside contractor (who hopefully is at or above my standard of SSIS) to develop ETL strategy and solutions for a new data warehouse. The server solution will be 2012R2 with the DEV and TEST servers virtualised on a VMware cluster of windows servers. The PROD server will be clustered physical hardware with the MDB stored on a SAN. Mercurial will be the Source Code Control solution and I am insisting on RedGate Toolbelt so that we can use the MSSM plugin for SQL code control and the SQL compare for deployment.

    I am out of my depth a bit with defining the proper strategy for code management and also for config deployment (I will save that for another post). The basic choices are code locally or code on the DEV server. For practical purposes I will probably only be able to have one shared DEV database, rather than one per user - Still pushing for both individual and shared DEV databases. If we go down this route, should each developer have their own copy of the Mercurial repository on their local machines.

    The alternative is to have everyone develop on the server directly and to run a single Mercurial repository that all code gets booked into.

    My preference is for the first option (code locally, independent DBs and repositories, build and push to the DEV server) but only because this feels familiar from other programme development languages. Does anyone have any useful considerations or issues with different configurations for SSIS development. This methodology will form the standard for SSIS for the next 5 years, and this will end up being a huge codebase as we will be taking feeds from over 100 different systems by the time the DW is fully configured. If it makes any difference the plan for the DW logical model is full-on subtype/SuperType data definitions, the Physical model has not been agreed but will almost certainly be built on 3NF tables and some associated views to then generate fact and dimension tables for consumption by reporting tools (Most likely QlikView and BOXI 4.1)

  • I would agree with your preference. If you do everything locally and then push to the dev server you aren't stepping on each other's toes during development and unit testing.

    Develop and unit test locally then push to a dev server that does automated unit and integration testing. Then you can deploy to QA from the dev environment.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply