Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dmaxj
Posting Yak Master
174 Posts |
 Posted - 2013-05-21 : 11:58:22
|
| I have a job that calls an SSIS package. The package executes a query with a parameter. If there is no parameter, the the package tries to execute and invalid SQL statement (DELETE FROM table1 WHERE mDate = ?) because there is no value for ?The job will return a failure, how can I tell whether the package is still trying to execute the invalid statement?Regards |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-21 : 12:22:55
|
| You can use parameter validation - see here in the section Parameter Validation http://msdn.microsoft.com/en-us/library/hh213214.aspxIf you do that and the parameter validation fails, then the package won't go and try to execute the query. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-22 : 00:41:56
|
| Why is it that parameter is lacking value in some cases? In that case, do you still want to go ahead and execute query ignoring parameter based filter or do you want to ignore step at all? Based on that requirement you can implement a conditional logic which will execute statement only if parameter has expected value------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2013-05-22 : 15:16:54
|
| Thanks, James and Visakh16!Visakh16, the parameter is lacking a value because the query will only have data every few weeks. I would rather handle an empty set based on that condition, but the original developer ignored empty sets. So, I noticed that the job calling the package always ran successfully, regardless of the parameter value. This does not mean that the package did not fail, only that the job completed (which is what the sql agent log shows) -I am looking at the conditional route for handling empty dataset events, but kinda not sure where to implement (precedence constraint or sql task, etc)Regards |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-23 : 00:47:18
|
quote:
Originally posted by dmaxj
Thanks, James and Visakh16!Visakh16, the parameter is lacking a value because the query will only have data every few weeks. I would rather handle an empty set based on that condition, but the original developer ignored empty sets. So, I noticed that the job calling the package always ran successfully, regardless of the parameter value. This does not mean that the package did not fail, only that the job completed (which is what the sql agent log shows) -I am looking at the conditional route for handling empty dataset events, but kinda not sure where to implement (precedence constraint or sql task, etc)Regards
If you want to do it in control flow then you should be using precedence constraints based on expression. If in data flow, you can use conditional task,derived column or expression task (if in 2012)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
dmaxj
Posting Yak Master
174 Posts |
Posted - 2013-05-30 : 11:46:18
|
| OK- thanks... I went the route of using a conditional task in from Data Flow... Working smoothly, now... Thanks for the input, everyone! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-31 : 02:19:27
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
ssis package timeout outcome