Dynamic data transfer with different data structures

Dynamic data transfer is one of the most challenging tasks in Data Integration tools. This challenge will be much greater when structure of data is different. As you saw in the previous recipe, the SSIS Data Flow Task cannot work with dynamic metadata and this means that if structure of columns (name of columns, data types of columns, and number of columns) changes, the Data Flow Task will fail on validation and stop working.

Good news is that there are alternative ways for data transfer based on source and destination outside the Data Flow Task.

In this recipe, we will see an example of dynamic data transfer from SQL server database to CSV flat files. The source tables may have different data structure. At the end of this recipe, we will talk about other sources and destinations.

Getting ready

Xp_CmdShell should be enabled for this recipe. To enable xp_cmdShell, run the following statements on SSMS on the local default instance of the SQL...