
â ããŒã1
â ããŒã3
ãã®ããŒãã§ã¯ãProductsãã£ã¬ã¯ããªãããŒãããããžãã¯ãå€æŽããŸãïŒ
- ãUnion Allãã³ã³ããŒãã³ãã䜿çšããŠã2ã€ã®çä¿¡ã¹ããªãŒã ã1ã€ã«çµåããŸãã
- æ°ããã¬ã³ãŒãã«ã€ããŠã¯æ¿å ¥ãè¡ããæ¢ã«è¿œå ãããŠããã¬ã³ãŒãã«ã€ããŠã¯æŽæ°ãè¡ããŸãã ã¬ã³ãŒããè¿œå ãšæŽæ°ã«åé¢ããã«ã¯ãLookupã³ã³ããŒãã³ãã䜿çšããŸãã
- ã¬ã³ãŒããæŽæ°ããã«ã¯ããOLE DBã³ãã³ããã³ã³ããŒãã³ãã䜿çšããŸãã
ãã®ããŒãã®æåŸã§ãåºåã»ããã䞊ååããããã«ãã«ããã£ã¹ãã³ã³ããŒãã³ããæ€èšããŸãã
ãã®ã»ã¯ã·ã§ã³ã§ã¯ãåèšã§ãUnion AllãLookupãOLE DB Commandãããã³Multicastã®4ã€ã®æ°ããã³ã³ããŒãã³ãã玹ä»ããŸãã
ããã«ãå€ãã®åçããããŸãã
SSISã®ç¶ç¶çãªçŽ¹ä»
æ°ããããã±ãŒãžãäœæããŸãã

ãããŠãååãã LoadProducts_ver2.dtsx ãã«å€æŽããŸãã

[å¶åŸ¡ãããŒ]é åã§ã[ããŒã¿ãããŒã¿ã¹ã¯]ãäœæããŸãã

ãããŒã¿ãããŒã¿ã¹ã¯ãèŠçŽ ãããã«ã¯ãªãã¯ãããã®ãããŒã¿ãããŒãé åã«ç§»åããŸãã SourceAããã³SourceBæ¥ç¶çšã®2ã€ã®ããœãŒã¹ã¢ã·ã¹ã¿ã³ããèŠçŽ ãäœæããŸãã ãããã®èŠçŽ ã®ååãããããããœãŒã¹AããšããœãŒã¹Bãã«å€æŽããŸãã

ãœãŒã¹Aã次ã®ããã«èšå®ããŸãã

ãªã¯ãšã¹ãããã¹ãïŒ
SELECT ID SourceProductID, Title, Price FROM Products
åªããæ©èœãäžåºŠã«ç€ºãããã«ãããã§ã¯æå³çã«SourceIDããªãªãŒã¹ããŸããã
ãœãŒã¹Bã次ã®ããã«èšå®ããŸãã

ãªã¯ãšã¹ãããã¹ãïŒ
SELECT ID SourceProductID, 'B' SourceID, Title, Price FROM Products
ãã®çµæãã»ããAã«ã¯3ã€ã®å[SourceProductIDãTitleãPrice]ããããã»ããBã«ã¯4ã€ã®å[SourceProductIDãSourceIDãTitleãPrice]ããããŸãã
ã Union All ãèŠçŽ ã䜿çšããŠã2ã€ã®ã»ããã®ããŒã¿ã1ã€ã«çµåããŸãã ããœãŒã¹AããšããœãŒã¹Bãããã®éãç¢å°ãæãããã«ããŸãããã

2ã€ã®åä¿¡ã»ããéã§åãã©ã®ããã«äžèŽãããã¯ããUnion AllãèŠçŽ ãããã«ã¯ãªãã¯ããŠç¢ºèªã§ããŸãã

ã芧ã®ãšãããããã§ã¯ååãäžèŽããåã®èªåäžèŽãè¡ãããŸãã å¿ èŠã«å¿ããŠããããã³ã°ãå®è¡ã§ããŸããããšãã°ã2çªç®ã®ã»ããããSourceIDåãè¿œå ããŸãã

