SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Do I have to use a stored procedure in exe SQL task to use a parameter?


Do I have to use a stored procedure in exe SQL task to use a parameter?

Author
Message
dndaughtery
dndaughtery
SSC Eights!
SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)

Group: General Forum Members
Points: 900 Visits: 1086
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.
dndaughtery
dndaughtery
SSC Eights!
SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)

Group: General Forum Members
Points: 900 Visits: 1086
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 (?)
John Rowan
John Rowan
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6294 Visits: 4551
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
Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3886 Visits: 1465
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





Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15459 Visits: 14396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search