SSIS for Multiple Environments

  • Robbob

    SSC Veteran

    Points: 212

    Hi this is a great article. I am very familar with SSIS and would like any information on where I can get some more specifics on Multi Environment implementation. I have not had the need to create a Proxy or a credential account as you mention in your article. so I am trying to follow along and I think I have the jist of it. One question I have is when developing the package on your local machine and then running tests in each environment do you set package security up to a specific account? We have had some issues here with the credentials being saved as part of the package so we just use Passwords but I would like to know more about the credential implementation. Any info anyone can give on this topic is greatly appreciated.

    Thanks,

    Rob

  • WI-DBA

    SSCrazy

    Points: 2110

    When a developer runs the package from their machine, it will (in our case) connect as dbo to the Configuration View - the reason being the SSIS developers our part of a group that has higher privs on DEV. If you use sql logins, there will be a bit more management involved when doing the promotes from the developer, to the qa system, etc. When we move the package to a Test environment, the credential/proxy take care of the security. The package execution takes on its (job step owners) credentials.

    If I missed the question, please re-phrase and I will give it another shot.

    Cheers
    http://twitter.com/widba
    http://widba.blogspot.com/

  • Toby White

    SSCertifiable

    Points: 6127

    One question I have is when developing the package on your local machine and then running tests in each environment do you set package security up to a specific account?

    The way I handle this is by using the SQL Agent account or proxy. The package doesn't have any different security persay, but the security of the different agent/proxy accounts varries. Infact I developed a way to have one SSIS_configurations table for several environments.

    SSIS_configurations can be a view/function that returns the data depending on what account (suser_sname()) calls it.

  • SQLRNNR

    SSC Guru

    Points: 281252

    Nice article and very useful information.

    We use a table in each environment.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • danv

    Mr or Mrs. 500

    Points: 581

    ryan thanks alot for the info.

    robert. are you referring to the package protection level?

    when we are in development we EncryptSensitiveWithUserKey enabling each developer to do their own thing while working locally. when we put our packages to DEV or UAT or PROD environments we EncryptSensitiveWithPassword with a strong common password.

    this might have some useful info: http://msdn.microsoft.com/en-us/library/ms141747.aspx

Viewing 5 posts - 16 through 20 (of 20 total)

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