ãã®å ŽåãããœãŒã¹Aãã»ããã®SourceIDã¯NULLã«ãªããŸãã
ãã®å Žåã®2ã€ã®ã»ããã®çµã¿åããã¯ãSSISåŽã§è¡ãããŸãã ãœãŒã¹ããŒã¿ããŒã¹ãšåä¿¡ããŒã¿ããŒã¹ã¯ç°ãªããµãŒããŒ/ SQL Serverã®ã€ã³ã¹ã¿ã³ã¹ã«é 眮ã§ãããšããäºå®ã«æ³šæãã䟡å€ããããŸãããã®ãããUNIONãŸãã¯JOIN SQLæäœã䜿çšããŠãç°ãªãããŒã¿ããŒã¹ã®ããŒãã«ã䜿çšããŠSQLã¯ãšãªãåžžã«äœæã§ãããšã¯éããŸããã ïŒä»¥äžã§èª¬æããLookupã®ä»£ããã«äœ¿çšã§ããŸãïŒã
NULLå€ããAãã«çœ®ãæããã«ã¯ããUnion Allãããã¹ããªãŒã ãéä¿¡ãããDerived Columnãã³ã³ããŒãã³ãã䜿çšããŸãã

掟çåãšãã£ã¿ãŒãããã«ã¯ãªãã¯ããŠã次ã®ããã«æ§æããŸãã

次ã®ããšãããŸãããïŒããŠã¹ãå©ããŸãïŒïŒ
- ãDerived Columnãã§å€ãReplace 'SourceID'ãã瀺ããŸã-ããã¯ãåºåã§å€ãSourceIDåãæ°ãããã®ã«çœ®ãæããããšãæå³ããŸãã
- REPLACENULLé¢æ°ããåŒãé åã«ãã©ãã°ããŸãã
- SourceIDåãREPLACENULLé¢æ°ã®æåã®åŒæ°ã«ãã©ãã°ããŸãã
- 2çªç®ã®åŒæ°ãšããŠãå®æ°ãAããèšè¿°ããŸãã
ãUnion AllããééããåŸã«ããŒã¿ã«äœãèµ·ãã£ãã®ããç解ããããã«ããUnion AllããããDerived Columnãã«åããç¢å°ã®ãEnable Data ViewerããäœæããŸãã

å®è¡æã«ããã±ãŒãžãå®è¡ãããšãçµæãšããŠå€æããã»ããã確èªã§ããŸãã

ããã§ã¯ããã®æ®µéïŒæŽŸçåã®åïŒã§ãæåã®ã»ããã®è¡ã®SourceIDåã«NULLå€ãå«ãŸããŠããããšãããããŸãã
ãšã³ããªãDemoSSIS_TargetããŒã¿ããŒã¹ã«ä»¥åã«è¿œå ããããã©ãããå€æããããã«ã Lookupã³ã³ããŒãã³ãã䜿çšããŸãã

ãããããã«ã¯ãªãã¯ãããšããã®èŠçŽ ãæ§æãããŸãã

