Feb 122013
 

As part of a recent project, I needed to check if a file existed before starting an SSIS package to import that file.  The catch was that I did not know what time time file was going to be placed on the file system.  If the SSIS package runs and the file does not exist yet, the package will fail.

You can create a script task component within SSIS to check for a file and then sleep, however several sources said that this could spike the processor so I deceded to go a different route.  To solve the problem, I wrote a quick little powershell script to check if the file exists and then wait in a loop before starting the SSIS package.

I created a SQL agent job with 2 steps.  The job runs daily at 1:00 AM. The job has a status of Executing on Step 1 until the file exists.

Step 1: powershell script to check for the file (see below)

Step 2: Execute SSIS package task.

The file that I am looking for in this example is: C:\test\ImportFolder\fileToCheck_20130212.txt
You will notice that today’s date is also appended to the end of the file name.  I built the file name out dynamically along with the current date stamp.

$dt = Get-Date -format yyyyMMdd
$path = 'C:\test\ImporFolder\'
$theFile = $path + 'fileToCheck_' + $dt +'.txt'

#  $theFile variable will contain:  C:\test\ImportFolder\fileToCheck_20130212.txt
While (1 -eq 1) {
	IF (Test-Path $theFile) {
		#file exists. break loop
		break
	}
	#sleep for 60 seconds, then check again
	Start-Sleep -s 60
}

This post is part of the blogging phenomenon known as TSQL Tuesday. This month’s blog party is hosted by Wayne Sheffield, who is writing a series of blog posts in the month of February all about powershell..

Jan 162013
 
Step 5

I’ve started working with SSIS a lot more lately.  I am going to attempt to document here some of the quirks that took me a little while to figure out along the way.

In this tidbit, I have a multi-step SSIS package that needs to be run on a daily basis.  However, one step of the process should only be run on a specific day of the week (lets say it should only be run on Tuesday).

My screenshots are all taken in Visual Studio 2010.  The process is the same for 2008 R2.

The process:

  1. Create the necessary steps.  For this simple example, I created 3 Execute SQL tasks.  They each run the query: “SELECT 1”.
  2. Drag the green arrows to link each of the tasks in order.  By default, the links will all be success constraints.
  3. Double click on the line between Task 1 and Task 2.  This will open the Precedence Constraint Editor.
    1. Change Evaluation operation to: “Expression and Constraint”
    2. In the expression block, type: “DATEPART( “dw”, getdate()) ==3″
      • Using the DATEPART(“dw” ) function, Sunday=1 and Saturday=7.  Since we only want Tuesday, we choose ==3.
    3. Press OK
    4. You will notice that the line between Task 1 and Task 2 now has an “fx” symbol on top of it.
  4. At this point, the package will run and Task 2 will only run on the specified day.  However there is an issue with the current setup: Task 3 will begin executing as soon as Task 1 completes.  It will not wait  until Task 2 completes on Tuesdays.  We need to modify that link as well to create a fork in the path.
  5. Double click on the link between Task 1 and Task 3.
    1. Change Evaluation operation to: “Expression and Constraint”
    2. In the expression block, type: “DATEPART( “dw”, getdate()) !=3″
    3. Under multiple constraints, change to the option: “Logical OR. One constraint must evaluate to True”
      • Changing to Logical OR is required.  Since we created the fork at Task 1, only one of the two lines going into Task 3 will evaluate as successful completion.
    4. Press OK
    5. You will notice that the line between Task 1 and Task 3 turns into a dashed line and it also has an “fx” symbol on top of it.
  6. You can now run your package to test that the steps work properly.  To test the design, I changed my formula’s ==3 and !=3 to a different day of the week to make sure that Task 2 got bypassed correctly on its off day.