By: Amar Reddy | Updated: 2017-05-04 | Comments (5) | Related: 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:
and this is the file I want to generate. Each PackageName is the header, followed by the detailed records.
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.
Package Variables
Before adding any tasks into the SSIS package, we need to add two variables of the below types.
- USER::PackageList: This variable is of type Object to store the result set of all the distinct PackageNames from the source table.
- 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.
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.
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.
Foreach Loop Container
Next add a Foreach Loop Container after the Execute SQL Task with a precedence constraint as shown below.
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.
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.
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.
The query parameter needs to be mapped by clicking the Parameters... button and then mapped to the USER::PackageName variable as shown below.
Once the OLEDB source has been configured, we need to add a Flat File Destination as shown below.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Once the package development is complete, we can execute the package.
This should then create a file like below.
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.
About the author
Amarendra Reddy Thummeti is a Senior Business Intelligence Engineer who works in Silicon Valley and holds a Master's Degree in Computer Science. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-05-04
