I'm looking for a way to use XML Bulk Load (or an other method, I'm open to alternatives) to load data from an XML file generated from an API request into a SQL database.
I can see how I would use XML Bulk Load to load the saved XML files into the database, but I'm looking for a way where I can do a nightly call the API then load the data from that into my database.
Any pointers on where to begin reading on how to do this are gratefully received!
I learnt vbscript and wrote different variations on the below to resolve this.
Function folders(strFolder)
dim objFSO
set objFSO = CreateObject("Scripting.FileSystemObject")
if not objFSO.FolderExists(Left(strFolder, InStrRev(strFolder, "\")-1)) then
wscript.echo " Target folder not found."
wscript.echo " Creating folder: " & strFolder
objFSO.createfolder(strFolder)
wscript.echo " Folder created."
end if
' Test to see if it worked..
if not objFSO.FolderExists(Left(strFolder, InStrRev(strFolder, "\" )-1)) then
wscript.echo "-=*> ERROR: Folder could not be created at: " & strFolder
wscript.echo " QUITTING"
wscript.quit
end if
End Function
Function LZ(ByVal Number)
If Number < 10 Then
LZ = "0" & CStr(Number)
Else
LZ = CStr(Number)
End If
End Function
Function TimeStamp
Dim CurrTime
CurrTime = Now()
TimeStamp = CStr(Year(CurrTime)) & "-" & LZ(Month(CurrTime)) & "-" & LZ(Day(CurrTime)) & "T" & LZ(Hour(CurrTime)) & "-" & LZ(Minute(CurrTime)) & "-" & LZ(Second(CurrTime))
End Function
dim objFile, objFSO, objHTTP, oSP, objShell, oShell, oFSO, oFile
dim strFile, strURL, strAuth, strXSD, strConnection, strObject, strComputer, strProcess
' All the folder paths
dim strPath, strPathConfig, strPathActivities
' End of folder paths
dim strLocation, strScriptPath, strScriptCmd
dim bProcess
' Loop variables
dim LoopCounter
' End of loop variables
' set locale to Ingerlund
setlocale(2057)
' Get location and path of current script for all future scripts
set oShell = createobject("wscript.shell")
strLocation = wscript.scriptfullname
set oFSO = createobject("scripting.filesystemobject")
set oFile = oFSO.getfile(strLocation)
strScriptPath = oFSO.getparentfoldername(oFile)
set oFSO = nothing
set oFile = nothing
bProcess = false
' Check to see if the Report Server is running..
set objShell = wscript.createobject("wscript.shell")
strComputer = objShell.expandenvironmentstrings("%COMPUTERNAME%")
strObject = "winmgmts://" & strComputer
For Each Process in GetObject( strObject ).InstancesOf("win32_process")
If UCase(Process.name) = UCase("ReportServer.exe") Then
bProcess = true
Exit for
End If
Next
if bProcess = false then
wscript.echo "-=*> Launching Report Server"
strProcess = chr(34) & strScriptPath & "\ReportServer.exe" & chr(34)
objShell.run strProcess
wscript.echo " Waiting for 30 seconds for Report Server to start up"
wscript.sleep 30000
end if
const ForReading = 1, ForWriting = 2, ForAppending = 8
strAuth = ""
strConnection = "provider=SQLOLEDB;data source=;database=;uid=;pwd="
' Check folders exist!
strPath = "V:\Data Analysis\Injixo\XML\Updates\" & cstr(Year(date)) & "-" & right("0" & cstr(Month(date)), 2) & "-" & right("0" & cstr(Day(date)), 2) & "\"
folders(strPath)
strPathConfig = strPath & "config\"
folders strPathConfig
' Activities sub directory
strPathActivities = strPathConfig & "activities\"
folders strPathActivities
dim objBL
set objBL = createobject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.connectionstring = strConnection
objBL.errorlogfile = strScriptPath & "\Error_Logs\update_config_activities\error_" & TimeStamp & ".log"
objBL.checkconstraints = true
objBL.keepidentity = False
wscript.echo "-=*> Beginning updates at: " & Now
' ----------------------------------------------------------------
' Begin Config_Activities
' ----------------------------------------------------------------
set objFSO = CreateObject( "Scripting.FileSystemObject" )
wscript.echo " Downloading, parsing & uploading activities"
for LoopCounter = 1 to 1100 'CONFIG FILE
if LoopCounter = 3 then
LoopCounter = 1000
end if
strFile = strPathActivities & LoopCounter & ".xml"
set objHTTP = CreateObject( "WinHttp.WinHttpRequest.5.1" )
strURL = "http://127.0.0.1:8080/activities/" & LoopCounter & ".xml?auth=" & strAuth & "&display=activity_id,deleted,name,name_short,official_name,official_name_short,paid,color"
' Set time outs to indefinite..
objHTTP.SetTimeouts 0, 0, 0, 0
objHTTP.Open "GET", strURL, false
objHTTP.Send
if objHTTP.status = 200 then
set objFile = objFSO.OpenTextFile( strFile, ForWriting, true )
objFile.Write(objHTTP.ResponseText)
objFile.Close
strScriptCmd = "%windir%\system32\cscript.exe " & chr(34) & strScriptPath & "\parse_config_activities.vbs" & chr(34) & " " & chr(34) & strFile & chr(34) & " CLIENTALL /Bypass 1 /NoCancel"
oShell.run strScriptCmd, 0, True
strXSD = strScriptPath & "\XSD\config_activities.xsd"
objBL.execute strXSD, strFile
end if
next
wscript.echo " Activities download, parsing & upload complete"
set objFSO = Nothing
set objFile = Nothing
set objHTTP = Nothing
' Update Config_Activities
wscript.echo " Triggering spUpdateActivities"
set oSP = createobject("ADODB.Connection")
oSP.open strConnection
oSP.CommandTimeout = 0
oSP.execute "exec spUpdateActivities"
wscript.echo " Stored procedure complete"
oSP.close
set oSP = Nothing
wscript.echo " Activities complete"
' ----------------------------------------------------------------
' End Config_Activities
' ----------------------------------------------------------------
wscript.echo "-=*> Finished updates at: " & Now
It's not perfect by a long stretch, and still requires a few bits to be shifted to config files and handled a bit better, but it does the job. I reference this in a batch file that contains:
@echo off
setlocal EnableDelayedExpansion
:script
pushd %~dp0
cscript //nologo %1
I re-use that batch file for different scripts, so use different shortcuts to the batch file with different scripts names in the location like so:
%windir%\System32\cmd.exe /C ""PathtoScript\launch_script.bat"" update_config_activities.vbs
Hope this helps someone in the future.
You can use the Script task to get the xml data directly from Injixo into your Dataflow. This will avoid the use of files and allow you to insert it directly into your Database
public class ScriptMain : UserComponent
{
string _xml = string.Empty;
XmlDocument _document;
XmlNodeList _activities;
/// <summary>
/// This method is called once, before rows begin to be processed in the data flow.
///
/// You can remove this method if you don't need to do anything here.
/// </summary>
public override void PreExecute()
{
base.PreExecute();
/*
* Add your code here
*/
string url = "http://xx.x.x.xxx:8080/v1/activities.xml?auth=xbase64xxxxxxxxx";
using (WebClient client = new WebClient())
{
_xml = client.DownloadString(url);
}
_document = new XmlDocument();
_document.LoadXml(_xml);
_activities = _document.SelectNodes("/activities/activity");
}
/// <summary>
/// This method is called after all the rows have passed through this component.
///
/// You can delete this method if you don't need to do anything here.
/// </summary>
public override void PostExecute()
{
base.PostExecute();
/*
* Add your code here
*/
_document = null;
_xml = string.Empty;
_activities = null;
}
public override void 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".
*/
foreach (XmlNode activity in _activities)
{
XmlNode nameNode = activity.SelectSingleNode("name");
XmlNode nameShortNode = activity.SelectSingleNode("name-short");
InjixoActivitiesBuffer.Name = (nameNode == null) ? string.Empty : nameNode.InnerText;
InjixoActivitiesBuffer.NameShort = (nameShortNode == null) ? string.Empty : nameShortNode.InnerText;
InjixoActivitiesBuffer.AddRow();
}
}
}
