In completing Exercise 2 - Create Efficient Lookups on page 228 of the "Training Kit (Exam 70-463): Implementing a Data Warehouse" book, I encountered an error.
Checking on the error:
[Set default values [212]] Error: The "Set default values" failed because truncation occurred, and the truncation row disposition on "Set default values.Inputs[Derived Column Input].Columns[Gender]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Set default values" (212) failed with error code 0xC020902A while processing input "Derived Column Input" (213). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
So the error message complains the data truncation on Gender.
Checking on the derived column for gender - it is 1 byte! But the gender would be 'N/A' if not matching, it needs 3 bytes!
Solution:
There is no an easy way to change the gender to 3 bytes in SSIS. So I change it in SSMS:
ALTER TABLE stg.CustomerInformation
ALTER COLUMN Gender NChar(3)
ALTER TABLE stg.CustomerInformation
ALTER COLUMN MaritalStatus NChar(3)
After these changes, the package runs!