ããã§ã¯ãäžèŽãããã®ãèŠã€ãããªãè¡ã«ã€ããŠããäžèŽããªãåºåãã¹ããªãŒã ã«ãªãã€ã¬ã¯ãããŸãã ãã®å Žåã åºåã§2ã»ããã®ã äžèŽäžèŽåºå ããšã ã«ãã¯ã¢ããäžèŽãªã åºå ããååŸããŸã ã
ããšãã°ããIgnore failureããšããå€ãèšå®ãããšãTargetIDãã£ãŒã«ãã§äžèŽããªãã£ãè¡ïŒäžèšåç §ïŒã«å€NULLãæžã蟌ãŸãããã¹ãŠã®è¡ã1çµã®ã äžèŽäžèŽåºå ããéããŠè¿ãããŸãã
ããã«ãã£ãã·ã¥ããšã¯ã1ã€ã®SQLã¯ãšãªã«ãã£ãŠåç §ãšããŠäœ¿çšãããã»ããïŒæ¬¡ã®ã¿ããåç §ïŒãã¡ã¢ãªã«å®å šã«ããŒããããSQL Serverãç¹°ãè¿ãåŒã³åºãããšãªããã£ãã·ã¥ããè¡ãäžèŽããããšãæå³ããŸãã
[éšåãã£ãã·ã¥]ãŸãã¯[ãã£ãã·ã¥ãªã]ãéžæãããšã[詳现èšå®]ã¿ãã§ãçä¿¡ã»ããã®åè¡ã«äžèŽããããã«å®è¡ããããã©ã¡ãŒã¿ãŒã§èŠæ±ãç»é²ã§ããŸãã 楜ãã¿ã®ããã«ããã®ããããã£ãããã£ãŠãSQL Server Profilerãä»ããŠãããã±ãŒãžã®å®è¡æã«çæãããã¯ãšãªã確èªã§ããŸãã
次ã®ã¿ãã§ããã£ã¬ã¯ããªãšããŠæ©èœããã»ãããå®çŸ©ããå¿ èŠããããŸãã

ããã«ãªã¯ãšã¹ããç»é²ããŸããïŒ
SELECT SourceID, SourceProductID, ID TargetID FROM Products
次ã®ã¿ãã§ã¯ããã£ã¬ã¯ããªã§è¡ãæ€çŽ¢ãããã£ãŒã«ããšããã£ã¬ã¯ããªã®ã©ã®åãåºåã»ããã«è¿œå ããå¿ èŠãããããæå®ããå¿ èŠããããŸãïŒå¿ èŠãªå ŽåïŒã

é¢ä¿ãå€å¥ããã«ã¯ãããŠã¹ã䜿çšããŠSourceProductIDãã£ãŒã«ããSourceProductIDã«ãã©ãã°ããSourceIDãã£ãŒã«ããSourceIDã«ãã©ãã°ããŸãã
ãDestination Assistantãã³ã³ããŒãã³ããè¿œå ããŠããLookup No Match Outputãã¹ããªãŒã ããã¬ã³ãŒããæ¿å ¥ããŸãã

éãç¢å°ããã«ãã¯ã¢ããããããOLE DBå®å ãã«ãã©ãã°ãããã€ã¢ãã°ããã¯ã¹ã§ãã«ãã¯ã¢ããäžèŽããªãåºåãã¹ããªãŒã ãéžæããŸãã

ãã®çµæã次ã®çµæãåŸãããŸãã

ãOLE DB Destinationããããã«ã¯ãªãã¯ããŠèšå®ããŸãïŒ


æ°ããã¬ã³ãŒããæ¿å ¥ããåŠçãè¡ããŸããã
次ã«ã以åã«æ¿å ¥ãããã¬ã³ãŒããæŽæ°ããããã«ãOLE DBã³ãã³ãã³ã³ããŒãã³ãã䜿çšããŠãéãç¢å°ãLookupããããã«ç§»åããŸãã

ãMatch Match Outputãã¹ããªãŒã ã¯ããã®ã³ã³ããŒãã³ãã«èªåçã«éãããŸãã å ã»ã©éžæãããLookup No Match Outputãã¹ããªãŒã ïŒ

ãOLE DBã³ãã³ãããããã«ã¯ãªãã¯ããŠèšå®ããŸãã


次ã®æŽæ°ãªã¯ãšã¹ããäœæããŸãã
UPDATE Products SET Title=?, Price=? WHERE ID=?
次ã®ã¿ãã§ã¯ãçä¿¡ã»ãããã«ãã¯ã¢ããäžèŽåºåãã®è¡ã®ããŒã¿ã«åºã¥ããŠãã©ã¡ãŒã¿ãŒãã©ã®ããã«èšå®ããããã瀺ããŸãã

