Hi, I am trying to create a SSIS package that will load a XML file into a SQL Server table. The problem is that the contents of the XML file are not known in advance. Therefore, the destination table needs to be created "on the fly". I have tried searching online but I cannot find an answer to this problem. Thanks in advance for your help!

You can always use a Script Task and use C# Coding to Parse through your XML document and make the task create your table. But how frequent do you expect the package to run? would you create a table or add columns each time you execute the package? We can work on a better design if we have more information :) Regards, Dinesh

Alas, SSIS requires persistent metadata. I advocate crating the package programmatically based on inspected input. You can use EzAPI or pure .net code: http://msdn.microsoft.com/en-us/library/ms135946.aspxArthur My Blog

Hi Dinesh, Thanks for your reply. This package is expected to run at least once daily, possibly more. It will loop through a list of XML files provided by the clients and will load all of the files. The files are coming from multiple clients but all of the files are XML. I would like to create a table per input file because I will not know in advance the columns in the files. The tables that are created will not be permanently kept on our db. The goal is to load the data on our db in these "temp" tables and then move the data into our permanent tables. The creation of many tables therefore is not an issue seeing as the temp tables are not persisted. Please let me know if you have any other questions. Thanks again for your help!

Thanks Arthur. Yes, the persistent metadata is the issue for us:( We wanted to keep the system flexible so that the client can change the contents (columns) in their XML file if needed without any changes to the software. Have a great day!

Hello, Please take a look at he URL belo as Sudeep has answered in the forum and Arthur above you cannot acheive this in a straight forward manner. Creating SQl Table based on XML Dynamically The trick would be to use the Script task to create the query/table from the XML File, and then use standard Data flow techniques to transfer data from temp to your destination tables. This will be purely .NET coding (using XMLDocument to load the XML File and parsing through each childnode and reading through the elements) Hope this gives you some of the information that you need. Regards, Dinesh