UPDATE WITH SSIS

Question

UPDATE WITH SSIS

PSB

SSChampion

Points: 13195
More actions
April 18, 2011 at 9:58 am

#237860

Hi,
How do I do something like below with SSIS ?
UPDATE dbo.ABCD
SET
ABCD_ClosedDateTime = GETDATE ()
WHERE
ABCD_Call_ID IN + '@[User::_FileID] ' + - 1 AND
ABCD_Call_ID IN + ' @[User::_FileID]'
@[User::_FileID] is a SSIS package level variable.
Thanks,

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

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Create an ExecuteSQL task and use an Expression to define the SQL to be executed - you'll be able to build the expression to include your variables as required.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

steveb. SSC-Forever Points: 46733 More actions · Answer 2

In the control flow use the Execute SQL task.

I way I would do it would be to convert the statement into a strored proc with One parameter.

then in SSIS use the execute SQL taskt to call this proc and use a '?' to pass the parameter

eg.

EXEC [YourUpdateProc] ?

and in the parameter mapping tab of the task map the varaible to the paramter, you will need to use parameter name of '0' as the parameters are referenced by a zero based array.

Or use Phil's way, both have the same result..

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

By the way, that query syntax is a little unusual. What does @[User::_FileID] contain?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

PSB SSChampion Points: 13195 More actions · Answer 4

User::FileId is a variable that holds the FileID of the file loaded.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 5

PSB (4/18/2011)
User::FileId is a variable that holds the FileID of the file loaded.

And presumably an integer (given that you are performing maths on it)?

In which case, why not something like:

...

ABCD_Call_ID BETWEEN (@[User::_FileID] - 1) AND @[User::_FileID]

?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin