none
What If environmental Variable is not present??

    Question

  • I am having a few packages that uses environment variable as a Configuration.

    If the Env Variable is not present the system package  is just showing me warning and it is taking default connection string .

    The configuration environment variable was not found.  The environment variable was: "SSIS_TEST". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid. 

    Can Any one tell me how to force the package to fail when it is not found Envi variable???

    Tuesday, October 25, 2011 7:43 PM

Answers

  • are you doing smth wrong? Because I've tested it in my computer.

    Anyway,

    could you please verify that:

     

    @echo off
    if "%NAME_OF_YOUR_ENVIRONMENTAL_VARIABLE_HERE%x"=="x"  goto novar
    echo Yes
    goto end
    :novar
    echo No
    :end

    And please set a breakpoint to Execute process task onpostexecute and tell me the value of EnvVariableExists?
    How to: http://www.sqlservercentral.com/articles/Video/64762/

     


    Senior BI Consultant & PM @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com Follow Me
    • Edited by Onur Omer Ozturk Tuesday, October 25, 2011 10:27 PM
    • Marked as answer by ravi3005 Wednesday, October 26, 2011 2:41 PM
    Tuesday, October 25, 2011 10:25 PM

All replies

  • you may check it within a seperate package using script task before you run your package.

    see coding example: 

    http://www.dreamincode.net/forums/blog/143/entry-2686-working-with-environment-variables-in-c%23/

    http://msdn.microsoft.com/en-us/library/y6k3c7b0(v=VS.90).aspx


    Senior BI Consultant & PM @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com Follow Me


    Tuesday, October 25, 2011 7:46 PM
  • Thanks for the Reply ...

    Is there any other way...am not familiar with script task..

     

     

    Thank You

    Tuesday, October 25, 2011 7:57 PM
  • Thanks for the Reply ...

    Is there any other way...am not familiar with script task..

     

     

    Thank You


    Unfortunately not :(  Its a real problem I must admit!
    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Tuesday, October 25, 2011 8:36 PM
  • thanks for the reply..

     

    Can u explain me script task ...

    i need a script that if env variable =null or doesnt exist the package has to fail

    Tuesday, October 25, 2011 8:42 PM
  • maybe using a batch file could also help you.

    Create a batch file (i.e CheckEnvVariables.BAT) put the following script in and save to disk.

     

    @echo off
    if "%TMP%x"=="x"  goto novar
    echo Yes
    goto end
    :novar
    echo No
    :end
    


     

    1. Create a new package,
    2. Create a string variable (package scope) within SSIS, name it as EnvVariableExists.
    3. Drag drop execute process task.
    4. Open, Go to Process tab and locate your batch file.
    5. In same screen see the StandartOutputVariable and select your variable to hold the value.
    6. Drag drop Execute Package task and link it to your initial package,
    7. Finally connect Execute process task to Execute Package task with following constraint:

     Eval Op: Express and Contstraint
     Value: Success
     Expression: @EnvVariableExists=="Yes\r\n"

     

    It should look like that:

    Then you will be able to check if the environmental variable exists before running your initial package.

    Let me know if it helps.

    Regards

    Onur


    Senior BI Consultant & PM @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com Follow Me

    Tuesday, October 25, 2011 9:15 PM
  • Thank You ..i will try it and let u know...
    Tuesday, October 25, 2011 9:37 PM
  • I have tried what u have said..

     

    there is no difference the package is running with out env variable..

    Tuesday, October 25, 2011 9:50 PM
  • are you doing smth wrong? Because I've tested it in my computer.

    Anyway,

    could you please verify that:

     

    @echo off
    if "%NAME_OF_YOUR_ENVIRONMENTAL_VARIABLE_HERE%x"=="x"  goto novar
    echo Yes
    goto end
    :novar
    echo No
    :end

    And please set a breakpoint to Execute process task onpostexecute and tell me the value of EnvVariableExists?
    How to: http://www.sqlservercentral.com/articles/Video/64762/

     


    Senior BI Consultant & PM @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
    Visit: ssisnedir.com Follow Me
    • Edited by Onur Omer Ozturk Tuesday, October 25, 2011 10:27 PM
    • Marked as answer by ravi3005 Wednesday, October 26, 2011 2:41 PM
    Tuesday, October 25, 2011 10:25 PM