July 11, 2011 at 12:39 am
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 post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply