31 Days of SSIS

Hopefully by now you’ve read the introductory post for the 31 Days of SSIS series that I am writing.  Today we will talk about something completely fuzzy and outside of the development environment.  We’re talking about folder structures!  Wahoo!

I have often needed to consult with clients on how to setup the SSIS environment.  For some clients, they are just getting into SSIS and haven’t had the opportunity to consider how to setup their environment.  Others have been at it a while and haven’t stopped to think out how they want the environment to function.

Today we will be going through some of the things that I do to setup the SSIS environment.  These will all have a focus outside of SSIS packages and the service.  They will all deal with folder structures.  These folders are where the SSIS packages, configuration files, and files that are imported and exported are stored.

The Folder Structure

The SSIS folder structure is usually contained under a single root directory.  For simplicity, I often name this folder SSIS.  Yeah, I’m creative.  Off of the root folder, I’ll add folders for Packages and WorkFiles.

The Packages folder often contains many folders underneath it.  These folders are for all of the SSIS projects that have been deployed.  For every project there will be a Project folder.  The Project folder may have other folder beneath them, such as a Configurations folder.

The WorkFiles folder will be similar to the Packages folder in the sense that there will be Project folders below it.  The Project folders will have other folders such as Archive, Logs, Response, or Temporary.

If you are good at visualizing, the folder structure will look like this diagram:

The Folder Purpose

Now that this has been described, less put some definition and understanding behind these folders.

• Packages: Everything under the Packages folder is considered “code”.  Because of this status these folders are under change management control.  People who are not the gatekeepers should only be able to browse these folders, if that.  These folders should be backed up on a regular basis.
• Project (under Packages): These folders contain all of the SSIS package files (dtsx) and configuration files (dtsconfig) for each deployed project.  A project could be a single SSIS project or a collections of projects based on a client’s business.  Typically, I store SSIS packages outside of the MSDB database in the file system.  This has worked best for change management and with security in the environments.
• WorkFiles: Everything under the WorkFiles folder is considered temporary.  These folders will store files to be imported, exported, or log files.  Business users are often granted access to these folders depending on their projects and whether they need to pick-up, drop-off, or review files.
• Project (under WorkFiles): These folders contain project, same project definition from above, related files.  They can be logs, data received for import, or data exported.
• Archive (Workfiles –> Project): After a file has been imported by an SSIS package, the imported file is dated and placed in this folder.
• Logs (Workfiles –> Project): Log files generated from the SSIS package and the SQL Agent job steps are placed here.  These need to be in a place where more than those running the jobs can find them.
• Response (Workfiles –> Project): When the SSIS package has data that cannot be imported and is rejected for some reason – that data will go in this folder.
• Temporary (Workfiles –> Project): This folder stores the raw files that are generated during an SSIS package execution.  This keeps them out of the way and is an easy place to just delete everything in the unlikely scenario that there are space constraints.

Folder Structure Wrap-Up

There are countless other folders that you could add, but this should cover a lot of the basics.  These have been most of the basics that I try to use.

This has been about how I do things.  The tips and tricks I’ve learned to keep things going and to reduce headaches.  These aren’t necessarily the right answers, but they are my answers.

My question to you is – do you agree?  What do you do differently?

Related posts:

1. 31 Days of SSIS – Raw Files Are Awesome (1/31)
2. 31 Days of SSIS – The Introduction
3. 31 Days of SSIS – Using Breakpoints (2/31)