SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 ssis package timeout outcome
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dmaxj
Posting Yak Master

173 Posts

Posted - 05/21/2013 :  11:58:22  Show Profile  Reply with Quote
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

Edited by - dmaxj on 05/21/2013 12:19:52

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 05/21/2013 :  12:22:55  Show Profile  Reply with Quote
You can use parameter validation - see here in the section Parameter Validation http://msdn.microsoft.com/en-us/library/hh213214.aspx
If you do that and the parameter validation fails, then the package won't go and try to execute the query.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/22/2013 :  00:41:56  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

dmaxj
Posting Yak Master

173 Posts

Posted - 05/22/2013 :  15:16:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/23/2013 :  00:47:18  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

dmaxj
Posting Yak Master

173 Posts

Posted - 05/30/2013 :  11:46:18  Show Profile  Reply with Quote
OK- thanks... I went the route of using a conditional task in from Data Flow... Working smoothly, now... Thanks for the input, everyone!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/31/2013 :  02:19:27  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000