XML Data Load Using SSIS Script task C#.Net


XmlDataDocument xmldoc = new XmlDataDocument();
XmlNodeList xmlnode,xmlnode1;
int i = 0,j=0;
string str = null;
string FilePath = Dts.Variables["User::File_Name"].Value.ToString();
int nodecount = 0;
FileStream fs = new FileStream(@FilePath, FileMode.Open, FileAccess.Read);
xmldoc.Load(fs);
xmlnode = xmldoc.GetElementsByTagName("TagName");
xmlnode1 = xmldoc.GetElementsByTagName("AnotherTagName");           
nodecount = xmlnode.Count;

for (i = 0; i <= xmlnode.Count - 1; i++)
    {
       for (j = 8; j <= xmlnode[i].ChildNodes.Count - 2; j++)
           {
               str = xmlnode[i].ChildNodes.Item(0).InnerText.Trim() + "|"
               + xmlnode[i].ChildNodes.Item(1).InnerText.Trim() + "|"
               + xmlnode[i].ChildNodes.Item(2).InnerText.Trim() + "|"
               + xmlnode[i].ChildNodes.Item(3).InnerText.Trim() + "|"
               + xmlnode[i].ChildNodes.Item(4).InnerText.Trim() + "|"
               + xmlnode[i].ChildNodes.Item(5).InnerText.Trim() + "|"
               + xmlnode[i].ChildNodes.Item(6).InnerText.Trim() + "|"
               + xmlnode[i].ChildNodes.Item(7).InnerText.Trim() + "|"
               + xmlnode[i].ChildNodes.Item(j).ChildNodes.Item(0).InnerText.Trim() + "|"
               + xmlnode[i].ChildNodes.Item(j).ChildNodes.Item(1).InnerText.Trim() + "|"
               + xmlnode[i].ChildNodes.Item(j).ChildNodes.Item(2).InnerText.Trim() + "|"
               + xmlnode[i].ChildNodes.Item(j).ChildNodes.Item(3).InnerText.Trim() + "|"
               + xmlnode[i].ChildNodes.Item(j).ChildNodes.Item(4).InnerText.Trim()
               ;
               //MessageBox.Show(str);
               try
                    {
                        SqlConnection conn = new SqlConnection("Data source=YourServerName; Database=YourDBName;Integrated Security=SSPI");
                        conn.Open();
                        SqlCommand cmd = new SqlCommand("Insert into YourTableName select '" + str + "','" + FilePath + "'", conn);
                        cmd.ExecuteNonQuery();
                        //MessageBox.Show("Inserted Data Successfully !!");
                        conn.Close();
                    }
                catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                }
            }
 fs.Close();
Dts.TaskResult = (int)ScriptResults.Success;

Comments

Popular posts from this blog

SSIS vs. T-SQL

Sync SSAS Cube From One Environment to Another

Pump data from one server to another Efficiently