The example we are going to see explains how to transfer the Master records to a Master file destination & detail records to a detail file destination from a single flat file source, using SQL Server Integration Services.
We have used a flat file as a source file and created some records as Master and Detail records in the file. In this source file, ‘M’ indicates it is a master record, and ‘D’ indicates it is a detail record
In the Flat File Connection Manager Editor of SQL Server, first, we should add the new flat file Connection. Next, we should specify the name of the connection manager and also the row delimiter for the source file. The flat file source will read data from the text file, which can be in a delimited, fixed width or mixed format. To learn more, follow the link Flat File Connection Manager Editor
Next, select the Dataflow Task in our package and add the Flat file source. Double click the flat file source and then select the columns. Using the Columns node, map the externals columns to output columns. The output column provides unique names for each output column.
Now, we have to use the Conditional Split Transformation Editor to define the logic to split the data. The Conditional split Transformation is used to split the source depending on the content of the data. In this example, the lines end with ‘M’ will be created as Master records, and the lines end with ‘D’ will be created as Detail records.
To learn more about Conditional Split Transformation, please go through the link Conditional Split Transformation.
So, using Conditional Split Transformation editor, we have defined the split logic. Next, we have to create a Script component for data transformation. If we use Script component for source, it will support multiple outputs. If we use Script Component for Transformation, It will support one input and multiple outputs, but the Script component for Destination will support one input.
Based on our example here, we have to use the Script Component for source, so it can support multiple outputs. To know more about Script Transformation using SSIS.
After finishing the above coding, we should run the package. Now, it shows the boxes in green color to indicate the package execution is successful. From the source file we have moved four records to the destination. Based on the Split condition, one record is created as a Master record, and three records are created as Detail record.
The SQL Server Integration Service is an effective tool for splitting and transferring a single flat file into multiple database files using conditions.
