Post reply

Comparing two folders and one table to make files count same..

  • July 10, 2011 at 6:18 pm

    #241319

    Hello All,

    Help is greatly apprecited:

    scenario as below::-

    There are two folder (Pending and Dictations) and one Table Pending, in all three there should be same files/ same number of files and here i have to create SSIS package which will compare all the three and if it finds any extra file in any folder then file should get deleted by package itself..

    There is one field which is common in all, i.e. P_VoxFileFull, file name in Pending and Dictations table is as :

    1234#110710132452#4321#01.vox

    and in Pending Table P_VoxFileFull files should contain the same name.. by this we can compared this..

    I have to do it by using SSIS tools and then deploy it on SSMS with SQL server Agent with job.

    So please suggest how can it be implemented..

    Thanks.

  • July 11, 2011 at 12:39 am

    #1350891

    I would use plain old .NET to do this, but if I really had to use SSIS, I would use .NET in a script task 😀

    OK, seriously now, this is how you can do it within SSIS using (mostly) regular components:

    * first get the files in the folders and store them in 2 object variables. Okay, I would do this in .NET, because it is so easy there. Use DirectoryInfo.GetFiles and store the results in a dataset. Populate an object variable with this dataset.

    * create a dataflow and use the 2 object variables as sources (Okay, again, you'll need .NET to do this, as there isn't a standard recordset source). There are plenty of resources on how to read from an object variable in the dataflow. Create a 3rd source that gets the values from the table.

    * Sort the 3 sources on filename (you can already sort the source from the table by using an ORDER BY clause). Don't forget to mark the source as sorted in the Advanced Editor of the OLE DB Source. Use MERGE JOINS (left outer) to join the 3 sources together into a single flow with 3 columns.

    * Finally, use a conditional split to filter out all the rows that don't have 3 equal filenames in a row. Those rows are the files you are looking for. Write those rows to a recordset destination (which actually.

    * After the dataflow, place a script task, read the data from the resultset and delete the files.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply