31 Days of SSIS – Folder Structures (4/31)

by Jason S, posted in SQL Server 2008, SQL Server 2008 R2, SQLServerSyndication, 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:

ssis-026

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?

11 thoughts on “31 Days of SSIS – Folder Structures (4/31)”

  1. Pingback: Wrapping Up 2011 | Strate SQL
  2. Pingback: 31 Days of SSIS - DTSConfig Configuration (19/31) | Strate SQL

  3. Is this meant to be for deployment layouts or both deployment and development? If only for deployment, how do you suggest to develop packages that call child packages? Should they point to the local development copy or the deployment copy? How would you setup a configuration for development path and deployment path and layout?

    Like

    1. I primarily use this for deployment. In development, I use whichever folder structures they already have available for storing code. When I start testing the packages, even durign development, I'll run the packages through SQL Agent against this folder structure to make certain everything is laid out appropriately.

      Like

      1. Ok, thanks. That brings up another question. If you were to setup a dedicated SSIS server and not install MSSQL, how would you suggest scheduling and managing those schedules? I think the only real way is via Task Scheduler. I'd really like to be able to do this from a SQL Agent Job on the SQL Server but my testing shows that it runs on the SQL Server.

        Like

  4. Pingback: SQL Server Central

  5. I like your recommendations for selecting a standard. But are there reasons to choose msdb instead of a file system for deploying packages?

    Like

  7. Pingback: 31 Days of SSIS – One Variable to Rule Them All (5/31) | Strate SQL
  8. Pingback: 31 Days of SSIS – The Introduction | Strate SQL

  9. Can tell you use an organized layout, and some of the concepts under workfiles I hadn't considered before.

    What do I do differently?
    Well the concept of WorkFiles is outside of the 'SSIS' hierachy, in fact by the time it gets to production on an entirely different drive. One reason is that the WorkFiles lives on whether another developer was to follow me and structure packages differently… or for that matter a different ETL tool was used, or even reverted to importing 'manually'. Second reason is the source control and permissions alluded to. Non-developers in my environment need access to the WorkFiles, on a regular basis to drop off files, and in rarer situations, to see what files have been imported. Confuses me to much to have this mixed in with folders that need source code control.

    Under my equivalant to WorkFiles/Project, there is a 'Samples' folder which contains samples (i.e. templates) of what the files are 'supposed' to look like. Especially with Excel files, i've found a groove where if the package uses a file under 'WorkFiles/Project/Samples' at design, then have it loop through 'WorkFiles/Project' at execute time. This can be challenging though if the 'Samples' get out of sync with the package.

    Like

Comments are closed.