Post reply

SSIS config file stored in SQL server Table is not used by the SSIS package

  • May 4, 2009 at 6:16 am

    #200082

    Hi,

    I am trying to use SSIS config file details to be stored in SQL server table. However I can create the configuration with ease. Now the problem comes when I change the password in the configuration table and reopen the ssis package, it doesnot pick up the password from the SQL server configuration table. Whats strange I find it that that the ssis package doesnot read the configuration details from the SQL server table at all.

    To keep you informed, I have created the configuration table in the same database as what I am using. The table has been created using the ssis configuration wizard only.

    I have used ssis package XML configuration file with ease. But since these files contain sensitive data as SQL server IP, user, password, I have to find alternative option which seemed to use of SQL server table to store configuration details.

    I need some help if I am going wrong any where.

    Thanks in advance.

  • August 7, 2011 at 5:03 am

    #1364869

    Hi ,

    I too face similar question with SSIS sql server table configurations.

    I could create sql server configuration table easily for development instance.

    To UAT environment, I have changed the connection string information but the problem is how the SSIS package execution would identity this table configurations and read the information from this configuration table?

    If anybody knew the process, please help me out.

    Thanks in advance

    --Durga

  • August 8, 2011 at 5:58 am

    #1365120

    Can you try with the below one.

    Go to Package Properties and then Go to Security Tab and then Change the Protection Level to "EncryptSensitiveWithUserKey"

  • August 9, 2011 at 2:01 am

    #1365658

    sqlusers (8/8/2011)

    Can you try with the below one.

    Go to Package Properties and then Go to Security Tab and then Change the Protection Level to "EncryptSensitiveWithUserKey"

    I wouldn't do that. Encrypting all sensitive data with the user key (in this case the user key of the developer), can lead to issues once the package is deployed to the server and is executed under a different user account.

    If all the sensitive information is stored in a configuration table, use the DontSaveSensitive protection level.

    Make sure package configurations are enabled and that SSIS can find the configuration table (usually the connection string to the config table is stored in an environment variable or in a XML config file on a fixed location).

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 3 (of 3 total)

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