Miscellaneous Ramblings on Hacking

November 1, 2006

SSIS:: Test for Data Files Existence

Filed under: ETL, SQL Server Integration Services, SSIS — Zack Bethem @ 6:15 pm

A common data source for my packages includes flat files. Unlike the scheduling of the master package, however, the flat files may not be staged every single day. Leaving the packages responsible for loading the files as is, however, will result in a false failure. This leads to the following script task in the control flow. The task will read input variables, which the file connection manager should also be using, to test whether the file exists or not. If it does not, it will not execute the subsequent data flow and return a success.

Defined variables:


Defined Data Flow:


Script Task

In the Control Flow, add a Script Task. The task is defined with ReadOnlyVariables of:

The ReadWriteVariables is set to:

Add the following code to the script task:

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
‘ Created: Zack Bethem – AmberLeaf
Public Sub Main()
Dim fileLoc, fileName As String

If Dts.Variables.Contains(“User::ExecDir”) = True AndAlso _
Dts.Variables.Contains(“User::FileName”) = True Then
fileLoc = CStr(Dts.Variables(“User::ExecDir”).Value)
fileName = CStr(Dts.Variables.Item(“User::FileName”).Value)
‘System.Windows.Forms.MessageBox.Show(“FileDir:”+fileLoc+”FileName:” + fileName)

If File.Exists(fileLoc + fileName) Then
Dts.Variables.Item(“User::FileExists”).Value = True
‘System.Windows.Forms.MessageBox.Show(“File exists”)
Dts.Variables.Item(“User::FileExists”).Value = False
‘System.Windows.Forms.MessageBox.Show(“File not exists”)
End If

Dts.TaskResult = Dts.Results.Success
Dts.TaskResult = Dts.Results.Failure
End If

End Sub
End Class

Now your set. The script task will read in the variables User::ExecDir and User::FileName. Using those variables, it will use the System.IO to determine if the file exists or not. If it does, the variable User::FileExist variable is set to True. Otherwise, the variable is set to False.

Conditions in Control Flow

The next step is to define a condition in the control flow. This is done by double clicking on the path line from the Script task to the Data Flow task. This leads to the following dialog:

Here is where you add the conditional logic. The Evaluation Operation is set to ‘Expression’. Using our User::FileExists variable, a check is done to see if the value is equal to True. If it is, the data flow executes. Otherwise, we have our graceful exit and the package is successful.

