To create a system to efficiently export table data to csv files which is resistent to changes in the file structure.
Also to minimise the changes needed for a new file format / columns - this should be controlled by table data.

SSIS is an efficient method of exporting data.

It is a product that has become to be considered a part of sql server and a developer would be expected to have skills in the product.

This method creates a configured package to export the data at run time.
As such it will prevent issues with process control and give a consistent approach to file import including logging and error handling.
Release control does not need to consider SSIS packages apart from the initial release.
Note: the same product can be easily altered to export from any source and to any destination.
It will mean that you never need to create another file export package.

This article is split into several parts as a development tutorial but the final product can be used and the article treated as documentation.
The objective is to take entries from a table that contains a description of the data to be exported
Use this with a data template table to create the package data to export the data to a file.

Tables

PackageTemplate - Static package data and rows to be replaced
FileType - Definition of the file
FileControl - Files to be imported with FileType_id
DataImport - generic table with 100 columns of varchar(8000)

s_PackageData - Output the package data

Proof of concept

Can a package be created and executed.

Create an export package: text file export
Execute the package to export data
Import the package xml data into a table
Export the package data to a file
Execute the created package to export data

Find the configurable components of a package

Create simple packages and compare for differences that need to be configured
Single text column export
Two text column export

Package template table

Create a simple package with the required export processing

Import package XML data to a single column table
Set file ID
Log result

Import the package to a table
Identify the configurable rows and data items that need to be replaced
Populate a table with replacement rows for each item that needs to be updated
Create a template table with place holders for the replacement items

FileType table

Defines the file types and the column data types to be exported

FileControl table

Defines the files to be exported and references the FileType table.
Allocates a FileID

Export configuration table

For the export define the columns that need to be exported

Package generation

SP to take the template and configuration and output the export package data
Input: file type, file id
Output: package data

Package execution control

Input: FileID
Call package generation SP
Create export package
Execute export package