Search code examples
sql-serverssisssis-2012

Organizing SSISDB using Project Deployment

Looking for guidance/examples of how you organize your SSISDB projects. Is there a best practice? I was not able to find any regarding defining projects.

Few thoughts I had:

  • One big project with all packages?
  • Keep your projects as small as possible?
  • Somewhere in between?
  • Organize by a business project?

Thanks in advance!

Solution

  • Just my two cents and some experience in the Project design.

    One big project with all packages?

    Certainly not, unless you have a reason for such design. Big projects have the following disadvantages:

    • Difficult to split work among persons if working on the same project, although possible.
    • Build and Deploy times of a big project can be substantial and slow down your development.
    • Big projects require much RAM on VS for manipulation
    • Ineffective usage of SSISDB versioning. SSISDB versions projects as a whole, not on package basis.

    Keep your projects as small as possible?

    Would not go as well. If you keep projects very small, you loose advantages of project design and infrastructure -- common project properties and Connection Managers. If your package - project ratio is 1:1, there is no sense of using SSIS Catalogs, except for better reporting.

    Somewhere in between? Organize by a business project?

    Yes, this makes sense. You organize packages in a project which share Source-Destination systems, goals and Business Projects. By splitting onto different projects, you can organize parallel work and deployment.
    Some rules of thumb. If you invoke one package from the other, this is a clear indication to include it in the same Project.
    If your packages in different Projects share same Connection Managers there is no need to put them into the same Project. You can better control this with SSISDB Environments, where you can manage general settings and map it to Projects.