I have an Excel file with first_name, last_name, location as source. In destination I've first_name, last_name, location and status
In the SSIS ETL process, I need to pass the default value "Current" for the status column. How do I configure this in a Derived Column Transformation when that status column is not available in the source?
In the Derived Column transformation, you can select 'Add as new column' in the second column (named "Derived column"). This setting determines whether an existing column is overridden or a new one is added.
So, enter "Status" as the Derived Column name, set Derived Column to Add as new column and enter "Current" in the Expression field. SSIS will automatically assign a data type to it. If you specifically need NVARCHAR instead of VARCHAR, you can change the expression to
(DT_WSTR, 7)"Current"
This adds a cast to NVarchar.
