Hi All,

I have a scenario, need to create SQL server Tables dynamically.

I Have multiple xml data file on a particular location, and want to load those XML data into sql server tables, but he metadata of each xml data files are not same.

Hance the approach is that,

1. Pick first file from that location

2. Create a table according to that xml data file metada

3.  load data on newly created table.  

4. Pickup the next xml data files.

5. loop through, till the XML data files are exists on that location.

any suggestions, Ideas.

Thanks

Sandeep Vaishya

Hi Sandeep,

The scenario you have put is not the best of cases to use SSIS as you wold eb doing almost the entire ETL in script task or my be a coupleof other tasks in Control flow. You would not be able to use Data FLow task as the meta data keeps changing.

Steps you would require:

1. Iterate thru each fle

2. Check the meta data and create query string to create the table (Script Task)

3. Upload the data to the table created(Script task) as it would not be possible to read the XML data in bulk load etc or Execute SQL task.

4. Drop the table created or what ever be your case (Execute SQL

Hi Sudeep,

Thanks for the quick responce.

I do not know any scripting language, I worked only on ETL tools.

as per you approach, how can I check the metada and create query string to create table in Script Task.

and then Will I use DFT to load the data into newly created table ?.  

What if, I do not have MetadaData Files(.xsd). How can I creatre xsd files for eaxh xml data source in SSIS while looping through each file.

Thanks

I do not know any scripting language, I worked only on ETL tools.

as per you approach, how can I check the metada and create query string to create table in Script Task.

 The case that you have presented wuld require coding and not pure use of any tool will help. May be you need to google or get in touch with a peson who has some basic coding knowledge.

and then Will I use DFT to load the data into newly created table ?.  

 You will not be able to use DFT as you meta dta is changing constantly. You could create a dynamic SSIS package but that would be a big task and require a lot of coding.

What if, I do not have MetadaData Files(.xsd). How can I creatre xsd files for eaxh xml data source in SSIS while looping through each file.

You could generate the XSD for the XML file just goolge. once thats in place you have a look at this lin

Hi Sandeep,

were you able to create the package dynamically from XML source, if yes, Please help asap :(

Regards,

Ankit