none
Foreach File Enumerator Naming Issue RRS feed

  • Question

  • I have a SSIS package that has 8 different Foreach loops that loop through specific file names in a directory and process them to a database.  All of them work fine except for 1 of them because of the way I have it setup but i don't know another way to do it.

    The one having issues is looping through a directory looking for files with "XXXX_Held_At_Store" (The X's can vary) so I have it setup with a wildcard like this "*_Held_At_Store.pfs" but unfortunately one of the others is looking for "XXXX_Dept_Held_At_Store" so i have it setup with a wildcard also like this "*_Dept_Held_At_Store.pfs".  The "Dept_Held_At_Store" works fine because it is more specific but the "Held_At_Store" is bringing in the " Dept_Held_At_Store" files since the wildcard is at the beginning.

    I don't really have an option to change the names of the files it is importing so that is out of the question.  The only option I can come up with which is not what I would prefer is to process the "Dept_Held_At_Store" first and move them to a different directory and then process "Held_At_Store" since there wont be any " Dept_Held_At_Store" files.  Is there any way for me to configure this to only bring in the "XXXX_Held_At_Store" without conflicting with the "XXXX_Dept_Held_At_Store" with or without using a wildcard?

    Tuesday, January 10, 2012 6:18 PM

Answers

All replies

  • In one of the ForEachLoops where either you process *Held_At_Store* you can merely add a Script Task followed by a conditional branch a Precedence Constraint that is in which (in the Script Task) you would simply further check if this is the wanted file name. If it is you set a [new] package variable (say of type Int to mimic a boolean) to for example 0 in the script task and the Precedence Constraint would direct the package to the needed DFT is the file is right, or simply end/terminate (you can add here another empty Script Task).

    Example of such conditional branching: http://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/


    Arthur My Blog

    • Edited by ArthurZModerator Tuesday, January 10, 2012 7:14 PM added link to example
    Tuesday, January 10, 2012 7:13 PM
    Moderator
  • You can use something like a SSIS object to get the list of the "XXXX_Held_At_Store" tables and filter them out in a script component check this link , ( this link does not have your answer) and check step 3 and see how the list of files are been placed in a SSIS variable , IN the script (step 3) you can filter out the files that you dont need and redirect them to a ROW COunt object just to get rid of them, and finally use the SSIS Object in your FOR LOOP and loop the selected files

    good luck


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Tuesday, January 10, 2012 9:08 PM
  • I suggest you modify the regular expression to exclude the pattern _Dept_

    More information can be found here


    Please vote as helpful or mark as answer, if it helps
    Wednesday, January 11, 2012 6:25 AM
  • More elegant way, is to move the processed files when they are consumed by SSIS tasks. So when the _Dept_ files are consumed. Move them to say a "processed" folder


    Please vote as helpful or mark as answer, if it helps
    • Proposed as answer by Compugourmet Thursday, January 12, 2012 8:22 PM
    Wednesday, January 11, 2012 6:26 AM
  • I suggest you modify the regular expression to exclude the pattern _Dept_

    More information can be found here


    Please vote as helpful or mark as answer, if it helps
    Regular expressions are not supported in ForEach Loop Component.

    Arthur My Blog
    Wednesday, January 11, 2012 3:03 PM
    Moderator
  • More elegant way, is to move the processed files when they are consumed by SSIS tasks. So when the _Dept_ files are consumed. Move them to say a "processed" folder


    Please vote as helpful or mark as answer, if it helps


    _Dept_ may become moved, but how to prevent "*_Held_At_Store.pfs" from being picked?

    Answer I think can be as elegant as setting the wildcard mask to pick only *_dept*. Is that an option RammiKan?


    Arthur My Blog
    Wednesday, January 11, 2012 3:06 PM
    Moderator
  • another suggestion is MOVE THE FILES to a backup folder (In this Link the files get copied you can change it to move option)

    1 - loop 1 finds

             1.1- DFT

             2.1- Move file

    2- same as 1

    .

    .

    7- loop 7 find XXXX_Held_At_Store

             7.1- DFT

             7.1- Move file

    8- loop 8 find *_Held_At_Store

             8.1- DFT

             8.1- Move file

    Because in step 7 you have moved the file "XXXX_Held_At_Store" to a backup folder you will end up with files that are NOT like "XXXX_Held_At_Store" so now you can use "*_Held_At_Store" in the last loop.

     


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Wednesday, January 11, 2012 3:46 PM
  • More elegant way, is to move the processed files when they are consumed by SSIS tasks. So when the _Dept_ files are consumed. Move them to say a "processed" folder


    Please vote as helpful or mark as answer, if it helps


    _Dept_ may become moved, but how to prevent "*_Held_At_Store.pfs" from being picked?

    Answer I think can be as elegant as setting the wildcard mask to pick only *_dept*. Is that an option RammiKan?


    Arthur My Blog

    I technically could move the _Dept files first and then process "*_Held_At_Store.pfs" since that wildcard would not find  Dept_Held_At_Store.pfs since they are no longer in the directory but I would prefer to execute them all at the same time within one sequence container without having to be dependent on a separate foreach.  Also I would like to keep them all in the same directory until the last step in the SSIS package in case one of the files has 'garbage' and fails the package so that I can re-run the package and find out which file needs attention.  

    Is there an expression on the foreach where I could specify something like "*_Held_At_Store.pfs" but NOT contain "Dept" in the name?  Ultimately if I don't find an easy way to do this I think I will change the package that originally builds these flat files to rename _Held_At_Store.pfs to something more specific that doesn't come close to another file name.  This may be the easiest route but I am unsure if there are other packages or programs dependent on the naming of these files throughout the company.  Could make it easy for me but difficult for others if it messes up their programs. I checked with a few of the Devs and they think it would be fine but sometimes you blow things up even when you think you wont.

    Wednesday, January 11, 2012 5:14 PM
  • Wait, well, both files will be (or can be) picked up by a mask *Held_At_Store*, right? So what is the problem to use one ForEachLoop? I thought the structure is different [of the files] this is why you need a different DFT.

    I exactly offered the "expression way" but achieving this by the means of a Precedence Constraint in which you would check if the currently processed file is wanted or not. The SSIS expression in this constraint would be like FINDSTRING(@[pkVarCurrentFileName], @[FileNameToExclude],1) > 0 if it is true you do not process it.

    Besides, I am kinda interested in covering this in a blog post, if you can wait I may blog about this tonight or tomorrow so you can have a visual "idea" of what I am talking about. It is just better to have a mechanism of "skipping" a file on demand.


    Arthur My Blog
    Wednesday, January 11, 2012 6:53 PM
    Moderator
  • Wait, well, both files will be (or can be) picked up by a mask *Held_At_Store*, right? So what is the problem to use one ForEachLoop? I thought the structure is different [of the files] this is why you need a different DFT.

    I exactly offered the "expression way" but achieving this by the means of a Precedence Constraint in which you would check if the currently processed file is wanted or not. The SSIS expression in this constraint would be like FINDSTRING(@[pkVarCurrentFileName], @[FileNameToExclude],1) > 0 if it is true you do not process it.

    Besides, I am kinda interested in covering this in a blog post, if you can wait I may blog about this tonight or tomorrow so you can have a visual "idea" of what I am talking about. It is just better to have a mechanism of "skipping" a file on demand.


    Arthur My Blog

    Yes both files can be picked up by using *_Held_At_Store.pfs and I am using separate DFT's for every type of file as I am using different Foreach loops for each type of file also.

    I tried to do the Precedence Constraint but was unable to get it to successfully work.  I have no doubts it would work correctly if I had the right syntax but I wasn't able to figure that part out. I have decided to change the package that originally creates these flat files to change the name to "XXXX_Store_Held_At_Store.pfs" so that I can use "*_Store_Held_At_Store.pfs" without issues with the other file.  I also chose to do this to keep everything consistent in my package so there were not any dependencies between any of the Foreach loops and also so I did not have to have a "one off" scenario where everything is the same except one part.  This would cause for more headaches in the long run if someone had to go in and edit or change the package that was unfamiliar with the process.  I appreciate everyone's help, they all seemed to be viable solutions to my issue.  Please keep me ArthurZ please keep me posted on your blog post as I would love to see how this would work in your methodology. I am sure your walk through would help me in a situation like this down the road.

    Wednesday, January 11, 2012 7:49 PM
  • but I would prefer to execute them all at the same time within one sequence container without having to be dependent on a separate foreach.  Also I would like to keep them all in the same directory until the last step in the SSIS package in case one of the files has 'garbage' and fails the package so that I can re-run the package and find out which file needs attention.  

    OK then dont move the files , what  you can do is the last 2 loops that are related to the "*_Held_At_Store" and "XXXX_Held_At_Store" can be hooked up to a SSIS object ,

    1- make 2 ssis object    1- uVar_XXXXFile  and   2- uVar_NON_XXXXFile

    2- make a loop  that finds all "*_Held_At_Store" files (bith files are included)

    3- in this loop make a script and get each files propertie like

     

    4- you have to add more variable , see variables in script task

    5- in the  loop use a DFT

    6- in the DFT use a SCRIPT COMPONENT ( i will say what this will do and the end), the SCRIPT component has 2 exits to 2 "Record set destination", the script MUST have 2 OUT PUTS

    7- use 2 records set destination for each SSIS objects one for uVar_XXXXFile  and  one for uVar_NON_XXXXFile

    8- end of loop

     

    Now you can use the 2 related FOR LOOP with the SSIS objects

    ------------------------------

    Main Part is the SCRIPT COMPONENT  (You need to modify this script) , this is the script with 2 outputs , a good example is here

    ---------------------------------

    use a script and redirect the files relate to "XXXX_Held_At_Store" to uVar_XXXXFile  and   the rest of the files to uVar_NON_XXXXFile

           If Me.Variables.uVarPathFileName = "XXXX_Held_At_Store" Then
                With MainOutputrecordsBufferXXXX
                    .AddRow()
                    .PathFileName = Me.Variables.uVarPathFileName
                    '.FileRowCounter = Me.Variables.uVarFileRowCounter
                    .Path = Me.Variables.uVarPath
                    .FileName = Me.Variables.uVarFileName
                    .FileExtension = Me.Variables.uVarFileExtension
                    .DateCreated = Me.Variables.uVarDateCreated
                    .DateModified = Me.Variables.uVarDateModified
                End With
            Else
                With MainOutputrecordsBufferNON_XXXX
                    .AddRow()
                    .PathFileName = Me.Variables.uVarPathFileName
                    '.FileRowCounter = Me.Variables.uVarFileRowCounter
                    .Path = Me.Variables.uVarPath
                    .FileName = Me.Variables.uVarFileName
                    .FileExtension = Me.Variables.uVarFileExtension
                    .DateCreated = Me.Variables.uVarDateCreated
                    .DateModified = Me.Variables.uVarDateModified
                End With
            End If
    


    to get an idea  of the script check this link

     


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Wednesday, January 11, 2012 8:10 PM
  • I just came up with a very simple and fast solution

    keep all the loops that you have , add a variable uVarSkipFileFlag = False (boolean SSIS variable) in the loop that looks for "*_Held_At_Store" before the DFT add a script task that checks the FIle name inthe script check for

    IF uVar_FileName = "XXXX_Held_At_Store" THEN

    uVarSkipFileFlag = TRUE

    ELSE

    uVarSkipFileFlag = FALSE

     

    go to the DFT ---> Expression ---. set the Property Disable ---- set the expression to uVarSkipFileFlag

    this will disable the DFT when the file name is "XXXX_Held_At_Store" and will go to the next file


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Wednesday, January 11, 2012 8:37 PM
    • Marked as answer by RammiKan Thursday, January 12, 2012 4:32 PM
    Thursday, January 12, 2012 4:52 AM
    Moderator
  • Hello,

    How about having all FEL containers in a sequence container and make them run in parallel i.e. do not connect then FEL containers except the the _Dept_ and _Dept_Held FEL container.

    Connect these two FEL containers with a onSuccess constraint.

    Also, inside the FEL container's have an additional FST(File System Task) to move the files to a "processed" folder.

    What do you think. All tasks execute in parallel and _Dept_ and _Dept_Held run in sequence.


    Please vote as helpful or mark as answer, if it helps
    Thursday, January 12, 2012 5:00 AM
  • I had a similar issue yesterday. I defined a variable to hold the currently navigated file name. This variable is passed to the script task and inside the script task I checked if the Filename contained the characters of the name of text file that I wanted to ignore. If that it is true, I simply exited from the script task. This way I was able to navigate through the other files without any issues.

    Thanks

    Murali

    Thursday, January 12, 2012 5:40 AM
  • I have published my proposed solution in my blog for your reference:

    http://geekswithblogs.net/Compudicted/archive/2012/01/11/the-ssis-expression-wayndashskipping-an-unwanted-file.aspx


    Arthur My Blog
    Wow.. you made that look a lot easier than I was doing when I tried the other day.  I will definitely be bookmarking your blog for future references.   Thank you so much for your help.
    Thursday, January 12, 2012 4:32 PM
  • I just came up with a very simple and fast solution

    keep all the loops that you have , add a variable uVarSkipFileFlag = False (boolean SSIS variable) in the loop that looks for "*_Held_At_Store" before the DFT add a script task that checks the FIle name inthe script check for

    IF uVar_FileName = "XXXX_Held_At_Store" THEN

    uVarSkipFileFlag = TRUE

    ELSE

    uVarSkipFileFlag = FALSE

     

    go to the DFT ---> Expression ---. set the Property Disable ---- set the expression to uVarSkipFileFlag

    this will disable the DFT when the file name is "XXXX_Held_At_Store" and will go to the next file


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    This is very similar to ArthurZ's approach but i think you are a little confused on the file names.  Either way i think it works the same way ArthurZ's would.  Thanks for your help.
    Thursday, January 12, 2012 4:38 PM
  • This is very similar to ArthurZ's approach but i think you are a little confused on the file names.  Either way i think it works the same way  ArthurZ's would.  Thanks for your help.

    Yes you are right, use his blog.

     

    Thanks ArthurZ for the blog


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Thursday, January 12, 2012 6:56 PM
  • You are welcome.

    Hey Nik, by the way I liked your chapter in the Deep Dives vol 2 very much, and say hi to Vidas from me.


    Arthur My Blog
    Thursday, January 12, 2012 7:10 PM
    Moderator
  • Thanks Arthur

    I talked to vidas today he told me about you, maybe we can get to gether and talk aboout SSIS, I have new ideas

    can you eamil me at SNikkhah@live.ca, thanks


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Thursday, January 12, 2012 8:23 PM