SSMSã䜿çšããŠãDemoSSIS_SourceBããŒã¿ããŒã¹ã«æ°ãã補åãè¿œå ããŸãã
USE DemoSSIS_SourceB GO -- SET IDENTITY_INSERT Products ON INSERT Products(ID,Title,Price)VALUES (6,N'',NULL), (7,N'',NULL), (8,N' ',NULL) SET IDENTITY_INSERT Products OFF GO
ããŒã¿ãã©ã®ããã«å€æŽããããã远跡ããããã«ãå®è¡ã®ããã«ããã±ãŒãžãéå§ããåã«ãå¿ èŠãªå Žæã§ãEnable Data Viewerããå®è¡ã§ããŸãã

ããã±ãŒãžãå®è¡ããŠå®è¡ããŸãïŒ

ãã®çµæãOLE DB Destinationã³ã³ããŒãã³ãã䜿çšããŠ3è¡ãæ¿å ¥ãããOLE DB Commandã³ã³ããŒãã³ãã䜿çšããŠ10è¡ãæŽæ°ãããããšãããããŸãã
ãOLE DBã³ãã³ããã§èŠå®ãããŠããã¯ãšãªã¯ãçä¿¡ã»ããã®åè¡ã«å¯ŸããŠå®è¡ãããŸããã ãã®äŸã§ã¯10åã§ãã
ãOLE DBã³ãã³ããã§ã¯ãããšãã°ãã¿ã€ãã«ãŸãã¯äŸ¡æ Œãå€æŽããããã©ããã確èªããå€ã®ãããããç°ãªãå Žåã«ã®ã¿å¯Ÿå¿ããè¡ãæŽæ°ãããªã©ãTSQLã§ããè€éãªããžãã¯ãèšè¿°ã§ããŸãã
æ確ã«ãããããDemoSSIS_TargetããŒã¿ããŒã¹ã®ProductsããŒãã«ã«æ°ããåãè¿œå ããŸãã
USE DemoSSIS_Target GO ALTER TABLE Products ADD UpdatedOn datetime GO
次ã®ã³ãã³ããæžããŸãããã

ããŒã ããã¹ãïŒ
DECLARE @TargetID int=? DECLARE @Title nvarchar(50)=? DECLARE @Price money=? IF(EXISTS( SELECT Title,Price FROM Products WHERE ID=@TargetID EXCEPT SELECT @Title,@Price ) ) BEGIN UPDATE Products SET Title=@Title, Price=@Price, UpdatedOn=GETDATE() WHERE ID=@TargetID END
ãŸããããããã¹ãŠãã¹ãã¢ãããã·ãŒãžã£ã®åœ¢åŒã§é 眮ãããEXEC ProcNameïŒãïŒãïŒããåŒã³åºããŠç»é²ããããšãã§ããŸãã ããã§ã¯ã誰ã«ãšã£ãŠã䟿å©ã ãšæããŸããæã«ã¯ããã¹ãŠã1ãæã«ãŸãšããæ¹ã䟿å©ãªå ŽåããããŸãã SSISãããžã§ã¯ãã ãã ããããã·ãŒãžã£ã䜿çšãããšãå©äŸ¿æ§ãåŸãããŸãããã®å Žåãããã·ãŒãžã£ãå€æŽããã ãã§ãSSISãããžã§ã¯ãã®åäœæãšåå±éãåé¿ã§ããŸãã
ãã®åŸãã³ãã³ãããã¹ãã®é åºã«åŸã£ãŠãã©ã¡ãŒã¿ãŒãã€ã³ãã£ã³ã°ãåå®çŸ©ããŸãã

DemoSSIS_SourceAããŒã¿ããŒã¹ãæŽæ°ããŠã¿ãŸãããã
USE DemoSSIS_SourceA GO UPDATE Products SET Price=30 WHERE ID=2 --
ãŸããå®è¡æã«ãããžã§ã¯ããéå§ããŸãã ãã®çµæãå®è¡ã®ããã«ããã±ãŒãžã次ã«éå§ããåŸããã®ã¬ã³ãŒãã«å¯ŸããŠã®ã¿ãUPDATEã1åã ãå®è¡ããå¿ èŠããããŸãã

