So we have now exited from Phase 1 because all of our files have been moved to our loading directory and their paths have been entered into our SQL Server table. We now need to insert the files and their names into the destination table of LoadedTable. We have two SQL Server connections in this DataPump task "Names Of Files" which will be the table from where the file name will be retrieved and "Destination" which is where we will load the files.
After we have chosen our tables as the Source and Destination objects we come to the most important part which is defining our transformations. The first thing I do is remove the ones the DTS Designer has entered for us. We then need to create our own. Drag the Source column FileLocation over to ActualFileName and when prompted choose Copy Column as the type of transform. Now drag the FileLocation column again over to the FileDetails column. When prompted choose Read File transformation.
Our whole set of transformations for this package should then look like this
As you can see there is a bit of work involved setting this up if you want to dynamically load the filenames as well as import the files themselves but hopefully this article will help those who have had a requirement to do this type of thing.