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

Question

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

dndaughtery

SSChampion

Points: 11290
More actions
July 31, 2013 at 7:09 am

#297032

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dndaughtery SSChampion Points: 11290 More actions · Answer 1

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 SSC Guru Points: 56440 More actions · Answer 2

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[/url] - by Jeff Moden

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

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 = ?;

SELECTValue = CASE @arg WHEN '1900-01-01' THEN GETDATE() ELSE @arg END

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

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