Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
imcoolanytime_sql
Starting Member
2 Posts |
 Posted - 2013-07-18 : 15:02:58
|
| I've been trying all over to accomplish this task:- load an xml file from a folder into a table_a - capture the file name in the above tableI've tried script task and SQL tasks by creating variables. I'm able to load the table but cannot seem to capture the file name. Any ideas? Thanks in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
imcoolanytime_sql
Starting Member
2 Posts |
Posted - 2013-07-19 : 11:06:06
|
| 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. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-19 : 13:49:29
|
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.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-20 : 08:41:06
|
quote:
Originally posted by imcoolanytime_sql
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 steps1. 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 filename2. 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 filenamea similar logic package can be found below which uses text file instead of XML as the sourcehttp://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Capturing XML File name