Loading multiple XML file with different Metada In SQL Server Tables with SSIS 2008
Hi, I have multiple xml data file in a directory say C:\XMLData\ abc1.xml, abc2.xml, abc3.xml etc. Need to loop through each file in ssis with Foreach loop container, and get the file name say abc1, and load the data of abc1.xml to abc1 table in sql server DB. next iteration will pick up the abc2.xml and find the abc2 table in sql server DB then insert the data in abc2 table. while each iteration, xml source should also point each xsd file correspondingly. Tables are already created in DB I solved my problem up to getting the file name from ech iteration and assigned file name to variable, in oledb destination data access mode I select Table or view name variable, then corresponding table will get selected for data insertation. Just wanted to know how can I read each xsd file for each xml data files while iteration. Any early help will be appreciated. Sandeep VaishyaThanks Sandeep Vaishya
February 17th, 2011 12:49am

For this a script task has to be used to read the XSD file of the given XML file.
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2011 5:18am

Hi Sandeep, You cannot use Data flow task in your scenario and hence cannot use OLEDB Destination as the meta data would change. In you last post you mentioned that you need to create the table based on the XML files has that requirement gone? If thats the case you would be better of useing Script task in control flow and reading XML file Node by Node (XML Reader or XML Document - XML Doc is slower as compared to reader.) and inserting the data to the corresponding table.My Blog | Ask Me
February 17th, 2011 5:32am

Thanks Sudeep, Thats right, requirement i a bit change now. Instead ofcreating teh table, we have already tables in DB. can I get the sample code for same. Thanks Sandeep Vaishya
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2011 5:48am

Have a look at this link for ideas. once you iterate create insert script to load to table(you can get the code for this online just google.) I will not be able to send you the code right away.My Blog | Ask Me
February 17th, 2011 6:40am

I'm agree with others , you need to use scripting for this, because structure of xml files are not same, xml source and data flow doesn't support dynamic metadata, so you need to handle this data transfer with script, you need C# or .NET code to read xml data and load it in a destination, you can use XMLReader class for this purpose http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2011 7:02am

If you can use third party solutions, check the commercial CozyRoc Data Flow Task Plus . It is an extension of the standard data flow task and can process dynamic data flow columns at runtime. You can process all your files using just one For Each Loop container and one data flow task. The solution doesn't require programming skills.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
February 17th, 2011 3:41pm

Hi I got a C# script code for loading XML data into Sql server table. the only thing I need to use this in sctipt task and use the variable instead of hardcoded file path and table name. Script: using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.Data.SqlClient; using System.IO; namespace ST_37ea42e2bf564f4f9a848267124b45f1.csproj { [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { public void Main() { // TODO: Add your code here String sDatabaseConnectionString = @"DataBaseConnection string"; // Here I trying to use Variable Instead of C:\HCS\Data\cd_catalog.xml for file location String sXMLFile = @"C:\HCS\Data\cd_catalog.xml"; try { using (SqlConnection oConn = new SqlConnection(sDatabaseConnectionString)) { // Open the Connection to the database oConn.Open(); // Instanciate a new DataSet using (DataSet dsTemp = new DataSet()) { //Read the XML file into the DataSet dsTemp.ReadXml(sXMLFile); // Instante a datatable from the DataSet using (DataTable dt = dsTemp.Tables[0]) { // Instanciate a new SqlBulkCopy object using the connection using ( SqlBulkCopy sb = new SqlBulkCopy(oConn) ) { // Assign BatchSize sb.BatchSize = 50; // Assign Destination Table Name // Here I trying to use Variable Instead of C:cd_catalog for table sb.DestinationTableName = "cd_catalog"; sb.WriteToServer(dt); } } } } } // Catch any SQL errors first catch (SqlException ex) { } Dts.TaskResult = (int)ScriptResults.Success; } } } I tried multiple option but not able to get variable inside the script. Any help, how can I use variable. Thanks Sandeep Vaishya
Free Windows Admin Tool Kit Click here and download it now
February 22nd, 2011 1:45am

in case you want to use variables se the syntax below and set the variable as read or write as per your need in the Script setup window. Read a variable String sXMLFile = Dts.Variables("Use::strFileName").value.tostring() write to a variable: Dts.Variables("Use::strFileName").value = sXMLFileMy Blog | Ask Me
February 22nd, 2011 3:28am

Hi Sudeep, Thanks for your quick reply. I assigned variable in ReadonlyVariables in script task part String sXMLFile = Dts.Variables("Use::V_Source").value.tostring(); I did the same and getting bellow error .. Error 1 Non-invocable member 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel.Variables' cannot be used like a method. Thanks Sandeep Vaishya
Free Windows Admin Tool Kit Click here and download it now
February 22nd, 2011 3:41am

Hi, I used like that String sXMLFile = Dts.Variables["myvariabe"].value.tostring(); It is working fine. Now I want to use OLEDB connection, what ever I defined in SSIS Connection manager in SSIS Script Task instead of String sDatabaseConnectionString = @"DataBaseConnection string"; any suggeations.... Thank you all for your valuable suggestions.Thanks Sandeep Vaishya
February 22nd, 2011 6:33am

Have a look at my blog where I talk of dynamically setting connection string based on SSIS variables using expressions. And for the code square brackets are use in C# and () is used for vb.net code. I did not notice that you were using C#. :)My Blog | Ask Me
Free Windows Admin Tool Kit Click here and download it now
February 22nd, 2011 9:35am

Have a look at my blog where I talk of dynamically setting connection string based on SSIS variables using expressions. And for the code square brackets are use in C# and () is used for vb.net code. I did not notice that you were using C#. :)My Blog | Ask Me
February 22nd, 2011 9:35am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics