Dynamic OLEDB Table Generation based on XML Files in SSIS package



I am developing an SSIS package that takes an XML file's information from an existing table in an OLEDB, generates an XSD schema and then imports the data from the XML file into its own table in a database, with its own columns, mappings, etc. The XML files are all quite different, with different content in each one. Therefore, I'm trying to make a way to have my package dynamically generate tables based on the XML file being brought in at run-time.


I've done extensive research and have found nothing to help me figure out how to implement a way to have my package (or VB program executing the package itself) dynamically look at the incoming XML file, create a table based on the mappings, and then load the XML into the dynamically generated table.


If that doesn't make sense, I'm essentially trying to replicate the function of the "Create Table" button in the OLEDB Destination Task that automatically generates Create Table SQL code based on the file loaded in.


How can I make this happen? I'm aware SSIS doesn't play nicely with dynamic meta-data, but there must be another way to do what I'm trying to do.


No comments:

Post a Comment