tvp merge doesn't seem to update any data

Aug 14, 2010 at 12:10 PM
Edited Aug 14, 2010 at 12:11 PM

RE: SSIS TVP Merge Destination

Great idea -  but it doesn't seem to work. - wonder if I'm missing anything simple.

I've profiled it - it creates the custom type, then it creates the MergeProcedure, but it never calls the procedure for rows that pass through..  

If I create the objects myself and call the procedure manually, it updates the data as expected.

I'm on Microsoft Visual Studio 2008
Version 9.0.30729.1 SP
Microsoft .NET Framework
Version 3.5 SP1
Installed Edition: Enterprise

 

Profiled sql:

create type TVPType4d2624b8c16f49759242d2d00f71b821 as Table(
SiteVisitID varchar(100),
SiteVisitStartDate datetime,
TrackingID int,
AnonymousCookie varchar(1000),
URL varchar(2000),
ReferrerURL varchar(2000),
IP varchar(50),
PCID varchar(200),
IsNewPCID bit,
ChannelID int,
ReferrerURLHost varchar(200),
AffiliateID int,
LastCookiePCIDChannelID int,
SiteVisitAggregateID int
);
go
create procedure MergeProcedure_16f54ae462274a39b9efdcb277b5b843
( @tmpTable  TVPType4d2624b8c16f49759242d2d00f71b821 readonly )
 as

MERGE INTO SiteVisit AS TARGET USING @tmpTable AS SOURCE

ON  TARGET.SiteVisitID = SOURCE.SiteVisitID
  and ( TARGET.ChannelID != source.ChannelID
        or TARGET.LastCookiePCIDChannelID != source.LastCookiePCIDChannelID)
WHEN MATCHED THEN  UPDATE  SET
  TARGET.ChannelID = SOURCE.ChannelID ,
  TARGET.LastCookiePCIDChannelID = SOURCE.LastCookiePCIDChannelID;