It is not the same, Merge is working fine now.

You are using varchar columns as join keys. Add a derived...

It seems some issue with the sorting part. Try executing the attached package.

Also, is it possible to share some sample data as well?...

Can you share your sample package?

Now, the mappings are right.

But I can see your merge...

So, what is the error you are getting now?

It should work. Did you update the Conditional Split as well?

In Conditional Split, you have to use Key column coming from Database, not the one from flat file.

Thanks a lot for your help Ed and Chris. It works.

Cheers!..

In your Merge transformation, you're only selecting HSBC_TRADE_REF column from Sort Db.

1. You need to select HSBC_TRADE_REF column from Flat File as well.

2. Map the HSBC_TRADE_REF column coming from...

Can you give screenshot of the conditional split as well?

and the screenshot of the destination.

It seems you are refering wrong columns in your destination.

Jim5130 (5/27/2015)

---

My reference column data is a varchar and does not allow NULL value as it is the transaction number. I am getting this error right now:

"The value violated the...

Yes, there will always be a special character in the end.

I've tried something as below, it works, but with certain values.

DECLARE @txtMessageText varchar(max)

set @txtMessageText = 'QU - ...

You can use below query -

create table #tmp1

(col1 varchar(1)

,col2 varchar(1)

)

insert into #tmp1

select 'a','l' UNION ALL

select 'b','p' UNION ALL

select 'a','m' UNION ALL

select 'a','n' UNION ALL

select 'b','q' UNION ALL

select 'x','y'

SELECT ...

Here is one more option which you can use.

Using Merge in SSIS -

https://itssmee.wordpress.com/2010/10/03/ssis-insert-and-update-rows-in-a-table-based-on-the-contents-of-a-excel-file/%5B/url%5D

Yes, we can achieve this using SSIS.

You need to create a data driven subscription in your SSRS report which will extract the excel sheet and send it via Email.

In SSIS,...

I would suggest, load your flat file into a staging table first, then use Merge to insert/update records in your destination table.

Otherwise, you can use SCD transformation, but performance wise...