Mixing parameter syntax in Execute SQL task

Hi all,

As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.

I can do this in two separate Execute SQL tasks as follows:

Task 1 syntax

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= -1
WHERE SourceTableName = 'cancel'

Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0

UPDATE dbo.tblLogging
SET
RowsReturned= ?
WHERE SourceTableName = 'cancel'

However I cannot make this work with a single SQL statement such as

DECLARE @.maxDate datetime
SELECT @.maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW

UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @.maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= ?
WHERE SourceTableName = 'cancel'

because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.

Is this behaviour by design, is it a bug, or is there something I've missed?

Thanks as ever,

Richard

Richard,

What error message do you get?

-Jamie