ããã±ãŒãžã®å®è¡åŸãã¯ãšãªã䜿çšããŠããã確èªããŸãã
USE DemoSSIS_Target GO SELECT * FROM Products ORDER BY UpdatedOn DESC

ãã®ããŒãã®ãã¬ãŒã ã¯ãŒã¯ã§ã¯ã ãã«ããã£ã¹ãã³ã³ããŒãã³ããæ€èšããŸãã ãã®ã³ã³ããŒãã³ãã䜿çšãããšã1ã€ã®ã¹ããªãŒã ããè€æ°ãååŸã§ããŸãã ããã¯ãåãããŒã¿ã2ã€ä»¥äžã®ç°ãªãå Žæã«èšé²ããå¿ èŠãããå Žåã«åœ¹ç«ã¡ãŸãã 1ã€ã®ã»ãããå ¥ããŸãããå¿ èŠãªã ãã³ããŒãåºããããã®ã»ããã®åã³ããŒã§ãç§ãã¡ã¯äœã§ãã§ããŸãã
ããšãã°ãDemoSSIS_TargetããŒã¿ããŒã¹ã«å¥ã®LastAddedProductsããŒãã«ãäœæããŸãã
USE DemoSSIS_Target GO CREATE TABLE LastAddedProducts( SourceID char(1) NOT NULL, -- SourceProductID int NOT NULL, -- ID Title nvarchar(50) NOT NULL, Price money, CONSTRAINT PK_LastAddedProducts PRIMARY KEY(SourceID,SourceProductID), CONSTRAINT CK_LastAddedProducts_SourceID CHECK(SourceID IN('A','B')) ) GO
ãã®ããŒãã«ãã¯ãªã¢ããã«ã¯ããSQLãããŒã®å®è¡ãã³ã³ããŒãã³ãããå¶åŸ¡ãããŒããšãªã¢ã«è¿œå ãããTRUNCATE TABLE LastAddedProductsãã³ãã³ããèšè¿°ããŸãã


ãããŒã¿ãããŒã¿ã¹ã¯ãã³ã³ããŒãã³ãã®ãããŒã¿ãããŒãé åã«ç§»åãã次ã®ããã«ã³ã³ããŒãã³ããè¿œå ããŸãã

é»è²ã®æå笊ã«æ³šæããŠãã ãã-ããã¯ãUpdatedOnåãè¿œå ãããã€ã³ãããªãã£ãããã§ãã ãOLE DB DestinationãèŠçŽ ã«ç§»åããããããã³ã°ãã¿ãã«ç§»åããUpdatedOnåãå ¥åãã£ãŒã«ããšããŠç¡èŠããŠãOKããã¯ãªãã¯ããŸãã

å¥ã®ãOLE DB DestinationãèŠçŽ ãäœæããMulticastèŠçŽ ãã2çªç®ã®éãç¢å°ããã®èŠçŽ ã«ãã©ãã°ããŸãã

ããããããããããã«ååãå€æŽããŸãã

ãTo LastAddedProductsããèšå®ããŸãã


æåŸã®3ã€ã®æ¿å ¥ãããã¬ã³ãŒããSSMSã§åé€ããŸãã
USE DemoSSIS_Target GO DELETE Products WHERE SourceID='B' AND SourceProductID>=6
ãããŠãããã±ãŒãžãå®è¡ããŠå®è¡ããŸãïŒ

