August 1, 2012 at 3:47 am
I have one SSIS package. In the package I configured the config file, and it is enabled. I am calling this package from SQL Job. There I provided the config file path as well.
My package store as file system in the server. Config file is in the Server H:\ drive.
If I remove the config file from the specific location, still my SQL job got sucess. I want my job should fail, if my config file is not available in the given path.
How I can achieve that. Pls suggest.
Thanks,
Pallab
August 3, 2012 at 12:32 am
Pallab Bakshi (8/1/2012)
I have one SSIS package. In the package I configured the config file, and it is enabled. I am calling this package from SQL Job. There I provided the config file path as well.My package store as file system in the server. Config file is in the Server H:\ drive.
If I remove the config file from the specific location, still my SQL job got sucess. I want my job should fail, if my config file is not available in the given path.
How I can achieve that. Pls suggest.
Thanks,
Pallab
You need to add some task at the beginning of your package that checks if the config has loaded. You could add for example a dummy variable in your package called @ConfigLoaded with the default set to FALSE. In the config file, you set this to TRUE. Check at the start if this variable has a value of TRUE.
ps: the reason the package still succeeds when removing the config is because it will run with the default values you specified in the package
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 22, 2012 at 12:33 pm
If you really want to directly check for a config file's availability, you can have a script task at the beginning of you package, which checks for the file (say your_package.dtsconfig) in the specified folder and if you don't find the file there then force fail the script task by saying :
Dts.TaskResult = (int)ScriptResults.Failure;
else say
Dts.TaskResult = (int)ScriptResults.Success;
Otherwise, as the other member suggested, have a variable which if not populated wuld mean the non avalaibility of the file.
Or maybe save / deploy your package by blanking out all variables which are read from the config file ie have theor default value as "". That way even if the config is not there, the package will fail as soon as it tries to use one of the variables.
My suggestion would still be a script task as that is more clear and more direct.
Hope this helps.
August 22, 2012 at 2:02 pm
n79799 (8/22/2012)
If you really want to directly check for a config file's availability, you can have a script task at the beginning of you package, which checks for the file (say your_package.dtsconfig) in the specified folder and if you don't find the file there then force fail the script task by saying :Dts.TaskResult = (int)ScriptResults.Failure;
else say
Dts.TaskResult = (int)ScriptResults.Success;
Otherwise, as the other member suggested, have a variable which if not populated wuld mean the non avalaibility of the file.
Or maybe save / deploy your package by blanking out all variables which are read from the config file ie have theor default value as "". That way even if the config is not there, the package will fail as soon as it tries to use one of the variables.
My suggestion would still be a script task as that is more clear and more direct.
Hope this helps.
Assuming that there are multiple versions of the package (dev, QA, production etc) and that the config file is in a different place for each, how does the script task 'know' which place it should be looking when the package executes?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 22, 2012 at 2:37 pm
There's another approach. If the config file is non-existant it goes back to the design time values for the variables that are being configured. Make sure those are invalid values, and then when it gets to an expected 'configurable' component and gets a bad error (say, an impossible path to a file) it'll roll over and cry.
You can have this as the first test of the package as well, and can work with n79's idea. Combining the idea, create a variable called 'Config Tested'. This is set to FALSE at design time (meaning that it can't be tested without failing actually, but you can play with that). Make sure it's configurable, and set it to 'TRUE' in your configuration file(s). Have the first object in the pathing test to make sure this variable is true. If it fails, fail the package.
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
August 22, 2012 at 3:26 pm
Thats a very valid question......I assumed that the package will be running in an environment where these things are correctly set up meaning the package is selfaware(by way of variables with defined paths for DEV, QA and PROD) or within the script task which is aware of the paths.
But yes, a very valid concern......because either way the Pkg will need to be aware of DEV, QA and the PROD paths......which means some kind of a hardcoding inside the package.
Its a classic problem where the thing used to configure other things itself is not configured properly. So i guess some amount of hardcoding is required.
August 22, 2012 at 11:21 pm
n79799 (8/22/2012)
Thats a very valid question......I assumed that the package will be running in an environment where these things are correctly set up meaning the package is selfaware(by way of variables with defined paths for DEV, QA and PROD) or within the script task which is aware of the paths.But yes, a very valid concern......because either way the Pkg will need to be aware of DEV, QA and the PROD paths......which means some kind of a hardcoding inside the package.
Its a classic problem where the thing used to configure other things itself is not configured properly. So i guess some amount of hardcoding is required.
There's no need to hardcode anything. Like I said 3 weeks ago and Grant repeated it yesterday, just create a variable that is FALSE at design time and is set to TRUE by the config. Check the variable with a script task. Not a single hardcoded line.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply