SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Capturing XML File name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

imcoolanytime_sql
Starting Member

USA
2 Posts

Posted - 07/18/2013 :  15:02:58  Show Profile  Reply with Quote
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 table

I'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

India
52317 Posts

Posted - 07/19/2013 :  00:53:11  Show Profile  Reply with Quote
you dont need SSIS for this. You can use BULKINSERT,bcp etc

http://msdn.microsoft.com/en-us/library/ms191184.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

imcoolanytime_sql
Starting Member

USA
2 Posts

Posted - 07/19/2013 :  11:06:06  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 07/19/2013 :  13:49:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/20/2013 :  08:41:06  Show Profile  Reply with Quote
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 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

http://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000