What if environment variable is not there??

Question

What if environment variable is not there??

kiran23330

SSC-Addicted

Points: 436
More actions
October 25, 2011 at 1:56 pm

#245219

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???

Viewing 15 posts - 1 through 15 (of 15 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

There is no explicit setting that I know of and the system will just generate a warning. HOWEVER, you could add a Script Task at the very start of the process that checks the connection and if its connection string equals the default to kick out a fail, then you could set THAT script task to fail the package on fail. This would work.

CEWII

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

kiran23330 (10/25/2011)
Can Any one tell me how to force the package to fail when it is not found Envi variable???

Unfortunately no, but I can give you a way to end up failing anyway. In design mode set the variable to something that you'd never configure. IE: "ImNotAServer_ConfigurationFailure"

Then, first step of your script, create a component in the Control Flow to test the value that's been set on something that uses the environment variable. This will depend on what you're using the environment variables for (I'm assuming it's for a package configuration). This could be anything from an attempt to select top 1 1 from sys.syscolumns to executing a blank proc. That'll force the failure.

What's the environment variable used to set?

EDIT: Heh, Elliott beat me. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Evil Kraig F (10/25/2011)
kiran23330 (10/25/2011)
Can Any one tell me how to force the package to fail when it is not found Envi variable???
Unfortunately no, but I can give you a way to end up failing anyway. In design mode set the variable to something that you'd never configure. IE: "ImNotAServer_ConfigurationFailure"
Then, first step of your script, create a component in the Control Flow to test the value that's been set on something that uses the environment variable. This will depend on what you're using the environment variables for (I'm assuming it's for a package configuration). This could be anything from an attempt to select top 1 1 from sys.syscolumns to executing a blank proc. That'll force the failure.
What's the environment variable used to set?
EDIT: Heh, Elliott beat me. 🙂

Sorry Man, you were responding to me on another thread..

CEWII

kiran23330 SSC-Addicted Points: 436 More actions · Answer 4

Thanks for the reply...

The Envi variable is having connection string of server and database ..

can You explain me in detail..am not familier with script task...

Thank you

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

kiran23330 (10/25/2011)
Thanks for the reply...
The Envi variable is having connection string of server and database ..
can You explain me in detail..am not familier with script task...

Add a script task to the control flow and place it first in line for execution. You want it to execute first.

Edit the vb.net script:

Public Sub Main()

If Dts.Connections("YourConnectionName").ConnectionString = "Your Default Connection String" Then

Dts.TaskResult = ScriptResults.Failure

Else

Dts.TaskResult = ScriptResults.Success

End If

End Sub

Should do it.

CEWII

kiran23330 SSC-Addicted Points: 436 More actions · Answer 6

Thank You for Reply..

What if the default connection string is in the environment variabel.??

i package gonna Fail?? if so tht gonna be a pro...

Can u gve me code like if environment variable = null then fail the package...

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

If it cannot get the value from the environment it will not be NULL it will be the default.

In this case you can't know if it failed to get the value or not, you have nothing to base it on.

That solution is tied to knowing that it WILL change at run-time.

A possible workaround is using the same environment variable to fill in a variable (defined as a string) where you can always tell.

That is a bit more complex and you need to add the variable as a read-only variable in the task editor so that you can use it in the task:

Public Sub Main()

If Dts.Variables("YourVariableName").Value.ToString = "Your Default Variable Value" Then

Dts.TaskResult = ScriptResults.Failure

Else

Dts.TaskResult = ScriptResults.Success

End If

End Sub

Basically you are using the same environment variable twice, to fill in a connection string AND another variable, you are only going to evaluate the other variable and it MUST NOT BE the default connection string, make it something like "Default Value" so its easy to pickout. And that variable won't be used by anything else in the entire package.

CEWII

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

Also, you need to click on the Script Task in the Control Flow and look at its properties, look for the property that is FailPackageOnFailure, by default it will be False, make it True.

CEWII

kiran23330 SSC-Addicted Points: 436 More actions · Answer 9

Thanks

I understand what ur saying...but What if Environment Variable doesnot exist on the server or system where the packge is running???

Thank You

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

By default the package will show a warning and the default value will be used. It will not die.

If you use the code I have specified it will die.

Beyond that I am unsure what you are asking..

CEWII

kiran23330 SSC-Addicted Points: 436 More actions · Answer 11

Thanks ..

what is the value of variable...it should be same as env variable like datasource=cccccc;intialcatalog=xxxxx;..................

Can u tell me indetail about that...

Thank you

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 12

The value of the variable is unimportant, just so that you can tell that what is there by default is NOT there at run-time. Use some simple text for the variable, then test for that text. IF you have the environment variable it WILL be different than the text and you know you can move on.

CEWII

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

Elliott Whitlow (10/25/2011)
There is no explicit setting that I know of and the system will just generate a warning. HOWEVER, you could add a Script Task at the very start of the process that checks the connection and if its connection string equals the default to kick out a fail, then you could set THAT script task to fail the package on fail. This would work.
CEWII

Would would you have in the script task? Any chance of a code example for that one?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 14

Gah! Sorry for my last, Elliott... I didn't scroll down far enough.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)