使用API的XML输出大容量上载到SQL server。

Question

我正在寻找一种方法来使用XML批量加载(或者其他方法，我对其他方法持开放态度)将数据从从API请求生成的XML文件加载到SQL数据库中。

我可以看到如何使用XML批量加载将保存的XML文件加载到数据库中，但我正在寻找一种方法，可以在夜间调用API，然后将数据从API加载到我的数据库中。

任何关于从哪里开始阅读如何做到这一点的建议都将受到感谢！

Answer 1

我学习了vbscript，并在下面写了不同的变体来解决这个问题。

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

它在很长一段时间内并不完美，仍然需要将一些内容转移到配置文件中，并进行更好的处理，但它已经完成了工作。我在一个批处理文件中引用它，该批处理文件包含：

@echo off
setlocal EnableDelayedExpansion

:script
pushd %~dp0
cscript //nologo %1

我对不同的脚本重用了该批处理文件，因此在以下位置使用不同的快捷方式指向具有不同脚本名称的批处理文件：

%windir%\System32\cmd.exe /C ""PathtoScript\launch_script.bat"" update_config_activities.vbs

希望这对将来的人有所帮助。

Answer 2

您可以使用脚本任务将xml数据直接从Injixo获取到数据流中。这将避免使用文件，并允许您将其直接插入到数据库中

 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();
        }

    }

}