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
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 1:28 PM
Points: 280, Visits: 622
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
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 1:28 PM
Points: 280, Visits: 622
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, October 23, 2013 12:46 PM
Points: 3,843, Visits: 3,833
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 01, 2013 10:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:24 PM
Points: 2,835, Visits: 1,124
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 03, 2013 7:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1480755
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse