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
- Draft the initial design of your package using ER/Studio or some other UML modeling program.
- Create your solution, Name your package according to the defined standards at your organization. Click here to view our list of standards.
- Define the variables listed above in section:Variables required by solution
- 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.
- Draft the initial SSIS package design using the IDE. It may look something like the example below.
How will we handle execution of the package?
Here are some of the common ways to execute SSIS packages.
- SQL Agent Scheduled job: SSIS job type
- SQL Agent Scheduled job: CMD line
- 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
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.
To create the desired effect described above, double click on the precedence constraint and it will load the constraint editor. Select “Expression” as the evaluated operation and set the expressions as shown below