Problem: The risk department notice an inaccuracy in your data-warehouse records.
Requirement/s: is to update all the records persisting to the flow of changes through time.
No Actual Credit Card Number is used in this example.
SELECT s.CreditCardNumber, s.NewDimLocationID as NewLocationID, d.Dimlocationid as OldLocationID, d.ReportingDate
FROM SourceUpdate AS s LEFT OUTER JOIN DestinationUpdate AS d
ON d.Creditcardnumber = s.CreditCardNumber
WHERE (s.CreditCardNumber = ************9867)
AND (d.ReportingDate BETWEEN '2011-06-06' AND '2011-06-30')
ORDER BY s.CreditCardNumber, d.ReportingDate
Requirement/s: is to update all the records persisting to the flow of changes through time.
No Actual Credit Card Number is used in this example.
SELECT s.CreditCardNumber, s.NewDimLocationID as NewLocationID, d.Dimlocationid as OldLocationID, d.ReportingDate
FROM SourceUpdate AS s LEFT OUTER JOIN DestinationUpdate AS d
ON d.Creditcardnumber = s.CreditCardNumber
WHERE (s.CreditCardNumber = ************9867)
AND (d.ReportingDate BETWEEN '2011-06-06' AND '2011-06-30')
ORDER BY s.CreditCardNumber, d.ReportingDate
The highlighted OldLocationID are the one that needs to be update.
Your SSIS package should be look like this:
1. Your Control Flow: (the script is for my personal testing. Just ignore it).
Your SSIS package should be look like this:
1. Your Control Flow: (the script is for my personal testing. Just ignore it).
- Drag Execute SQL TASK to your Control Flow.
General:
Result Set: Full result set
Connection: Configure your connection
SQLStatemen:
SELECT ReportingDate, CreditCardNumber, DimLocationID
FROM SourceUpdate
WHERE (ReportingDate BETWEEN ? AND ?)
General:
Result Set: Full result set
Connection: Configure your connection
SQLStatemen:
SELECT ReportingDate, CreditCardNumber, DimLocationID
FROM SourceUpdate
WHERE (ReportingDate BETWEEN ? AND ?)
- Drag Foreach Loop Container to your Control Flow.
Collection:
Enumerator: Foreach ADO Enumerator
ADO object source variable: ObjSourceUpdate
Enumeration mode: Rows in the first table
Collection:
Enumerator: Foreach ADO Enumerator
ADO object source variable: ObjSourceUpdate
Enumeration mode: Rows in the first table
-Drag a Data Flow Task
2. Your Data Flow: (the derive column and row count transformation is not crucial you can remove it if you want)
2. Your Data Flow: (the derive column and row count transformation is not crucial you can remove it if you want)
-Drag and drop your OLE DB Source Data Flow
OLE DB Source Editor:
OLEDB Connection Manager: Select your Connection
SQL Command text:
SELECT ReportingDate, Creditcardnumber, Dimlocationid
FROM DestinationUpdate
WHERE (ReportingDate BETWEEN ? AND ?)
AND CreditCardNumber = ?
ORDER BY ReportingDate
Parameter Mappings:
OLEDB Connection Manager: Select your Connection
SQL Command text:
SELECT ReportingDate, Creditcardnumber, Dimlocationid
FROM DestinationUpdate
WHERE (ReportingDate BETWEEN ? AND ?)
AND CreditCardNumber = ?
ORDER BY ReportingDate
Parameter Mappings:
Derived Column Transformation Editor:
Derived Column Name: DERLocationID
Derived Column: <add as new column>
Expression: Creditcardnumber == @[User::CreditCardNumber] && ReportingDate >= @[User::ReportingDate] && OldLocationid != @[User::NewLocationID] ? @[User::NewLocationID] : OldLocationid
Data Type: four-byte signed integer [DT_I4]
Conditional Split Transformation Editor:
Derived Column Name: DERLocationID
Derived Column: <add as new column>
Expression: Creditcardnumber == @[User::CreditCardNumber] && ReportingDate >= @[User::ReportingDate] && OldLocationid != @[User::NewLocationID] ? @[User::NewLocationID] : OldLocationid
Data Type: four-byte signed integer [DT_I4]
Conditional Split Transformation Editor:
Component Properties:
Connection Managers: Configure your connection
SQ Command:
UPDATE FactFinancialDaily
SET Dimlocationid = ?
WHERE CreditCardNumber = ?
AND ReportingDate = ?
Column Mappings:
Connection Managers: Configure your connection
SQ Command:
UPDATE FactFinancialDaily
SET Dimlocationid = ?
WHERE CreditCardNumber = ?
AND ReportingDate = ?
Column Mappings:
Row Count is just my counter ignore it.