SSIS update after llokup not working

Question

SSIS update after llokup not working

dallas13

SSCertifiable

Points: 7491
More actions
June 5, 2019 at 4:11 pm

#3650495

I am importing data from db2-dev server to stg table in SQL in 1st data flow task and bringing only few columns. -- This is done and works.
I need to bring remaining columns from deb-prod to this same stg table in SQL using another data flow task. Here I have db2 source and then lookup that compares db2 source with stg in SQL and then updates remaining columns.
But i only updates 1st column instead of all. Can u tell why? See below update statement.
The primary key in both DB2 dev and prod server table is same = ( Account _NO + Bill_Cycle_Date)
UPDATE STG_BillComparison_Analytics
SET
TOTAL_BILL_AMT_PROD = ?
,LPC_AMT_PROD = ?
WHERE ACCOUNT_NO = ?
AND BILL_CYCLE_DATE = ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Are you using an OLEDBCommand task to do this? These are notoriously slow, because they process one row at a time, and I recommend that you re-engineer this as follows:

Create an empty staging table to accept 'the remaining columns', plus the lookup columns.
Direct all of the matched rows to this staging table
After the data flow has completed, add an ExecuteSQL task which performs the UPDATE in a single hit.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 2

Another option would be to use 2 sources in the data flow - add a merge or merge join and output the results to the destination. Each input source to the merge must be sorted, which may require adding a sort operation between the source and merge components.

If you can insure that the data coming from the 'left' source is sorted appropriately, you don't have to include a sort operation.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

dallas13 SSCertifiable Points: 7491 More actions · Answer 3

Thanks but its a part of bit complex already existing package and I have to use it this way only. I tried to create SP for the update statement and still its not working. When I check lookup output from data viewers I see lot of records but when it goes to OLEDB command and runs Update or SP then it updates only 1st record. Not sure why. Thanks anyway.

dallas13 SSCertifiable Points: 7491 More actions · Answer 4

Here is what I found.

db2 - account_no - data type - decimal

sql - account_no - data type - decimal

in lookup, i have to use both columns but by default its taking only bill_cycle_date while mapping. Thats why its not working. Bill cycle date has int datatype in both sql and db2.

Any guesses now?? Thanks

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

dallas13 wrote:

Here is what I found. db2 - account_no - data type - decimal sql - account_no - data type - decimal in lookup, i have to use both columns but by default its taking only bill_cycle_date while mapping. Thats why its not working. Bill cycle date has int datatype in both sql and db2. Any guesses now?? Thanks

That would require a truly inspired guess. You started off this thread by describing an UPDATE proc which doesn't work and now you are talking about failed lookups. We can't see your code, so your descriptions need to paint an accurate picture of what is going on. At the moment, I cannot even join the dots to create an outline.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel