SQL Server Performance

SSIS Package Deployment Stategy?

Discussion in 'SQL Server 2005 Integration Services' started by seanworking, Nov 28, 2009.

  1. seanworking New Member

    I'm at the beginning stage of learning SSIS and have created a package that runs stored procedures that backup/copy and restore a database from one server to another. I have the package deployed on one server that then connects to the other servers via a linked server connection.

    I'm considering having one deployment server be my centralized package repository and using the same configuration to complete the processing for all the other servers that would be accessed by the packages.

    Does anyone have any insight into what would be a cost/benefit to this type of configuration?

    Thanks

  2. satya Moderator

    It is the best practice that you want to deploy a central repository for deployment strategy, few things to watch:
    During SSIS packages development, most of the time one has to share his package with other team members or one has to deploy same package to any other dev, UAT or production systems. One thing that a developer has to make sure is to use correct package protection level. If someone goes with the default package protection level ‘EncryptSenstiveWithUserKey’ then same package might not execute as expected in other environments because package was encrypted with user’s personal key. To make package execution smooth across environment, one has to first understand the package protection level property behaviour, please see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsprotectionlevel.aspx . In general, to avoid most of the package deployment error from one system to another system, set package protection level to ‘DontSaveSenstive’.It’s a best practice to take use of Sequence containers in SSIS packages to group different components at ‘Control Flow’ level. This offers a rich set of facilities
    o Provides a scope for variables that a group of related tasks and containers can use
    o Provides facility to manage properties of multiple tasks by setting property at Sequence container level
    o Provide facility to set transaction isolation level at Sequence container level.
    For more information on Sequence containers, please see http://msdn2.microsoft.com/en-us/library/ms139855.aspx. The most desired feature in SSIS packages development is re-usability. In other ways, we can call them as standard packages that can be re-used during different ETL component development. In SSIS, this can be easily achieved using template features. SSIS template packages are the re-usable packages that one can use in any SSIS project at any number of times. To know more about how to configure this, please see http://support.microsoft.com/kb/908018
  3. seanworking New Member

    Thanks you so much for you help!
  4. seanworking New Member

  5. charlii New Member

    Nice thread here.I am newbie here and don't know about SSIS before but now i get little information.Thanks dude.

Share This Page