SSIS Data Flow Advanced Editor

Nearly all the tasks or data flow components (transforms) in SSIS packages have their own editors or custom user interfaces to set their specific properties or fields. This editor can be opened by right-clicking the item and select "Edit...". Most of the transforms also have advanced editors which can be opened by right-clicking the item and select "Show Advanced Editor...". The Advanced Editor is standardized as following 5 tabs and one transform can have up to 4 of them.

  • Connection Managers
  • Component Properties
  • Column Mappings
  • Input Columns
  • Input and Output Properties

To understand all of them, we'll check the editor of the transforms which were created in the Data Conversion section.

  1. Open the package DataConversion.dtsx in the project LeanSSIS2 in Visual Studio.

  2. Click Data Flow tab and right-click "Excel Source" and choose "Show Advanced Editor..." to open the editor.
  3. The first Connection Managers will be shown up.

    [Advanced Editor Connection Managers]

    In this tab, the connection managers which will be used in the transform are set here. In this example, it was set as Excel Connection Manager.

  4. Click "Component Properties" tab.
  5. [Advanced Editor Component Properties]

    As you see, the Component Properties are the same as that in the properties window outside the editor. The OpenRowset property is the table or view level setting and it was set as Sheet1$ in this example.

  6. Click "Column Mappings" tab.
  7. [Advanced Editor Column Mappings]

    The column mappings between the input columns and output columns can be set here.

  8. Click "Input and Output Properties" tab. Then click "Book Name" in External Columns in Excel Source Output and change the Data Type to string on the right side.
  9. [Input and Output Properties]

    There are 2 outputs in this source transform. One is called Excel Source Output which is normal output and the other is Excel Source Error Output which is the error output if an error occurs. In the Excel Source Output, there are 2 sets of columns. The External Columns stand for the columns definition in the data source and Output Columns stand for the output of the transform. The schema data of the External Columns cannot be changed actually if the data source has not been changed. In this example, we changed the Data Type of "Book Name" column on purpose and see what will be happened.

    Please be noted, 2 columns ErrorCode and ErrorColumn are added in the Error Output Columns.

  10. Click "OK" to save the change and reopen the editor and go to the last tab.
  11. [External Columns cannot be changed]

    You will find the data type was changed back automatically.

  12. Click "Cancel" to close the editor. Right-click "Data Conversion" to open its Advanced Editor.
  13. The default tab is Component Properties which was already be mentioned above.

  14. Click "Input Columns" and change the "Output Alias" to "Book Name2".
  15. [Advanced Editor Input Columns]

    In this tab, the converted input column was selected and shown up. The output alias of the input column was changed on purpose.

  16. Click "Input and Output Properties" tab.
  17. [Input Output Alias Names]

    You can see the alias name you just typed is actually input alias name which is different from that of output alias name.

  18. Click "Cancel" button to close the advanced editor.

In summary, The advanced Editor provides a way to access the low-level properties of data flow components and a detailed data structure in both input and output columns.