Exporting Multi-Record Formated Flat File with SSIS
I recently had to come up with an easy way to create a multi record formatted file as a destination from and SSIS package. While I found a number of helpful tips on how to read from such a file I did not find anything on how to create such a file. I decided to see if I could do this utilizing standard SSIS objects with as little code as possible. I will walk you through the process utilizing the Adventure Works database as a source ( The sample database can be found at codeplex: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004)
The first thing I want to cover is what I a mean by a multi-record formatted file. This is a flat file that is delimited by some character in this case we will use a comma and the number of columns changes from one record type to the next. In this example I will have a file that has 2 distinct record formats. The first record format is the header record which has 4 columns from the salesOrderHeader table from the Adventure Works database. SalesOrderID, RecordType, OrderDate, and CustomerID. The second record type in this file is from the salesOrderDetail table and it contains 6 data elements; SalesOrderID, RecordType, ProductID, OrderQty, UnitPrice,and LineTotal. The two record type values are A_SH for the sales header record format, and B_SD for the sales detail record format. Below I will step through the process to creating this type of file as easily and quickly as I know how.
Step 1 is to get the first data set required; I choose to get the Header data first. This data is sorted by the SQL server for the merger join later.
Step 2 is to get the second data set, this is also sorted by the SQL Server for use in the merge later.
Step 3 is to add the derived column "DataColumn". This is where all the real work happens. What you do is put all of the non sort key data into a single row that is exported as a string (This can be any type of string). There is no reason to put the key data into this column as it will be used in the merge to give you the header/detail/detail/detail output you are looking for. All of the data you place in the derived column needs to be seperated by the column delimiter you choose for your export file, in this case a ",". The header and detail data sources are required to have a key row in common in this case SalesOrderID, this column is sorted first then the RecordType column will be sorted, where A_SH will come out first then A_SD(More on this later)
Step 4 is very similar to step 3 except you are working on the second data set.
Step 5 is the merge step, join the two data streams on both the SalesID and the RecordType id, notice the sort order. This will give you all of the SalesID from both the header and detail data streams together ordered by the RecordType, giving you the Header/Detail/Detail/Detail layout required by this type of file layout.
That's really it, the last step is just to stuff this data into a flat file that has the same column delimiter as you chose in the derived column tasks early in the pipeline.
Execution Results:
More can be added onto this package to give a header record or end record with row counts, execution date, or any other meta data required by the system reading this file.
Thats it, short and sweet. I'll try to the example package up on the site sometime soon.