Loading XML files from dynamic URL using an SSIS script task
Hello people, I'm currently working on a hobby project which involves getting market data for the MMO eve online into a database so that I can track the value of a characters assets ingame. I currently work with downloading around 24 xml files each morning and letting them load into the database, getting the xml files takes to long this way so I started working on automating the process. A few problems arise, altough all market xml look exactly the same their xsd are not compatible, so simply running through the webpages downloading the xml to a local xml file and then processing does not work. I created the following script peacing togheter scripts from varios sources using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime; using System.Xml; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { //Initialize XML Document to read the XML file private XmlDocument xDoc = new XmlDocument(); //String sURL = "http://www.eve-markets.net/xml.php?listid=mg18&key="; //XmlTextReader URL = new XmlTextReader("http://www.eve-markets.net/xml.php?listid=mg" & Microsoft.SqlServer.Dts.Runtime.Wrapper.Variable["User::MarketGroup"].Value.ToString()); XmlTextReader URL = new XmlTextReader("http://www.eve-markets.net/xml.php?listid=mg18&key="); public override void PreExecute() { base.PreExecute(); //Provide the path to read the XML file and load the xml document // xDoc.Load(@"G:\EOData\Files\XML\Minerals.xml"); xDoc.Load(URL); } public override void CreateNewOutputRows() { //Iterate through each node which has the value "Employee" // "//Employee" is the xpath to fetch all occurences of Employee node in the XML foreach (XmlNode xNode in xDoc.SelectNodes("//type")) { //Add new row to the output buffer for each employee node in the XML file XMLOutputBuffer.AddRow(); // //Assign values to the columns. // //Read the 1st attribute of the node Employee XMLOutputBuffer.typeID = xNode.Attributes[0].Value; // //Read the 1st Child node of the node Employee XMLOutputBuffer.ID = xNode.Attributes[0].Value; // //Read the 2nd Child node of the node Employee XMLOutputBuffer.name = xNode.ChildNodes[0].InnerText; XMLOutputBuffer.price = xNode.LastChild.ChildNodes[0].InnerText; } //XMLOutputBuffer.AddRow(); //XMLOutputBuffer.typeID = "18"; //XMLOutputBuffer.ID = "18"; //XMLOutputBuffer.name = "trt"; //XMLOutputBuffer.price = "3.00"; } public override void PostExecute() { base.PostExecute(); } } Now this will read the xml for minerals in the game off the eve markets webpage that holds the prices for minerals http://www.eve-markets.net/xml.php?listid=mg18&key= Now I need it so i can have a dynamic URL instead of a static, The URL is build up like follows http://www.eve-markets.net/xml.php with as parameter listid=mg, where mg stands for marketgroup. If anyone can help me, it would be greatly appreciated
October 26th, 2011 1:32pm

I suggest creating a object variable and fill it by anything you want. After put your dataflow task which includes your script component into foreachloop container which loops with ForeachADO Enum. Use variable within script component and do a text concatenation of the URL Senior BI Consultant & PM @ Nexum Bogazici If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2011 10:33am

ForEachLoop Editor: Script Component Editor: Script: string sURL = "http://www.eve-markets.net/xml.php?listid="; XmlTextReader URL = new XmlTextReader(sURL+Variables.LoopingListId.ToString()+"&key="); Senior BI Consultant & PM @ Nexum Bogazici If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
November 19th, 2011 10:37am

Txn for the answers, in the meantime I also kept looking but every solution I tried hit deadend so I decided to change my coding from visual C# to VB 2008. The package now looks like this, the only thing missing is the archive sql task that puts the itemprices in a history table The revised code ' Microsoft SQL Server Integration Services Script Component ' Write scripts using Microsoft Visual Basic 2008. ' ScriptMain is the entry point class of the script. Imports System Imports System.Xml Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports Microsoft.SqlServer <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _ <CLSCompliant(False)> _ Public Class ScriptMain Inherits UserComponent Private xDoc As XmlDocument = New XmlDocument() Public sURL As String '= "http://www.eve-markets.net/xml.php?listid=mg" + 18 + "&key=" Public MG As String Public xNode As XmlNode Public Overrides Sub PreExecute() ' ' Add your code here for preprocessing or remove if not needed ' 'Dim var As IDTSVariables100 'Me.VariableDispenser.LockOneForRead("MarketGroup", var) 'MG = CType(var("MarketGroup").Value, String) 'var.Unlock() 'MG = CStr(Dts.Variables("User::MarketGroup").Value) MG = Me.Variables.MarketGroup sURL = "http://www.eve-markets.net/xml.php?listid=mg" & MG & "&key=" xDoc.Load(sURL) MyBase.PreExecute() End Sub Public Overrides Sub PostExecute() MyBase.PostExecute() ' ' Add your code here for postprocessing or remove if not needed ' You can set read/write variables here, for example: ' Me.Variables.MyIntVar = 100 '' End Sub Public Overrides Sub CreateNewOutputRows() ' ' Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer". ' For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput". ' ' xNode.SelectNodes("//type") 'Add new row to the output buffer for each employee node in the XML file For Each xNode As XmlNode In xDoc.SelectNodes("//type") Me.XMLOutputBuffer.AddRow() Me.XMLOutputBuffer.typeID = xNode.Attributes(0).Value.ToString Me.XMLOutputBuffer.ID = xNode.Attributes(0).Value.ToString Me.XMLOutputBuffer.Name = xNode.ChildNodes(0).InnerText.ToString Me.XMLOutputBuffer.price = xNode.LastChild.ChildNodes(0).InnerText.ToString Next End Sub End Class
Free Windows Admin Tool Kit Click here and download it now
November 19th, 2011 11:17am

I am glad that i've helped you. I would appreciate if you could change type of the thread from discussion to Question? Senior BI Consultant & PM @ Nexum Bogazici If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.
November 19th, 2011 11:46am

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

Other recent topics Other recent topics