I am pulling my hair off... seriouslyT_T help! I tried to import flat files and excel sheets into SQL server with no problem since they were basically in the same format... and I only need to put all of those into one fixed table~ Now the things totally changed.... every XML file is carrying completely different features and every single one need to go to a undeveloped new table in SQL server. Here are two major issues I am trying to get over with!!! First... I am trying to import those xml files which do not have XSD generated yet... So I really want to use XML Source in Data Flow Task but sadly... XSD for each XML file will be different... And after some research online... seems like script task is the only solution to handle those XML files with different schema involved? But any way to combine script task into Data Flow Task so that generating XSD part from Data Flow Task can be retained? Second... some of those XML files after assigning the XSD will generate dozens of outputs while some of those generate only one... Any way that I can merge all outputs into one table for each single XML file? I just learnt how to deal with them separately and manually (use Merger Join for those multiple-output ones)but are there anyway to do it automatically or furthermore make all of those happen like in a Foreach Loop? The reason I am trying to do it automatically is that those outputs are still changing dynamically (sometimes fewmore output sometimes few less output)... Did anyone ever try something like this? different XSD need to be generated for different XML files and to be used dynamically? different outputs from one single file need to be merged into one table in SQL automatically and dynamically? BOY... Hopefully people can understand what I am talking about here... Any help will be hugely appreciated!!!!!! O.O

See if you can flatten or merge the files with XSLT: http://blogs.msdn.com/b/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx I think it is also possible to detect the schema dynamically and then process the files using one DFT or the other, but I am not sure how dynamic your files are in nature. Are you talking about loading absolutely any possible XML file? Doubt this. Excel I am not sure what is the issue, but seems a similar logic/approach must be used. In general, a good Script Task with some .net Office interop code should be able prepare a single Excel file that the SSIS would load.Arthur My Blog

Thank you Arthur for the info~ looks really a great idea, I will definitely check more on the xslt idea. Corecct me if I am wrong... does that mean every time I receive a new xml file I have to create xslt manually? Some of them look really crazy wild:( Since I was having those excel sheets in the same format, I was simply using the Foreach Loop to make it happen. However since none of those xml files are similar I cannot really loop through them... guess script task is the only option? Maybe a small excample will help with understanding my issues... I have a group of xml files: table A.xml., table B.xml, ... Assuming that I have already preprecessed the files successfully and will be using new xml and xds files ... Now I want to process table A.xml using its schema table A.XSD then create a new table called table A in SQL server... then same thing to table B, ... Then next day... I will have a new group of xml files (under the same names): table A.xml, table B.xml, ... (SCARY PART: tables are not structured int he same way as the ones from the first day. Maybe ten more columns showed up maybe some columns are replaced with others... which means the tables in SQL server will be created dynamically as well) So I have not learnt a lot on 'coding' yet ... does it sound feasible to create a script that can pull out one xml file and one schema each time and let it process and create table in SQL server just like in data flow task, then catch the next xml file w/ its corresponding XSD file? Hopefully, I showed my problem clear this time... phew~ Thanks again Arthur, I will keep trying and do not hesitate to throw me any other bright ideas if they come to you :D

You cannot really create a package that will be a robot per se. Each new XML file structure needs to be handled and that implies manually. Otherwise how do you know if it works? The Script Task is at least what makes sense always because it is very flexible, and with good programming knowledge it turns to be that Swiss Knife (sky is the limit with the Script Task). Seems to me that the structure is more or less the same, right? E.g .a column added or removed? If yes, perhaps the XSLT can simply extract them to a flat file from which you build the CREATE TABLE command?Arthur My Blog

Thanks Arthur... I do not have that Swiss Knife T_T but I found a faster way for dealing with those xml generating multiple outputs by importing my different xml files using Access or Excel... Very interesting to see that when dealing with xml files sometimes those multiple outputs happened in SSIS can be automatically joined by Access when importing while those including tons of blank records can be picked up by Excel when importing... So I am wondering if those softwares are actually using different schemas generators... if that is the case, once I found the right one to use, things will work out much easier... but this may just be my fantasy~