none
One critical requirement with For Each Loop container and File System Task - Please help!!!!

    Question

  • Hi,

    This is one of the critical requirement given by my client.

       Assume there are 3 Files are available in a folder. They are 

    a)file_july10.txt, 
    b)File_july11.txt and 
    c)File_july12.txt.

    My package has to do the following

    1) For each loop the the folder and all the file_*.txt files should be loaded into the table(data will be appended in the table).
    2) Then all the three files should be archived.
    3) Incase, next week , if they put 3 files in the shared path
        The files may be 
        a)File_july12.txt,
        b)File_July13.txt and 
        c)File_July15.txt.
      
     Please note - this File_july12.txt  is the same file we loaded  and archived during the last load, but this week it has come again)
       
    4) So, this time , i should load the  File_July13.txt and File_July15.txt files, because they are new files and then i have to archive it.
    But the File_July12.txt - I just need to delete it without loading, because we loaded it last time.


    I am ok with loading and archiving the files (1 & 2 steps). But, i am not able to do the next ie - if the already loaded file (might be loaded last week, or last month), i have to delete it without loading. 

    This is somehting like a case statement - if (file name is already there) - send it to File System Task for deletion. If not, send it to a DFT for loading and then archive.

    I have lot of questions like
    Where to store the loaded files name? How to compare the file name loaded this time to previous loaded file ? and then how to pass the file name to the File System Task ?

    Can you please help me to do this -  I don't know much about Script task - it would be great if you help me with all the other tasks!


    Thanks
    Revathy
       

    Tuesday, July 30, 2013 3:07 AM

Answers

  • Hi,

    Create a table while filename and Isprocessed/IsArchived column. On processing each file using foreach loop u can update the Isprocessed/IsArchived column so that while reading the files from the folder read only the file who status is not processed.

    Let me know if u have any issues.

    Regards,

    Arjun Balaji.

    balaji_dsce@yahoo.co.in

    Tuesday, July 30, 2013 8:12 AM
  • As Arjun suggested, the other elegant way is to create control table in SQL server that should store the filename (loaded/archived) and other column that flag for the file i.e. is it prevoiusly archived/loaded.

    Now in your for loop, once you load the file, for example A, add exec sql task that insert 1 record into the control , indicating file A (name of the file) and is archived set to 1.

    In your package, you have to select the filename with query like this

    SELECT FileName 
    FROM ControlTable
    WHERE IsArchived=1

    Save this result into object variable. Now you have to compare, new filename with these old file names, and one's that are loaded, you will load.


    Thanks, hsbal

    Tuesday, July 30, 2013 2:11 PM
  • Hi ,

      Can you please explain a bit more about 'Compare the File_names_loaded'?

    Because I am able to get the file name using a ForEachLoop and insetted into the table . I am confused about how to Compare the file name and then how to do the diversion like, if it is there, then delete and if it is not there, go to a dft and then archive?

    I am using SQL serve 2012. There is a task called Expression Task -will it help?

    Like hsbal said ,  do you have a column holding filename or column indicating whether the particular file archieve or not ? in the database table .

    If not !!!

    Step 1 :

    Use ForEachLoop Container  - loop through files in the source folder and get filenames and insert into table Sourcetbl .

    To capture filenames into table :

    http://sathyadb.blogspot.in/2013/02/ssis-capture-filenames-while-looping.html

    Step 2 :

    Use ForEachLoop Container  - loop through files in the archieve folder and get filenames and insert into table Destinationtbl .

    Step 3 :

    Use Execute SQL Task

    SELECT * FROM sourcetbl
    EXCEPT
    SELECT * FROM destinationtbl
     

    Get the above resultset into Object variable say Variable1 , try this link for example :

    http://beyondrelational.com/modules/2/blogs/101/posts/13354/ssis-full-result-set-to-a-set-variable-of-object.aspx

    Step 4 :

    Use ForEachLoopContainer with Variable1 as input and inside this ForEachLoopContainer use DataFlow task to load files into table and File System task to archieve/move the files

    Step 5 :

    Use File System Task to delete remaining files in the source folder (that are already processed before this current run)

    Step 6 :

    Delete tables Sourcetbl and Destinationtbl using Execute SQ task


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.


    Tuesday, July 30, 2013 6:16 PM

All replies