ãã®çµæã補åãšLastAddedProductsã®2ã€ã®ããŒãã«ã§è¿œå ãè¡ãããŸããã
第äºéšã®çµè«
ãã®ããŒãã§ã¯ãå°ããªãã£ã¬ã¯ããªãåæããæ¹æ³ãæ€èšããŸããã ããã§ã¯ããã¡ããããœãŒã¹å ã®ããŒã¿ããŸã åé€ã§ããç¬éã¯èæ ®ãããŠããŸããããå¿ èŠã«å¿ããŠãèªåã§è©ŠããŠã¿ãããšãã§ããŸãã åé€ãããšãã¯ãè¿œå ã®èŠçŽ ãèæ ®ããå¿ èŠãããå ŽåããããŸããããšãã°ãä»ã®ããŒãã«ããåé€ãããã¬ã³ãŒããžã®ãªã³ã¯ãããå ŽåããããŸãïŒæ¬¡ã®éšåã§ã¯ãããè¡ãäºå®ã§ãïŒã
åç §æŽåæ§ã«éåããªãããã«ãåä¿¡ããŒãã«ã®ãšã³ããªãè«ççã«åé€ãããå ŽåããããŸããããšãã°ãã¿ã€ããããïŒè«çåé€ãã©ã°ïŒã®åé€æžã¿ãã£ãŒã«ããŸãã¯æ¥æã¿ã€ãïŒè«çåé€æ¥æïŒã®DeletedOnããã®ããŒãã«ã«è¿œå ã§ããŸãã
å Žåã«ãã£ãŠã¯ãã¿ãŒã²ããããŒã¿ããŒã¹ãé 眮ãããŠãããµãŒããŒäžã«ãè£å©çãªäžéããŒã¿ããŒã¹ãäœæããïŒéåžžã¯ã¹ããŒãžã³ã°ãšåŒã°ããŸãïŒããœãŒã¹ããã®ãçã®ãããŒã¿ãæåã«ããŒããããŸãã ã¿ãŒã²ãããšã¹ããŒãžã³ã°ã¯ââåããµãŒããŒäžã«ããããã2çªç®ã®ã¹ãããã¯ããããã®äž¡æ¹ã®ããŒã¿ããŒã¹ã®ã»ãããæäœããSQLã¯ãšãªïŒSQL MERGEã³ã³ã¹ãã©ã¯ããŸãã¯JOINã³ã³ã¹ãã©ã¯ãã䜿çšããã¯ãšãªãªã©ïŒãç°¡åã«èšè¿°ããããšã§ãã
SSISã¯éåžžã«èå³æ·±ãããŒã«ã§ãããå Žåã«ãã£ãŠã¯çµ±åããã»ã¹ãå€§å¹ ã«ç°¡çŽ åã§ããã®ã§ãç§ãæãã«ããã®ããŒã«ã¯æŠåšåº«ã«æã£ãŠããŠã害ã¯ãããŸããã ããããåœç¶ãªããããã¹ãŠã®ééãéãç¶æ³ããããŸããããšãã°ããªã³ã¯ãµãŒããŒã䜿çšããŠçŽç²ãªTSQLã§ããã·ãŒãžã£ãèšè¿°ããããOOPã®ãã¹ãŠã®æ©èœã䜿çšããŠä»ã®ããã°ã©ãã³ã°èšèªã§ãŠãŒãã£ãªãã£ãèšè¿°ããããããªã©ãä»ã®æ¹æ³ã䜿çšããŠçµ±åãèšè¿°ããæ¹ãåççã§ãã
ææãå匷ãããšãã¯ã奜å¥å¿ãèŠããŸããããããšãã°ã衚瀺ããªãã£ãã¿ããã¯ãªãã¯ããæ å ±ã調ã¹ãŠåæããç¢å°ãã¯ãªãã¯ããŸããç¬èªã®ããããã£ãšèšå®ããããŸãã ããªãã«ãšã£ãŠèå³æ·±ããšæããããã¹ãŠã®ãã®ãè©ŠããŠã¿ãŠãã ãããå°ããªãã¹ãã±ãŒã¹ãæ ãããšã¯ãããŸããã ãããäŸå€ã«ã€ãªããããã«ã¹ããŒã ãå€æŽãããã®ç¶æ³ããæãé©åãªæ¹æ³ãèŠã€ããããšããã³ã³ããŒãã³ãã«ããé©åãªãã©ã¡ãŒã¿ãŒãéžæããŸãã
ãæž èŽããããšãããããŸããïŒ é 匵ã£ãŠ
â ããŒã3