none
Send email if Foreach loop container, does not find files

    Question

  • Hi All, I have a foreach loop container that copies *.csv files (using a file system task) from one folder to another. How would I send an email if not files are found by the foreach loop container? I am not sure what expression to use in the Precedence Constraint Editor. The variable that the foreach loop container uses is called 'FilesToMove'. I have tried using an expression such as @filesToMove == Null without success. Many Thanks. Chris
    Wednesday, February 24, 2010 4:17 PM

Answers

  • take a ForEachLoop container. map to the particular directory.

    Inside that you have to use a script task and there you can write following code to count the no of files.


    -------------------------------------------------
    ForEachLoop                                          |
    -------------------------------------------------
    |
    |             --------------
    |            |Script Task |
    |             ---------------
    |
    |
    |
    ---------------------------------------------------


    define this variable @ package level
    User::Count
    
    	Public Sub Main()
            Dts.Variables("User::Count").Value = Dts.Variables("User::Count").Value + 1
            MsgBox(Dts.Variables("User::Count").Value.ToString())
    		Dts.TaskResult = ScriptResults.Success
    	End Sub
    

       
    Now you can access the count variable , use a presedance costraint to send email task.
    Let us TRY this |

    http://quest4gen.blogspot.com/
    • Proposed as answer by Sudeep Raj Wednesday, February 24, 2010 4:31 PM
    • Marked as answer by Chris Garvey Wednesday, February 24, 2010 5:21 PM
    Wednesday, February 24, 2010 4:20 PM

All replies

  • take a ForEachLoop container. map to the particular directory.

    Inside that you have to use a script task and there you can write following code to count the no of files.


    -------------------------------------------------
    ForEachLoop                                          |
    -------------------------------------------------
    |
    |             --------------
    |            |Script Task |
    |             ---------------
    |
    |
    |
    ---------------------------------------------------


    define this variable @ package level
    User::Count
    
    	Public Sub Main()
            Dts.Variables("User::Count").Value = Dts.Variables("User::Count").Value + 1
            MsgBox(Dts.Variables("User::Count").Value.ToString())
    		Dts.TaskResult = ScriptResults.Success
    	End Sub
    

       
    Now you can access the count variable , use a presedance costraint to send email task.
    Let us TRY this |

    http://quest4gen.blogspot.com/
    • Proposed as answer by Sudeep Raj Wednesday, February 24, 2010 4:31 PM
    • Marked as answer by Chris Garvey Wednesday, February 24, 2010 5:21 PM
    Wednesday, February 24, 2010 4:20 PM
  • Hi Sundeep Raj,

    Thanks for your reply.

    I am getting an error on the following line of code:

    Dts.Variables(

    "User::Count").Value = Dts.Variables("User::Count").Value + 1

    Error - Option strict on prohibits operands of type object for operator '+'.

    The Count variable has been defined as an int32.

    Any ideas how to solve?

    Regards,
    Chris

    Wednesday, February 24, 2010 4:53 PM
  • You'll need to inform the .Net code that the "value" of your variable is an integer - it doesn't know that.  Use this:

    Dts.Variables("User::Count").Value = CInt(Dts.Variables("User::Count").Value) + 1


    Todd McDermid's Blog
    Wednesday, February 24, 2010 5:00 PM
  • Use the appropriate type conversion function here CInt , to convert the operands to data types defined for the operator.
    OR
    Write the following code in you scripts first line
     (before Imports statement)
    Option Strict Off


    Let us TRY this |

    http://quest4gen.blogspot.com/
    Wednesday, February 24, 2010 5:03 PM
  • Thanks Todd for your suggestion.

    I am getting a different error now when running the package and files are found by the foreach loop container:

    Error: Failed to lock variable "Count; FilesToMove;" for read/write access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

    Any suggestions?

    Thanks,
    Chris

    Wednesday, February 24, 2010 5:08 PM
  • In ur Script task editor have you put the variable in the REad/Write section??


    Hope this helps !!
    Sudeep Raj   |    My Blog
    Wednesday, February 24, 2010 5:10 PM
  • You need to make sure that you've put that variable in the "ReadWrite" list on the Script Editor window.
    Todd McDermid's Blog
    Wednesday, February 24, 2010 5:11 PM
  • you have to add the count variable as "read/write variable " .. please check the spelling also.
    Let us TRY this |

    http://quest4gen.blogspot.com/
    Wednesday, February 24, 2010 5:11 PM
  • Variable 'Count' has been defined as a read/write variable within the script already.

    It looks like the variable is locking for some reason?

    Chis
    Wednesday, February 24, 2010 5:13 PM
  • delete the script task and start with new ....

    :)


    Let us TRY this |

    http://quest4gen.blogspot.com/
    Wednesday, February 24, 2010 5:15 PM
  • The error says it cant find the variable : "Count; FilesToMove " 
    Have you by mistake put the semicolon at the wrong place in ur code?
    Hope this helps !!
    Sudeep Raj   |    My Blog
    Wednesday, February 24, 2010 5:16 PM
  • Thanks All - Script now works ok. Deleting and re-creating sorted things out.

    For future reference, I have placed the following code within the script task:

    Option

     

    Strict Off

    Imports

     

    System

    Imports

     

    System.Data

    Imports

     

    System.Math

    Imports

     

    Microsoft.SqlServer.Dts.Runtime

    Public

     

    Class ScriptMain

     

    Public Sub Main()

    Dts.Variables(

    "User::Count").Value = Dts.Variables("User::Count").Value + 1

    MsgBox(Dts.Variables(

    "User::Count").Value.ToString())

    Dts.TaskResult = Dts.Results.Success

     

    End Sub

    End

     

    Class

    Regards,
    Chris

    Wednesday, February 24, 2010 5:22 PM