SSIS Loop Through & Process Excel files

Processing files in a shared directory, local directory or FTP site is a common requirement.  Although there are many possible approaches, the one that has proven to be the best for method for ongoing maintenance, reporting and SOX/PCI requirements.

 

LEVEL 200 Article

Background & Description

The business requirements of the process should steer the design of your process.  When developing SSIS packages, you should bring up the following points/questions to the primary stake holder,development manager or business analyst.

General Overview & Solution Setup

 

  1. Draft the initial design of your package using ER/Studio or some other UML modeling program.
  2. Create your solution, Name your package according to the defined standards at your organization. Click here to view our list of standards.
  3. Define the variables listed above in section:Variables required by solution

    SetVariables

  4. Define all Connection Managers that will be used by your package.  It is always nice to follow standards, Check this article for a complete list of standards.
  5. Draft the initial SSIS package design using the IDE.  It may look something like the example below.solution_overview
  6. adsfadsf
  7. asdfasdf
  8. asdfasdf

 

How will we handle execution of the package?

Here are some of the common ways to execute SSIS packages.

  1. SQL Agent Scheduled job: SSIS job type
  2. SQL Agent Scheduled job: CMD line
  3. Windows Scheduled Task job

My preferred option is method #1 because of easy integration, scheduling, alerting and output that can be returned to the SQL Agent Job execution history.  We will focus on using Method #1 for the following this article.

Variables required by solution

Name Scope Data Type Value Description
var_CheckForFile [Package_Name] String none  
var_ArchiveFolder [Package_Name] String \someserversomedirectoryArchivesomexlsfilename_02082011_1510.xls  
var_FileEntityName [Package_Name] String \someserversomedirectorysomexlsfilename_02082011_1510.xls  
var_expr_ArchiveNm   String    
         

 

The first item to note is identifying if the file or file(s) to load exist.  There are a few ways to accomplish this task; the one I have found works best is to use the “Foreach Loop Container” in conjunction with a check.  This component will return “Success” if a file does not exist and allow the process to exit gracefully. 

var_CheckForFile

The fx icon denotes a precedence constraint before moving on to  the next SSIS component in the process.

checkIffileExists

To create the desired effect described above, double click on the precedence constraint SSIS_PrecidenceConstraint and it will load the constraint editor.  Select “Expression” as the evaluated operation and set the expressions as shown below

@[User::var_CheckForFile] !="none"

 

image

Leave a Reply