SSIS How to Map Hundreds of Derived Columns

Question

I have a table that needs to map 104 columns and convert from Unicode to non-Unicode, etc as (DT_STR,10,1252)[ColumnName] within the derived column editor in SSIS.

I cannot simply CAST from the SQL statement given I am ingesting from CDC Control task in SSIS and you have no opportunity to CAST outside of the derived column task.

Given I have over 100 columns to manually map, is there a way to dynamically do this in SSIS instead of going through each and every column manually?

Answer 1

I don't think there is a way to do that from Visual studio, but you can automate the package creation process using one of the following approaches:

1) Business Intelligence Markup Language:

• BIML

2) Using C# / VB.net DTS Wrapper classes

• .Net DTS Wrapper Classes

3) C# EzApi library

• EzApi library

---

Similar posts:

• How to manage SSIS script component output columns and its properties programmatically
• How to Map Input and Output Columns dynamically in SSIS?