About these ads


  1. […] Conditional Constraints Published 01 November 06 05:06 PM | mmasson  Here’s a more practical example of using constraints based on variables. I have a package that requires an existing database file to be attached to the local SQL Server. The package is synced out to multiple machines from a source control system and run frequently. I don’t want to manually setup the database on each machine, nor do I want to store the large database file in our source control system… so I decided to make the setup steps part of the package. My first attempt looked something like this: 1 – Initial attempt I created a package with a Script task, two File System tasks, and an Execute SQL task. The Script task branches the execution path based on whether or not the DB file exists on the local file system. This sort of worked – the file was copied, but the “Attach Database” task failed to run. 2 – Execute SQL Task does not execute I soon realized that if you branch your logic like this, you need to set the constraints on the task where your execution merges again (in this case, “Attach Database”) to OR’s instead of AND’s. 3 – Change constraint to logical OR Now when I run, the file is copied, and the database task is executed. On subsequent runs, the file copying tasks are skipped and “Attach Database” is called immediately. 4 – It works… kind of Although I could live with an error appearing the first time the script is run, it threw off some of our scripts, and people who weren’t familiar with the package would always freak out the first time they saw it. Error: 0x4 at Check if DB Files Exist: The Script returned a failure result. Task failed: Check if DB Files Exist While my initial solution (have my Script task print out the message “You can safely ignore the following error” when the file didn’t exist) calmed the masses, it was still a problem for the execution scripts (not to mention just bad form!). This is how I discovered how to create constraints with conditional expressions. I added a new Boolean variable to the package, and modified my script to set its value instead of setting success or failure. 5 – Package variables My script:     Public Sub Main() Dim strFile As String = Dts.Variables(“dbFile”).Value.ToString() Dts.Variables(“varFileExists”).Value = File.Exists(strFile) Dts.TaskResult = Dts.Results.Success     End Sub (Be sure to add the variables you use to the script task to the “ReadOnlyVariables” and “ReadWriteVariables” property of the script task. Also note that you’ll need to import System.IO for the File.Exists() method) I edited the constraints connected to the script task, set “Evaluation operation” to “Expression”, and added an expression based on the varFileExists variable. 6 – Setting expressions This gives us a much cleaner solution! Now we don’t see any errors the first time the package is run, and our files are copied only once. We’re good to go! NOTE: After I finished writing this up, I stopped by the SSIS forums and noticed a post where someone had encountered a similar problem, and wrote a blog entry about it. I thought I’d link it here.   Filed under: SSIS, Constraints, Expressions […]

    Pingback by Discovering SSIS : Conditional Constraints — November 2, 2006 @ 1:15 am

  2. I am glad to see this post. I have exactly similar situation and I have tried the logic that is mentioned here. However I get error when I run this with the following message.

    at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
    at ScriptTask_b731c76f60e0486b99030c7ad62734b6.ScriptMain.Main()

    What could be the reason for this? One change I had to make was to remove the User::ExecDir clause from ReadWrite because it complained the same variable cannot be set as Read only as well as Readwrite.

    Your help is appreciated.

    Comment by aharuray — July 20, 2007 @ 8:17 pm

  3. It’s been awhile since I’ve had this code in use. I’m thinking that there was a mis-type.

    The read-write may possibly need to be User::FileExists
    The read-only would be User::ExecDir and User::FileName

    Comment by Zack — July 20, 2007 @ 9:41 pm

  4. Yup! It works now! You are the best!

    Thanks for saving me loads of work..

    Comment by aharuray — July 24, 2007 @ 2:45 pm

  5. Thanks this was exactly what I was looking for. My issue is it keeps the red x out there saying “The task is configured to pre-compile the script, but binary code is not found” do I need to set precompile to False?

    Comment by javaravyn — December 20, 2007 @ 5:46 pm

  6. EXACTLY the post I was looking for. Nice, simple. Thank you.
    (I wish you would enable anonymous posts – not for cowards – but for people like me who visit several useful blogs (like this one) through the day and wish to leave a grateful note).

    Comment by narayanpavgi — February 5, 2008 @ 9:55 pm

  7. Works great. Wanted to know if I can tweak this to search the same directory for multiple files?

    Thanks in advance.

    Comment by salnasi — May 31, 2008 @ 5:36 pm

  8. Hi Zack,

    Great post, thanks a lot. I’ve got a bit of an issue though and your help would be appreciated.

    First question: I have quite a few XLS data sources, and even though I implemented this code SSIS Still moans that the Source file does not exist. Do you know how I get past this issue?

    Second Question: I disabled the control flow for the portion of the package that gives an issue so that I could test the code, and the package the runs fine. The issue that I have is that even after setting a condition in the precedence constraint the Script task doesn’t seem to go and update the File Exists variable. I had to alter the code slightly to work with the variables that I already had.

    The code as it currently looks can be found below:

    Imports System
    Imports System.Data
    Imports System.Math
    Imports System.IO
    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

    ‘ DATE: 12 August 2008
    ‘ AUTHOR: Colin Macguire
    ‘ OTHER INFO: Code sourced from https://dichotic.wordpress.com/2006/11/01/ssis-test-for-data-files-existence/

    Public Sub Main()
    Dim fileLoc, fileName As String
    If Dts.Variables.Contains(“User::Var_Source_Path”) = True AndAlso _
    Dts.Variables.Contains(“User::Var_Source_Name”) = True Then
    fileLoc = CStr(Dts.Variables(“User::Var_Source_Path”).Value)
    fileName = CStr(Dts.Variables.Item(“User::Var_Source_Name”).Value)
    ‘System.Windows.Forms.MessageBox.Show(“FileDir:” + fileLoc + “FileName:” + fileName)
    If File.Exists(fileLoc + fileName) Then
    Dts.Variables.Item(“User::Var_Source_Exists”).Value = True
    ‘System.Windows.Forms.MessageBox.Show(”File exists”)
    Dts.Variables.Item(“User::Var_Source_Exists”).Value = False
    ‘System.Windows.Forms.MessageBox.Show(”File not exists”)
    End If
    Dts.TaskResult = Dts.Results.Success
    Dts.TaskResult = Dts.Results.Failure
    End If
    End Sub

    End Class

    Your help would be greatly appreciated.


    Comment by colinmacguire — August 12, 2008 @ 9:55 am

  9. Colin.

    It’s been a while since I’ve looked at this post. But I think this might be your answer. Try the following:

    Go to the Control Flow
    Click on the background so you can get the Control Flow’s properties settings
    Under ‘Delay Validation’, set that to True

    I’ve been out of SSIS development as of late, but I’m pretty sure that making that setting is ok to do. It seems to work when I rebuild a test package.

    Comment by Zack — August 13, 2008 @ 3:32 pm

  10. Hi Zack,
    Your post really helped me lot. In my situation: a text file with name “‘ftp.May08′.txt” will come in a common folder say FTP on May. On June, it will come as “‘ftp.June08′.txt in common folder FTP. Here in your example, the file name is constant.I want to check the different file name with same extension(.txt). If you provide a solution for this,it would be great help for meThanks,

    Comment by kishorejb — August 18, 2008 @ 12:35 pm

  11. Hi Zack,

    Sorry for only replying now, been a hectic few days here. Thanks a lot for your comment, sorted me out straight away.

    Have a good one!


    Comment by colinmacguire — August 19, 2008 @ 3:01 pm

  12. What a great article. This really helped me out today and was very simple to implement. Thanks!

    Comment by markp3rry — September 11, 2009 @ 10:34 am

  13. Perfect. Thanks for taking the time to post.

    Comment by theonebalance — October 16, 2009 @ 2:21 pm

  14. Thank you so so very much for this post!!!!!!!!!!!!
    Very clear and to the point.

    Comment by kissa49 — May 20, 2010 @ 3:40 pm

  15. This method is very useful and important as well. I did not know it before. I just let my package run to the end.
    thank you very much

    Comment by Tom Nguyen — August 12, 2011 @ 1:22 am

  16. If I am pulling my path dynamically from SSISConfigurations table over to local connector SourceConnectionFlatFile — how would I apply IF EXISTS task logic?

    Comment by stevedev — July 16, 2012 @ 4:25 pm

    • nvm.. I used file exists in t-sql, and then executed package via “proc executing temp job” to avoid xp_cmdshell security concerns/block.

      CREATE PROCEDURE [dbo].[process_DataSync_IFFILEEXISTS_RunPKG]
      @Agency_id int
      DECLARE @job NVARCHAR(100) ;
      DECLARE @pkgpath NVARCHAR(250);
      DECLARE @Agency_id_Convert VARCHAR(10);

      SET @Agency_id_Convert = CONVERT(VARCHAR(10),@Agency_id)
      SET @pkgpath = ‘c:\databases\fbimport\’ + @Agency_id_Convert + ‘Import.dtsx’

      –check if exists — if so run pkg as temp job

      DECLARE @csvfile NVARCHAR(150)
      DECLARE @file_exists_result int
      SELECT @csvfile = ConfiguredValue FROM dbo.SSISConfigurations WHERE ConfigurationFilter = ‘2299’

      exec master..xp_fileexist @csvfile, @file_exists_result OUT

      IF (@file_exists_result = 1)

      SET @job = ‘xp_cmdshell replacement – ‘ + CONVERT(NVARCHAR, GETDATE(), 121) ;

      SET @PKGCmnd = ‘dtexec /f “‘ + @pkgpath + ‘”‘

      EXEC msdb..sp_add_job @job_name = @job,
      @description = ‘Automated job to execute command shell script’,
      @owner_login_name = ‘sqlmx7user’, @delete_level = 1 ;

      EXEC msdb..sp_add_jobstep @job_name = @job, @step_id = 1,
      @step_name = ‘Command Shell Execution’, @subsystem = ‘CMDEXEC’,
      @command = @PKGCmnd, @on_success_action = 1 ;

      EXEC msdb..sp_add_jobserver @job_name = @job ;

      EXEC msdb..sp_start_job @job_name = @job ;



      Comment by stevedev — July 16, 2012 @ 6:12 pm

  17. […] that is correct. I found this blog entry which explains how to do proper control flow conditional branching based on boolean […]

    Pingback by How to programmatically change the work flow between SSIS control flow tasks? | XL-UAT — March 5, 2015 @ 4:14 am

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Create a free website or blog at WordPress.com.


Get every new post delivered to your Inbox.

%d bloggers like this: