Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Do I have to use a stored procedure in exe SQL task to use a parameter? Expand / Collapse
Author
Message
Posted Wednesday, July 31, 2013 7:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 355, Visits: 801
I want to set a conditional in an exec sql task based on the value of one of my ssis variables but would rather not have to create a stored proc. Is it possible to do something like this:


If ? = "19000101"
Begin
Select GetDate()
End
ELSE
Begin

Select ?

End

Id be setting another ssis variable to the result set from the task.
Post #1479463
Posted Wednesday, July 31, 2013 7:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 355, Visits: 801
The answer is no, I didnt need a stored proc...but, I did need to add the same ssis variable in as a paramter for the second placeholder (?)
Post #1479487
Posted Wednesday, July 31, 2013 10:02 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 6:29 PM
Points: 3,840, Visits: 3,852
It may be easier to understand, and maintain, if you created two separate Execute SQL tasks, one for each query and then used expressions on the precedence constraints that lead up to those tasks.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #1479565
Posted Thursday, August 1, 2013 10:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:50 PM
Points: 2,845, Visits: 1,160
You should rewrite the query so there is only one parameter.

SELECT Value = CASE WHEN Arg = '19000101' THEN GETDATE() ELSE Arg END FROM (SELECT Arg = ?) a

or even

DECLARE @Arg DATETIME;
SET @Arg = ?;
SELECT Value = CASE @Arg WHEN '1900-01-01' THEN GETDATE() ELSE @Arg END




Post #1480021
Posted Saturday, August 3, 2013 7:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 7,135, Visits: 12,746
Are you convinced you require a round trip to the database server at all?

If your example reflects the true need and the SSIS Package runs on the same machine as the database instance, or at least the same time zone, then a SSIS Variable that takes its value from an Expression like this is all you need:

@[User::YourDateVariable] == "19000101" ? GetDate() : @[User::YourDateVariable]

Where @[User::YourDateVariable] is the variable you were substituting into your SQL statement.

PS Note, you may need to tweak your data types when using an expression this way to compare strings and then returning a datetime from GetDate().


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1480755
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse