Create summary rows with detail rows in SSIS

Question

I have a requirement to add small summary at the top of a CSV file. I then need to present the detailed line data directly beneath it.

This is what I'm trying to produce:

I'm trying to do this in SSIS and need to output it in CSV format.

My data source is SQL Server.

I've thought about using a UNION ALL to achieve this but I also need to present the column headings in the file as per line 6.

Any ideas on how I can do this? Thanks

Answer 1

You're in luck, this is a rare case of SSIS being able to make a mixed output file.

You even have 3, THREE!!!!, different options here

Option 1

Double data flow approach. The trick here is to create two different Flat File Connection managers that point to the same physical file on disk. The Header definition will go first and populate the top N rows with no header row written. The Body definition will contain the expected data with a header row.

DFT Header

We're going to use two Data Flows to make this happen.

I have a source that generates key/value pairs like

SELECT
*
FROM
(
VALUES ('agent_name', '123456')
,('bordereau_year', '2023')
,('bordereau_month', '1')
)D(ckey,cvalue);

I configured the Flat File Destination as a Overwrite

Configured the flat file connection manager with the Column Names in the first row.

DFT Body

Source data looks like

SELECT
*
FROM
(
VALUES
(NEWID(), 'ABC',NULL,'B123')
,(NEWID(), 'BCA',NULL,'B234')
)D(uuid,sub_agent_name,broker_reference,policy);

I configured the flat file destination with a

The Flat File Destination is spec'ed as

Run the package and get an output like

agent_name,123456
bordereau_year,2023
bordereau_month,1
uuid,sub_agent_name,broker_reference,policy
{7A769E78-4D08-43E1-9710-799FA9F93A08},ABC,,B123
{D8FA71FB-748A-4FF3-8AA4-902F48F4992B},BCA,,B234

Option 2

This will replace the first Data flow and take advantage of a property of the Flat File Connection Manager's Header property. Here, I created a Variable called @[User::HeaderData] and in the Expression for it, I used

"agent_name,123456
bordereau_year,2023
bordereau_month,1"

You do not need to use an Expression but if it's static data, the newline will get stripped when you paste into the Value. It will be preserved in a Expression.

If the content needs to be dynamic, then you'd need to do something to build it and then assign to the Value property.

Oh, and if you go this route, you'll want to check the "Column name in the first row" on the body.

Option 3

This approach is a different implementation for Option 1.

Do whatever you do to get the data and then have a C# task write the data to the file.

System.IO.File.WriteAllText("C:\\ssisdata\\output\so_76908880.csv", "Content here");