none
How to coordinate among multiple developers RRS feed

  • Question

  •  

    What is the best practice in managing an SSIS development with multiple developers.

     

    For example, we have 2 guys that will be developing various SSIS packages for a common data warehouse. We are most likely going to divide up the unit of work by the tables that need to get loaded. So, one guy will develop packages to load tables 1 thru 5 and the other guy will develop packages to load tables 6 thru 10.

     

    Should each person work within his own project? (Not sure what a difference between a solution and a project is, so I may be thinking of a solution.) If so, can multiple people open the same project? I don't think you can.

     

    I'm very new to SSIS and this would be the first time I'm doing something like this. I haven't even deployed a package to the server yet so I'm not even sure what implications/consideration I have before I take on this development effort I'm about to.

    Friday, April 4, 2008 4:06 PM

Answers

  • A couple of things to consider:

    • Use a source control system - SourceSafe, TFS, or something
    • Keep your packages small (one destination table per package is my usual approach). Only one dev can work on a package at a time, so keeping them smaller allows more parallel work.
    • Multiple developers can work on the same project and solution at the same time. They just can't edit the same package at the same time. Don't plan on being able to merge changes to the same package, as you can with C# or other development languages. Merging in SSIS is going to cause major problems.
    • Make sure you are using configurations on the packages. This will making sharing them among the team (and promoting to production) much easier.

     

    Friday, April 4, 2008 4:35 PM
    Moderator
  • Well, you don't have to check the project out to edit packages, only to add them. I work with 4+ persons teams regularly where we use exclusive check out. You just have to check out the project file, add the package, then check the project file back in. It takes 60 seconds.

     

    You can copy packages between projects (packages belong to projects, not to solutions).

     

    A master package (this is model I use regularly) can exist in the same project or in a separate one. I like keeping it in the same project for organization, but as long as the packages it references are available (in the local file system, a file share, or deployed to a SQL Server) the master package will be fine.

     

     

     

    Tuesday, April 8, 2008 1:45 AM
    Moderator

