Hi Tim,

I was able to create the SSISDB now.  I forgot to click the Enable CLR Integration check box on top of the screen.  Everything is good.

Thanks for your reply.

Brian.

What other alternative that you recommend? How can we change this query from MERGE to IF cardnumber EXISTS update ELSE insert method?

The target table GiftCard should have a unique CardNumber because it keeps track of the ending balance. The source table GiftCard_Transaction will have gift card redemption transaction from the...

I cannot use distinct because we want to capture all giftcard transactions...

The target GiftCard table has the cardnumber as the primary key which does not allow duplicate. The source table GiftCard_Transaction will have duplicate data because it is transactional table...

I am getting below error:

Msg 2627, Level 14, State 1, Line 7

Violation of PRIMARY KEY constraint 'PK_GiftCard'. Cannot insert duplicate key in object 'dbo.GiftCard'. The duplicate key value is (6033590000063027768).

The...

Here is the GiftCard Table:

CREATE TABLE [dbo].[GiftCard](

[CardNumber] [varchar](50) NOT NULL,

[Year] [int] NULL,

[Month] [int] NULL,

[Period] [int] NULL,

[TransAmount] [decimal](10, 2) NULL,

[BalanceAmount] [decimal](10, 0) NULL,

CONSTRAINT [PK_GiftCard] PRIMARY KEY CLUSTERED

(

[CardNumber] ASC

)WITH (PAD_INDEX...

Yes, below is a sample of the transaction table.

Card Number Transaction...