How to automatically sync SSIS columns in a Data Flow? MS BI

Learn to synchronise source and target columns of an SSIS component automatically with Visual Studio.

That is, how to automatically sync SSIS source and target columns, based on their name? This trick allows developing SSIS packages more efficiently. There are 2 options to link the columns in an SSIS component. The first option is manual mapping. The second option is to connect them automatically via the Visual Studio graphic interface.

Indeed, column management requires a major prerequisite. Prepare all target column names with the same names as the source columns. To link a large number of columns quickly and avoid wasting time with manual operations, follow the steps below.

1. Create the source table from SSMS

Simply run the SQL code below to create an empty table with 20 columns.

CREATE TABLE [dbo].[Source_Table]
(
    [Column_01] [nvarchar](20) NULL,
    [Column_02] [nvarchar](20) NULL,
    [Column_03] [nvarchar](20) NULL,
    [Column_04] [nvarchar](20) NULL,
    [Column_05] [nvarchar](20) NULL,
    [Column_06] [nvarchar](20) NULL,
    [Column_07] [nvarchar](20) NULL,
    [Column_08] [nvarchar](20) NULL,
    [Column_09] [nvarchar](20) NULL,
    [Column_10] [nvarchar](20) NULL,
    [Column_11] [nvarchar](20) NULL,
    [Column_12] [nvarchar](20) NULL,
    [Column_13] [nvarchar](20) NULL,
    [Column_14] [nvarchar](20) NULL,
    [Column_15] [nvarchar](20) NULL,
    [Column_16] [nvarchar](20) NULL,
    [Column_17] [nvarchar](20) NULL,
    [Column_18] [nvarchar](20) NULL,
    [Column_19] [nvarchar](20) NULL,
    [Column_20] [nvarchar](20) NULL
);

2. Create the target table with the same structure

CREATE TABLE [dbo].[Target_Table]
(
    [Column_01] [nvarchar](20) NULL,
    [Column_02] [nvarchar](20) NULL,
    [Column_03] [nvarchar](20) NULL,
    [Column_04] [nvarchar](20) NULL,
    [Column_05] [nvarchar](20) NULL,
    [Column_06] [nvarchar](20) NULL,
    [Column_07] [nvarchar](20) NULL,
    [Column_08] [nvarchar](20) NULL,
    [Column_09] [nvarchar](20) NULL,
    [Column_10] [nvarchar](20) NULL,
    [Column_11] [nvarchar](20) NULL,
    [Column_12] [nvarchar](20) NULL,
    [Column_13] [nvarchar](20) NULL,
    [Column_14] [nvarchar](20) NULL,
    [Column_15] [nvarchar](20) NULL,
    [Column_16] [nvarchar](20) NULL,
    [Column_17] [nvarchar](20) NULL,
    [Column_18] [nvarchar](20) NULL,
    [Column_19] [nvarchar](20) NULL,
    [Column_20] [nvarchar](20) NULL
);

How to manage column names to be automatically synced with SSIS?

This solution provides a significant time saving as it also limits manual errors and thus regressions. To do this, simply create a connection and an SSIS data flow. It can be an OLE DB destination on an SQL Server database, for example. The columns sync automatically upon the first connection. However, after a modification, the updated columns are no longer mapped with the destination ones. The trick is to name the target columns the same as the sources. Indeed, the link between the columns in SSIS is based on the column names.

3. Open the OLE DB Destination component from SSIS

By default, the columns with the same names are synced, but in case of a name change, it is possible to automatically link the source and target columns.

4. Right-click and select the option to automatically sync SSIS columns

Right-click in the mapping window of the component to be synchronised. Then click on Map items by matching names to link them automatically, as below.

*Option for automatic column mapping in SSIS*

5. The SSIS source and target columns with the same name are mapped.

All the columns are now linked.

*Synchronise SSIS Columns Automatically from a Data Flow*

This option connects the source and destination columns in a second. Even after a possible modification of the characteristics of the source fields feeding the component. Indeed, naming the source and target columns exactly the same way allows for automatic mapping. The default mapping of columns in SSIS is based on their name.

Conclusion on automatic syncing of SSIS columns

Automatic sync of columns with SSIS offers a very useful feature for data mapping from one source to another. When you use this feature, all source columns bearing the same name as the target are automatically mapped. This means that SSIS identifies and connects these columns in correspondence with their name.

This characteristic greatly reduces the time needed to manually set up each mapping, especially when you are working with large data sets with a large number of columns. Therefore, automatic mapping is a key function for improving efficiency and accuracy when handling data with SSIS.

Finally, to continue learning about data management with MS BI, here’s how to export a table to an Excel file using a package.