All replies

  • A couple of things to consider:

    • Use a source control system - SourceSafe, TFS, or something
    • Keep your packages small (one destination table per package is my usual approach). Only one dev can work on a package at a time, so keeping them smaller allows more parallel work.
    • Multiple developers can work on the same project and solution at the same time. They just can't edit the same package at the same time. Don't plan on being able to merge changes to the same package, as you can with C# or other development languages. Merging in SSIS is going to cause major problems.
    • Make sure you are using configurations on the packages. This will making sharing them among the team (and promoting to production) much easier.

     

    Friday, April 4, 2008 4:35 PM
    Moderator
  • Thank you. That was helpful. Some more questions.

     

    What's the difference between a solution and a project? Can a solution have mulitple projects? If so, what are the consideration in organizing projects within a solution.

     

    Friday, April 4, 2008 5:02 PM
  • A solution can contain many projects. You have to use different projects for different technologies - for example, if you wanted to have your SSIS packages, and your SSAS dimension and cube definitions in the same solution, you'd need one SSIS project and one SSAS project.

     

    As far as breaking one set of SSIS packages up into multiple projects, it's primarily used for organization. If I am planning to deploy a set of packages as a unit, most of the time I will keep them in the same project. If the packages are completely independent (ie, one set of packages that loads flat files into an HR system, and another set that populates a data warehouse from the CRM system), I'd put those in different projects.

     

    Friday, April 4, 2008 5:11 PM
    Moderator
  • Understood... Let me extend on this.

     

    In your example of HR vs CRM, if these are independent units of work, rather than having 2 projects within a solution, why not have one solution (and one project within it) for HR and another solution (and one project within it) for CRM?

     

     

     

    Friday, April 4, 2008 5:36 PM
  • Sure - in fact, that's what I was intending, I just didn't express it very well.

     

    The only time I have had two separate SSIS projects in the same solution was when I had two parallel but independent processes that loaded the same target database.

     

    However, all of this is just from my point of view. Basically, solutions and projects are ways of organizing your work, so what works well for me may not work at all for you.

     

    Friday, April 4, 2008 5:41 PM
    Moderator
  • Ok... I'm going to be playing with this over the next few days, but ler me ask you anyway since you seem to be connecting with my questions.

     

    Can connections or other global "things" and the way you set them be a motivation for having multiple projects in a solution? I'm thinking may be you can have global "things" (wether they are variables, or connections, or whatever) at a solution level that you may want to share among your projects.

     

    Or if these global "things" are set set a project level, (which I think they are) then what are you gaining by having multiple projects in a solution?

     

    In your example "The only time I have had two separate SSIS projects in the same solution was when I had two parallel but independent processes that loaded the same target database.", couldn't you have accomplished the samething with spearate solutions because when you deploy you can set execution times on a per package basis... or am I wrong about this?

    Friday, April 4, 2008 6:04 PM
  •  fftw_ayi wrote:

    Can connections or other global "things" and the way you set them be a motivation for having multiple projects in a solution? I'm thinking may be you can have global "things" (wether they are variables, or connections, or whatever) at a solution level that you may want to share among your projects.

    You can have misc. files at the solution level, but not anything that impacts a SSIS project (unless you store .dtsConfig files at the solution level).

     

     fftw_ayi wrote:

    Or if these global "things" are set set a project level, (which I think they are) then what are you gaining by having multiple projects in a solution?

    There's really not much at a project level for SSIS, either. You can create Data Sources and Data Source Views at the project level, but the consensus among most of the regular posters on this forum is that both are a bad idea, and you should stay away from them. Package configurations are a better way of sharing settings among packages.

     

     fftw_ayi wrote:

    In your example "The only time I have had two separate SSIS projects in the same solution was when I had two parallel but independent processes that loaded the same target database.", couldn't you have accomplished the samething with spearate solutions because when you deploy you can set execution times on a per package basis... or am I wrong about this?

     

    Yes, I could have. The deployment of packages is / can be completely independent of the solution or project that the package is part of. Unlike C# or VB.NET, where project = binary executable, in SSIS, each package is an "executable" (using the term a little loosely).

    Friday, April 4, 2008 7:07 PM
    Moderator
  • Thanks. You've been very thorough.

     

    I have some more questions as a result of this discussion, but I think it belongs in a new topic. Would you mind taking a look. It has to do with Data Sources and Connection Managers.

     

     

    Friday, April 4, 2008 7:55 PM
  • jwelch, I would like come back to the discussion of working in a multiple developer environment. You suggested the following,

     

    "Use a source control system - SourceSafe, TFS, or something"

     

    We do have something like this where I work. However, before we do this, a colleague and I were simply playing around with one solution file on a shared directory. (I'm not sure if my motivation is correct, but the reason why we are trying to share one solution file is so that we keep our packages together.) SO, we tested the following scenario.

     

    We both opened a solution file at the sametime. He created Package_A and I created Package_B. He saves A first and I save B. When I save B, I don't know about A because my colleague wrote it disk after I opened the solution. But I thought, somehow, my interface will keep A, but it doesn't. It tells me that there is a conflict, but I'm not sure if this can be resolved.

     

    Now, I am not sure if we can modify the same solution simulaneously like this, but my orginal question was, "Can multiple developers work on the same solution?", your response was "Yes". So, would you please clarify?

     

     

    Monday, April 7, 2008 4:01 PM
  •  fftw_ayi wrote:

    jwelch, I would like come back to the discussion of working in a multiple developer environment. You suggested the following,

     

    "Use a source control system - SourceSafe, TFS, or something"

     

    We do have something like this where I work. However, before we do this, a colleague and I were simply playing around with one solution file on a shared directory. (I'm not sure if my motivation is correct, but the reason why we are trying to share one solution file is so that we keep our packages together.) SO, we tested the following scenario.

     

    We both opened a solution file at the sametime. He created Package_A and I created Package_B. He saves A first and I save B. When I save B, I don't know about A because my colleague wrote it disk after I opened the solution. But I thought, somehow, my interface will keep A, but it doesn't. It tells me that there is a conflict, but I'm not sure if this can be resolved.

     

    Now, I am not sure if we can modify the same solution simulaneously like this, but my orginal question was, "Can multiple developers work on the same solution?", your response was "Yes". So, would you please clarify?

     

     

     

    I'm 99.99% certain that John's response was "Yes, when you're using source code control." What you are doing instead is completely unsupported and very dangerous and should not be done.

    Monday, April 7, 2008 4:08 PM
    Moderator
  • Right, I don't want to do what I described above. So, say we use some kind of source control software. We use something called Tortoise SVN (this may not the official name for it.) I never used SourceSafe so I dont know how that works, but I'm assuming Tortois SVN works in a similar way.

     

    So, let's say, my colleague and I check out the solution at the sametime. We both do different things to it, ie, he creates Package_A, and I create Package_B. We save our solutions (which is now different) in our respective local disk. What happens when we both go to check in the solution?

     

    The thing that is confusing me is this. When you save a package, it not only modifies the package file, but it also does something to the project file and the solution file. So, when two or more people are working on their own packages in the same solution and they all save simultaneously, how does SourceSafe or any other such tools merge the solution and the project files together?

    Monday, April 7, 2008 4:39 PM
  • Changing a package does not update the project or solution. Adding a package to a project does update the project file, because it contains a reference to the package file.

     

    Evidently, your source control is does not currently do exclusive check outs. Most source control systems do support this, so you could check to see if you can enable that for your SSIS projects. This means only one of you will be able to edit the project file at a time - which you only need to do when adding a package to the project.

     

    Another option (and one I have not used, so I don't know how well it will work) is to use the Merge feature of your source control system to merge the project files together after you have both edited them. I really can't recommend this, though, as I have no personal experience with it, and don't know how well it would work.

    Monday, April 7, 2008 5:21 PM
    Moderator
  • Ok, so that makes sense... exclusive check outs. But then if that's the only way we can work on the same project, what's the point? We're not really working in parallel. One person has to finish before the other person can add his packages.

     

    So, should we then just work in our individual solutions and plan on bringing our packages into a third solution? (I'm assuming you can export and import packages across solutions)

     

    The whole reason why we want to have our packages in the same solution is so that we can make a "master" package that controls the flow of our individual packages. Does this make sense, and is my motivation correct in trying to keep the packages developed by multiple developers in the same solution?

     

    Monday, April 7, 2008 7:17 PM
  • Well, you don't have to check the project out to edit packages, only to add them. I work with 4+ persons teams regularly where we use exclusive check out. You just have to check out the project file, add the package, then check the project file back in. It takes 60 seconds.

     

    You can copy packages between projects (packages belong to projects, not to solutions).

     

    A master package (this is model I use regularly) can exist in the same project or in a separate one. I like keeping it in the same project for organization, but as long as the packages it references are available (in the local file system, a file share, or deployed to a SQL Server) the master package will be fine.

     

     

     

    Tuesday, April 8, 2008 1:45 AM
    Moderator
  • Ok, I think everything makes sense now. Thanks.

    Tuesday, April 8, 2008 1:48 PM
  • Hi I have the same scenario,

    We are multiple users updating the same SSIS solution. Could you kindly verify what kind of configuration you are referring to please? If I update a package, my colleague is asked for the password when he tries to modify it and vice versa when I try and use his. Thanks in advance.
    Wednesday, February 20, 2019 8:52 AM
  • Hi Rachel,

    SSIS packages have a property called ProtectionLevel

    You can set its value as DontSaveSensitive. So it won't ask for the password anymore among folks on the same team. Please take a look here:

    Securing Your SSIS Packages Using Package Protection Level


    Wednesday, February 20, 2019 6:19 PM