Start a new project, add a "For Each File" component. Add a variable of type String. Now add the variable to the properties of the "For Each File" component. Choose wether full path name or only filename is retreived.
I'm trying to set up this as a job that runs daily as we get a new xml file every day. I think I'm almost close but somehow the variable is not being set correctly.
You need to do the following steps
1. Add a For Each Loop. choose file enumerator option. Browse to the folder where you get your xml files. Choose option as fully qualified and then map a string variable created inside ssis in the for each loop to get the filename 2. Then add a data flow task which will have a XML Source and OLEDB destination. Map it to a test XML file and set metadata mapping to map it to table columns. 3. Set an expression for source property of XML Source and map it to earlier created variable. 4. Add a derived column transform after XML source to add new column with value mapped to variable created above to get filename.
Run it and package will iterate through each XML, then transfering data from each file to table along with an additional column which contains the filename
a similar logic package can be found below which uses text file instead of XML as the source