August 25, 2014 at 9:15 am
I have an issue in a simple Execute SQL task that I can't figure out. (SSIS 2104)
The task runs a Merge query that updates/inserts a dimension table from a load table. The TSQL query works fine in SSMS, but in the execute sql task it shows as executed successfully but doesn't actually perform any updates or inserts.
The package execution results list only validation start and completion, and that the query was 100 percent complete. There are no parameters or variables in the query.
I'm sure there must be something simple I am missing here.
Thanks
MERGE dbo.dimClient AS CM
USING
( SELECT DISTINCT
load_KIDS3_clients.idClientKey ,
load_KIDS3_clients.firstName ,
load_KIDS3_clients.lastName ,
load_KIDS3_clients.birthdate ,
CASE WHEN load_KIDS3_clients.gender IS NULL THEN -1
ELSE load_KIDS3_clients.gender
END AS [gender]
FROM
dbo.load_KIDS3_clients
) AS CS
ON CM.idClientKey = CS.idClientKey
WHEN MATCHED AND NOT ( CS.firstName = CM.firstName
AND CS.lastName = CM.lastName
AND CS.birthdate = CM.birthdate
AND CS.gender = CS.gender
) THEN
UPDATE SET
CM.firstName = CS.firstName ,
CM.lastName = CS.lastName ,
CM.birthdate = CS.birthdate ,
CM.gender = CS.gender ,
CM.dssUpdated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT
( [idClientKey] ,
[firstName] ,
[lastName] ,
[birthdate] ,
[gender] ,
[dssUpdated]
)
VALUES ( CS.idClientKey ,
cs.firstName ,
cs.lastName ,
cs.birthdate ,
cs.gender ,
GETDATE()
);
Tim
August 25, 2014 at 9:23 am
Do you have any other table named dimClient with different schema?
August 25, 2014 at 9:25 am
No - just the one table with that name.
Tim
August 25, 2014 at 9:39 am
The reason why asked it is, sometime the data might be there in the right table.
but while querying in the SSMS, if we don't specify the schema name, the data will be pulled from the table with the default schema of the logged in user.
why don't you just try to create a trigger with a print statstatement or switch on the sql profiler and see what's going on with that table.
thanks
rm
August 26, 2014 at 2:11 am
Quick thought, compare the results of these two queries
😎
;WITH DISTINCT_KIDS3 AS
(
SELECT DISTINCT
load_KIDS3_clients.idClientKey ,
load_KIDS3_clients.firstName ,
load_KIDS3_clients.lastName ,
load_KIDS3_clients.birthdate ,
CASE WHEN load_KIDS3_clients.gender IS NULL THEN -1
ELSE load_KIDS3_clients.gender
END AS [gender]
FROM dbo.load_KIDS3_clients
)
SELECT *
FROM dbo.dimClient CM
INNER JOIN DISTINCT_KIDS3 CS
ON CM.idClientKey = CS.idClientKey
WHERE CS.firstName <> CM.firstName
OR CS.lastName <> CM.lastName
OR CS.birthdate <> CM.birthdate
OR CS.gender <> CS.gender;
GO
;WITH DISTINCT_KIDS3 AS
(
SELECT DISTINCT
load_KIDS3_clients.idClientKey ,
load_KIDS3_clients.firstName ,
load_KIDS3_clients.lastName ,
load_KIDS3_clients.birthdate ,
CASE WHEN load_KIDS3_clients.gender IS NULL THEN -1
ELSE load_KIDS3_clients.gender
END AS [gender]
FROM dbo.load_KIDS3_clients
)
SELECT *
FROM dbo.dimClient CM
INNER JOIN DISTINCT_KIDS3 CS
ON CM.idClientKey = CS.idClientKey
WHERE NOT ( CS.firstName = CM.firstName
AND CS.lastName = CM.lastName
AND CS.birthdate = CM.birthdate
AND CS.gender = CS.gender
) ;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply