Post reply

Cons vs Pros of using SSISDB deployment vs File Sytem deployment of SSIS pkgs?

  • October 12, 2022 at 6:34 pm

    #4098805

    If we are using File System Deployment of SSIS packages pretty much accross the enterprise,  and examining pros and cons of possibly switching to SSISDB catalog type of deployment   (SQL Server 2016 and 2017, Visual Studio 2015 (and other versions)).

    What should we be strongly aware of before engaging into such change?  There got to be SOME Cons not just Pros.

    What are the Cons? And will the Advantages outweigh the disadvantages?

    Likes to play Chess

  • October 13, 2022 at 7:10 pm

    #4099256

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • October 13, 2022 at 9:34 pm

    #4099342

    With the understanding that I don't use either, the real question is, what are you trying to accomplish through all of this?  Why is the change necessary for you?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 13, 2022 at 9:38 pm

    #4099343

    Thats exactly what my question is :).  Should we consider such change? if yes then WHY. and if we should not , then also WHY?

    Likes to play Chess

  • October 13, 2022 at 10:13 pm

    #4099355

    Do you have some links to the documentation that compares the old v.s. the new methods?  I'm not a SSIS user but I'd like to see what they're saying.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 14, 2022 at 6:06 pm

    This was removed by the editor as SPAM

  • October 19, 2022 at 11:35 am

    #4101402

    One big con depending on what version you are doing is that in Project mode deployment of individual packages isn't possible.  You have to deploy the entire project.  This was a problem with changed if one package was ready to go and another in a partial state.  Assumes the one in a partial state is in use in an earlier version.  I think this was fixed with 2016, but it could be a later version.

    Sometimes the packages are completely unrelated.  So having them in a project setup makes things at least more difficult.

    There was a little climb for me to get used to the project model.  But for ETL projects it's easy to create a master package with lots of child packages that are part of the same project.  This has been a best practice for a while.  But the project mode made this much easier to implement.

  • October 19, 2022 at 3:04 pm

    #4101527

    RonKyle wrote:

    One big con depending on what version you are doing is that in Project mode deployment of individual packages isn't possible.  You have to deploy the entire project.  This was a problem with changed if one package was ready to go and another in a partial state.  Assumes the one in a partial state is in use in an earlier version.  I think this was fixed with 2016, but it could be a later version.

    Sometimes the packages are completely unrelated.  So having them in a project setup makes things at least more difficult.

    There was a little climb for me to get used to the project model.  But for ETL projects it's easy to create a master package with lots of child packages that are part of the same project.  This has been a best practice for a while.  But the project mode made this much easier to implement.

    As you suggest, the deployment of individual packages is now supported. Though I rarely do it ... deployments are automated from checked-in VCS branches, so there should never be the case where packages in that branch are still in development.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • October 19, 2022 at 3:11 pm

    #4101529

    From the top of my head, some pros are as follows:

    1. Comprehensive execution logging, with built-in reports, is available to you as a 'freebie'. No additional config required.
    2. The ability to share connections and parameters across an entire project.
    3. Package execution can be initiated from T-SQL
    4. Runtime values for package parameters and connections can be assigned from SSISDB 'environments'. These environments allow the encryption of 'sensitive' values (eg, passwords).

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • October 19, 2022 at 4:47 pm

    #4101577

    "Runtime values for package parameters and connections can be assigned from SSISDB 'environments'. These environments allow the encryption of 'sensitive' values (eg, passwords)."

    I have not been able to make this work.  You have actually done this?  I have to flip connections before sending it to production.  This isn't hard, but it's not ideal, and the environment capability should make this unnecessary.

  • October 19, 2022 at 7:39 pm

    #4101656

    What exactly do you mean by 'flip'?

    Yes, I have sensitive parameters assigned from SSISDB, but I think you must mean something more involved than that.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • October 19, 2022 at 9:42 pm

    #4101710

    you likely missing a step on your initial setup of the catalog - or you aren't using parameters on your packages correctly.

    have a look at https://www.sqlservercentral.com/steps/ssis-catalog-environments-step-20-of-the-stairway-to-integration-services - step by step instructions.

    sensitive parameters have an issue - when you are debugging in VS they don't work as expected (or they are more complicated) - there is a workaround to it which makes the packages work with them through a tiny c# script to add at the start of the packages. See about it on https://www.hansmichiels.com/2016/11/19/using-sensitive-parameters-ssis-series/ - chrome may complain about privacy - just ignore it.

    it will basically allow you to manually enter sensitive parameters while on VS without changing their type - and then environments on ssis catalog deal with the true sensitive parameters on higher environments.

  • October 20, 2022 at 7:57 am

    #4101842

    Thanks for posting the second link, Frederico. That is a useful technique.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • October 20, 2022 at 3:40 pm

    #4102067

    I was imprecise with flip.  I should have said "change" the server.  As there are two choices for me, it's like "flipping" a switch.

  • October 21, 2022 at 3:25 pm

    #4102553

    RonKyle wrote:

    I was imprecise with flip.  I should have said "change" the server.  As there are two choices for me, it's like "flipping" a switch.

    Do you mean switching from not sensitive to sensitive? The link Frederico posted (mostly) gets round that issue.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 15 posts - 1 through 15 (of 16 total)

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