Post reply

SSIS Package Confiiguration Parameterization

  • August 8, 2007 at 10:25 pm

    #197237

    Can anybody guid meHow to make your SSIS package dynamic to where it can work in any  environment

  • August 9, 2007 at 1:59 am

    #725632

    You want to look at SSIS configuration files.

    There is a welth of knowledge on the net about it but basically, Menu > SSIS > Configurations.

    In the wizard, you specify what would want to have configured.

    If saved as a XML file, my preffered method, you can edit the file with any text editor and have one file per environment.

    At the bottom of this page, there is a quick explination: http://www.sqlis.com/26.aspx

     

    Let us know if you have any other questions regarding it.

     

    HTH.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • August 16, 2007 at 12:05 am

    #727262

    Thanks Crispin,

    I tried the steps given in that link.But it did'nt solve my problem. When i tried that, and transferred to another machine, i have to do environment variables? but the link has not stated anything about the environment variables. Could you throw more light on this aspect. Thanx in advance.

    Viswanathan N.

  • August 16, 2007 at 3:49 pm

    #727509

    Your package might have an environmental variable configuration and that variable might not exist on the machine you migrated to.

    Right click on the control flow canvas and select Package Configurations. Check the Configuration Type, second column from left, and see if any are set to Environmental Variable. Of so check to see the variable exists on the machine you migrated to and that its value is correct.

  • August 16, 2007 at 10:31 pm

    #727536

    Hi

    The use of standard configurations is always useful and this can be very beneficial while deploying the package to another location (QA, TEST, and Production).

    Environment variable

    Always use the environment variable to store path to XML file, which contains the connection string to configuration database.

    XML Files

    XML files are great candidate for storing connecting strings to initial configuration database. The path to these files can be stored in environment variable. This option proves to be very useful in deployment scenarios.

    Instead of storing all the required information in one XML file, the user should also create multiple files based on the business requirement. This way one SSIS package is not reading or dependent on configuration of another package.

    SQL Server Configuration

    SQL Server Configuration is the good candidate and most secure for storing data source and destination related connection string.

    Overall Configuration Settings

    1. Environment variable to store path to XML File

    2. XML File contains the connecting string to configuration database

    3. Configuration database contains the other configuration details like data source connecting strings, destination connection strings, folder paths for various possible usages within SSIS.

     

    Thanks
    Mohit Nayyar
    http://mohitnayyar.blogspot.com/
    "If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

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

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