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