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