update null values using ole db command

Question

I'm trying to update null values in my destination using the OLD DB Command in SSIS.  Does anyone know how to work around this?  Here is my error.

[Conditional Split [663]] Error: The expression "WHOLESALER_ZIP_CODE != lkpZIP" on "output "Changed" (693)" evaluated to NULL, but the "component "Conditional Split" (663)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row).  The expression results must be Boolean for a Conditional Split.  A NULL expression result is an error.

Answer 1

In Conditional Split :
Create the op as VALIDRESULTS with an expression WHOLESALER_ZIP_CODE != "lkpZIP"
Then  use this VALIDRESULTS for other components

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 2

nitish - not familiar with syntax of validresults....can you enlighten me?

Answer 3

You are using a conditional split..rt?
In conditional split you want to filter those records which have WHOLESALREZIPCODE not equal to lkpZIP.
So double click conditional split transform to open the editor and write:
OPName                 Expression
-------------------------------------------------------------------
ValidResults          WHOLESALER_ZIP_CODE != "lkpZIP"

Then when you will take the green op from conditional split to any other component, it will ask you to select a op(through a drop down box) .
Select the Validresults and connect to other component.

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 4

OK -

Got it -

the OutPut Name i have is called "Changed" - that is connected to the OLE DB Command which is scripted to update the zip code where the wslr pk matches the source file.

so in short i have done what you have suggested but it is not updating - there is an issue due to the fact the destination has records with "Null" values

Answer 5

Are you passing WHOLESALREZIPCODE as input parameter in OLEDB Command?
Do you want all the records where WHOLESALERZIPCODE is lkpZIP or any value except lkpZIP?

What is the script in OLEDB Command?

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 6

Try to use :
TRIM(WHOLESALERZIPCODE) != "lkpZIP" in conditional split

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 7

Nitesh -

here is the Script used in the ole db command -

Update [WHOLESALERS]
Set
[Zip]=?                     -- Column Name in Table
Where [WholesalerID]=?  --Column name of PK in table

Answer 8

ok - I will try but what effect will trim on the incoming source file have on the destination?

Answer 9

So you are doing mapping for ZIP with WHOLESALERZIPCODE in OLEDB Command.
Put a dataviewer on the connector between Conditional Split and OLEDB Command.
Check the values of WHOLESALERZIPCODE in the dataviewer.
You may want to filter the NULL Values for WHOLESALERZIPCODE in the conditional split as well
TRIM(WHOLESALERZIPCODE) != "lkpZIP" || ISNULL(WHOLESALERZIPCODE)

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 10

Nitesh -

I just want to review with you what i want to accomplish -

the null values exist in the DB...not the source file....(incoming data)

I'm trying to over write the null values with real data (notnull) where the wholesalerid(pk) matches the WholesalerID in the source file

thanks

Answer 11

If I were you, I wuld have done this:
I will take the WholeSalerId (PK) and WHOLESALERZIPCODE comming from conditinal split into a staging or temp table.
Then I will go to control flow and take a execute sql task.
Then write the script:
UPDATE WHOLESALER
SET ZIP = temp.ZIP
FROM WHOLESALRE INNER JOIN temp
ON WHOLESALER.ZIP = temp.ZIP

Have yu checked the values of PK column and WHOLESALERZIPCODE in dataviewer?

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 12

this is a development environment -


there is a bigger need to learn how to deal with null values that exist in the db when updating tables with data coming in the data flow

Answer 13

For updating the table uing data commng from Data flow there are 2 ways:
Use OLEDB Command (which you are already doing) and
SET based update using temp/staging table (I suggested earlier).

Only thing I am concerned is the value of ZIPCODE and PK  that are passing the conditional split as valid records.
Is the DFT showing rows flowing through Valid records op?
Try adding one copy column and connect the default op of conditional split to it. Add a dataviewer and check the value of the PK and ZIPCODE.

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 14

This works fine with fields that are not nulls...for example - i changed data in the db to erroneous info and then imported the source file ...

the source file corrected it fine through the normal routine using the ole db command....is there a better way to handle null values in the db?  if we can systematically change the null values to empty strings or something it may handle it better?

Answer 15

Nitesh -

here is my own answer - I have another resource and acheived a positive outcome.  I want to share this in the interest of sharing knowledge among my developer colleagues.  Thank you for your prompt response and your persistence this evening...

The solution is thus, - After the lookup the resulting value is passed thru a derived column

The definition is   drv_value    isnull(lkp_value) ? “ “ : lkp_value

Then use the drv_value in the conditional split – That will guarantee a value.