Multiple OR Conditions in SSIS Conditional Split

1
votes

I am working on a SSIS Task of loading data from the staging database to Live data base for a product workorder database.

I need to split the loading into New and Updated Values and based on the split, i will insert or update the records in the Live database.

I have created conditional split for New rows. However on creating condition for updated rows, my package is getting failed based on the reason

[Conditional Split [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Conditional Split" failed because error code 0xC020902B occurred, and the error row disposition on "Conditional Split.Outputs[Updated Rows]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

I found out the error occurred because of the condition i have specified. I have too many AND and OR conditions in my conditional split.

In my requirement, i will have Workorder ID and ProductID as consistent, however there will be rows like Order Quantity, Stocked Qty, Scrapped Qty getting updated in each data load. So I created the condition in conditional split as below

(srcWorkOrderID == lkpWorkOrderID) && (srcProductID == lkpProductID) && (srcOrderQty != lkpOrderQty) || (srcStockedQty != lkpStockedQty) || (srcScrappedQty != lkpScrappedQty) ..... and so on.

So if my workorderid and product id is matching in my staging and live database and other columns are not matching, i need to update all the other non matching columns in the Live Database.

If I specify only one not equal to condition like (srcWorkOrderID == lkpWorkOrderID) && (srcProductID == lkpProductID) && (srcOrderQty != lkpOrderQty) my package executes perfectly.

However I need multiple not equal to comparisons in my condition.

Any workaround for this?????

enter image description here

2
ssis
Are your databases on the same server? If so you could this much faster (and solve your problem) with a MERGE statement – Nick.McDermaid
Just concentanate columns srcOrderQty+srcStockedQty+srcScrappedQty and compre with lkpOrderQty+lkpStockedQty+lkpScrappedQty. Of course you must convert them to strings before concentanating. You will get one long string and compare with other :) – Justin

2 Answers

3
votes

Found the solution. I had some columns with NULL values in my condition

We need to handle the null values in SSIS conditional split as SSIS will treat the entire condition as NULL if there are null values in the variables.

Due to this, my entire condition was returned NULL

As a workaround, I handled the null values by ISNULL function for each variable and my package got executed without any errors...

1
votes

If you want to check the updations based on srcProductID and srcWorkOrderID.

Then add another braces seperatly for the all other columns(except above two)

(srcWorkOrderID == lkpWorkOrderID) && (srcProductID == lkpProductID) && 
(
(srcOrderQty != lkpOrderQty) || (srcStockedQty != lkpStockedQty) || (srcScrappedQty != lkpScrappedQty).....

)