Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
daheri
Starting Member
9 Posts |
 Posted - 2012-01-27 : 05:48:29
|
Hi,I have a simple data-flow in SSIS that extracts a set of rows, and then for each row it will do an ole-db command to update a row.This is my sql-command i use:UPDATE table aSET a.Value = ?WHERE a.ID1 = ?AND a.ID2 = ?AND a.ID3 = ? Problem is, the extracted dataset can contain NULL values in the ID columns. NULL values in ID columns should also be compared so that NULL = NULL will evaluate to TRUEIn normal SQL i just wrap them with ISNULL, but SSIS does not allow me to wrap the questionmark with ISNULL. How can I make this work? |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2012-01-27 : 06:53:21
|
| Why do you do an update per row?Why not bring the entire set of data into a table and then perform the update set based as opposed to row for row, that way you get better performance and the ability to handle nulls properly.Duane. |
 |
|
|
daheri
Starting Member
9 Posts |
Posted - 2012-01-27 : 07:44:12
|
| Performance-wise we get only about 100 rows for updateing each run, so it is finished before we can blink an eye.So yes, it could be optimized, but there is no need to put any effort, time and money into it.Btw. I found a solution to my problem. I just created a procedure that handles the update, and call the procedure instead of a direct update. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2012-01-27 : 12:08:17
|
| Great. If it was me, I'd still do it with a set based solution, who knows if one day the data grows?Duane. |
|
|
|
|
|
|
How does SSIS treat NULLs?