The title might be a little miss leading. “DBA Perspective” is solely my perspective in dealing with SSIS up to this point.

The list that I have tallied on how to proceed with configurations are as follows:

• XML Configurations – XML file that contains the SSIS configurations (can include multiples)
• Environment Variable – environment variable that contains configurations
• Registry Entry
• Parent Package Variable – This config type is more often used to update child packages
• SQL Server Table – a table inside SQL Server contains the configurations (can include multiples)

Now, the irony if you will, is that in my specific case we currently have a number of variations of packages that use many different methods – This to me = monstrosity

The two styles that seem the easiest for me are the XML Configurations or the SQL Server Table configuration. The preferred method of my choice is to store them all in a SQL table.

I have three tables that I use:

• SSIS_Configurations
• SSIS_File_Spec_Configuration
• SSIS_Log_Details

I’ll try to break down the schematics of each table:

SSIS_Configurations

• ConfigurationFilter – Name of process
• ConfiguredValue – Server Instance
• PackagePath – package connections
• ConfiguredValueType – self explanatory (string, etc.)

SSIS_File_Spec_Configuration

• Pkey – identifies multiple entries for one package
• JobIdentifier – name of package
• Apptype – what application is it a part of
• InputFileType – .txt, .csv, etc.
• InputFilePathType – is it a share, sql server, etc.
• InputFilePath – location of where the input file path is coming from
• SProcName – name of procedure utilized
• OutputFileType – .xlsx, .csv, etc.
• OutputFilePath – location of where the output file will be sent
• InputFileArchivePath – Archive location for the input file

SSIS_Log_Details

• ExecStartTime – start time of the SSIS run
• ExecEndTime – end time of the execution
• TaskName – name of Task being executed
• Status – what is the status of the package
• ErrorDescription – what was the error the package encountered
• Duration – duration of the package
• PkgName – package name
• SubPkgName – child package
• BatchId – self explanatory

This process has proved helpful for me; each DBA is different though and I can only say to find what works best for you in your particular situation and environment.