Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Generate text Output file with a Header and Details Format using SQL Server Integration Services


By:   |   Read Comments (3)   |   Related Tips: More > Integration Services Development

Problem

I need to generate a text file using SQL Server Integration Services (SSIS) that has multiple Header Groups and Detail rows for each header group. This tip explains the steps to generate a single flat file with multiple header and detail records.

Here is the data that I am going to use from the SSISPackageErrorLog table:

Source Table Data - Description: Query to see the Source Table Data

and this is the file I want to generate.  Each PackageName is the header, followed by the detailed records.

Desired OutPut file - Description: Output text file should like this
Solution

For this solution, I am using the SSISPackageErrorLog table and SQL Server Data Tools 2012 to develop this SSIS package. I am going to use the below query to get a list of distinct PackageNames that are part of this table. The idea is to fetch all the distinct header items (PackageNames) and then loop through each header record to populate the detail data for that header record into the same destination file.

PackageNames - Description: Query to get the Distint package names
from source table

Package Variables

Before adding any tasks into the SSIS package, we need to add two variables of the below types.

  1. USER::PackageList: This variable is of type Object to store the result set of all the distinct PackageNames from the source table.
  2. USER::PackageName: This variable is of type String to store the PackageName while looping through header records, to be used in a Foreach Loop Container.
SSISVariables - Description: The 2 variables should be created like this

Execute SQL Task

The first task in the package will be an Execute SQL Task using the below query in the SQLStatement section and selecting the Full result set under the ResultSet section.

SELECT distinct [PackageName]
FROM [ETL].[dbo].[SSISPackageErrorLog]

The Execute SQL Task component needs to be configured as shown in the below image.

Execute SQL Task - Description: Execute SQL task editor will be configured in this manner

Then map the FullResultSet into object variable USER::PakageList on the Result Set page.  The Result Name should be 0 as this is the table object value.

Execute SQL task result  - Description: The result set for the Query is mapped to this variable

Foreach Loop Container

Next add a Foreach Loop Container after the Execute SQL Task with a precedence constraint as shown below.

Foreach loop - Description: Foreach loop container needs to added

The Foreach Loop Container needs to be configured to use the Foreach ADO Enumerator for the Enumerator and the ADO object source variable should be USER::PackageList which contains the list of distinct PackageNames.

Configure Foreach loop - Description: Configuring Foreach loop container to select enurerator and ADO Object Source varaible.

Then go to Variable Mappings and map the USER::PackageName variable to index 0. This will hold the PackageName for the current record as we loop through the records. 

Foreach loop mapping - Description: Results from loop needs to be mapped to PacakgeName variable

Data Flow Task

The next step is to add the first Data Flow Task in the Foreach Loop Container named Load Header Record.  This task is to load the header record. In order to configure this Data Flow Task, we need to add an OLEDB Source and configure the connection to connect to the ETL database and entering the query in the SQL command text where we can pass a parameter through the query as shown in the below image.

The idea is to loop through each header record, where it will pick up the PackageName value in USER::PackageList object variable and then pass that value to the query parameter.

OLEDB Source - Description: Configuring OLEDB Source with Query

The query parameter needs to be mapped by clicking the Parameters... button and then mapped to the USER::PackageName variable as shown below.

Query paramter - Description: Paramtert needs to be mapped to Query

Once the OLEDB source has been configured, we need to add a Flat File Destination as shown below.

FlatFile Destination - Description: Need to add FlatFile Destination for the Source

The most important part in this whole solution is to configure the Flat File Destination correctly. As shown in the below image the Destination File path has to be selected. DestFile.txt is the destination file name in this example, since we do not what column names in our output file we can uncheck the Column names in first data row option as shown below.

FlatFile Connection Manger for header - Description: Falt file connection manager needs to be configured for header source

The next step is to configure the connection metadata properties. Go to the Advanced page properties and specify the correct column width in this case I have select 255 characters of type DT_STR and click OK.

Flatfile Connection Metadata - Description: MetaData needs to be configured in Advanced Tab

Then double click on the Flat File Destination and uncheck Overwrite data in the file as shown in the below image. This is important since we are looping through the records and we do not want to create a new file each time. The data has to be appended to the existing file.

OverWrite Data option - Description: Overwrite Data Option needs to be unchecked.


Data Flow Task 2

Now we need to add a second Data Flow Task under the Header Data Flow Task. This Data Flow Task will be generate the detail records and append to the results to the same file after the header row.

Details Data flow task - Description: New Data flow task added for detais rows

In order to configure the second Data Flow Task, we will need another query which will return the detail records for the specific header record. To do this, I am using the below query with a parameter as shown below.

OLEDB Source in Details DFT - Description: OLEDB and Query mapping in Details Data flow task

When we click the Parameters… button it will open a window in which we need to map the parameter values which will be the same as Header query parameter mapping which is the USER::PackageName as shown below.

Query paramerts for Detail records - Description: The Query paramter needs to be mapped with a variable .

Similar to first Data Flow Task, similar steps have to be followed. Select the same Destination File that we selected for the Header connection manger and make sure the Column names in the first data row option is unchecked as shown in the below image.

Flatfile connection for Details file - Description: Flatfile connection manager needs to be configured for Details file

Configuring the column properties is very important.  We need to make the first column PackageLogID match the column type from the Header row which should be column width 255 and DataType DT_STR and click OK.

Details Flatfile Metadata - Description: Metadate for Details Flatfile connection manger needs to be configured.

Now double click on the detail Flat File Destination and on the Connection Manager page uncheck the Overwrite data in the file like we did for the header record. Then click on Mappings to check the column mappings as shown below.

Dest Column Mapping - Description: Destination file for details need to mapped to soruce Columns

Once the package development is complete, we can execute the package.

Control Flow - Description: Control Flow of Full Package was shown here

This should then create a file like below.

Desired OutPut file - Description: Output text file should like this

Note: This process has a fixed filename for the destination.  You could further develop this process to create a dynamic file name including the date and time stamp.

Summary

As you can see, we can easily generate these kind of header and detail format files using SSIS packages, rather than writing complex C# or VB.Net code in a Script Task.

Next Steps
  • When you have a similar requirement you can follow this step-by-step approach and try to develop this using the built-in SSIS tasks.


Last Update:






About the author
MSSQLTips author Amar Reddy Amarendra Reddy Thummeti is a Senior Business Intelligence Engineer who works in Silicon Valley and holds a Master's Degree in Computer Science.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, June 14, 2017 - 1:14:23 AM - matt Back To Top

Thanks Amarendra for this, it helped me on working on a similar kind.


Monday, May 22, 2017 - 6:50:51 PM - Rob Back To Top

This was really helpful, able to reproduce the files easily with these instructions.I was wondering it could be the same even if there is a requirement for file with footer as well like totals and stuff. Thanks for this.


Friday, May 05, 2017 - 2:25:34 AM - Marty Back To Top

Nice article..cool stuff.


Learn more about SQL Server tools