January 27, 2012 at 2:52 am
Hi,
I have created a data-flow in ssis containing a "update-row" step. This step should simply do an update of the row.
Problem is. The row is identified by 4 columns. 3 of which can be null-values.
So in SQL i would do:
UPDATE table a
SET a.Value = ?
WHERE A.ID1 = ?
AND ISNULL(a.ID2, 0) = ISNULL(?, 0)
AND ISNULL(a.ID3, 0) = ISNULL(?, 0)
AND ISNULL(a.ID4, 0) = ISNULL(?, 0)
But apparantly SSIS do not like my ISNULL solution. Any suggestions for an easy way to get what i want?
And no, refactoring/redesigning the tables in not an option.
January 27, 2012 at 9:17 am
Perhaps using a derived column handle the isnull conversion of the pipeline data to the value zero before you get to this step?
CEWII
January 27, 2012 at 10:04 am
What is the error you are getting?
How about somehting like this (may not be logically equivalent, but is close):
UPDATE a
SET a.Value = ?
WHERE
A.ID1 = ? AND
CASE
WHEN a.ID2 IS NULL THEN 1
WHEN a.ID2 = ? THEN 1
ELSE 0
END = 1 AND
CASE
WHEN a.ID3 IS NULL THEN 1
WHEN a.ID3 = ? THEN 1
ELSE 0
END = 1 AND
CASE
WHEN a.ID4 IS NULL THEN 1
WHEN a.ID4 = ? THEN 1
ELSE 0
END = 1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply