To get that done in SSIS, we need:
– A variable to store the current file name and will be used as the connection string for our flat file connection
– A for each loop that will loop through our directory, pass each file name in there to our variable and then run a data flow task
– A derived column in our data flow task that will store our variable so we can import it into our SQL table
– Since we want this to be “clean”, we will also run a truncate before the for each loop
So let’s look at the code for that
It’s Biml, so there isn’t much code. Check it out.