EDX - SQL Server

[#1]

Hi

I am trying to get some help on EDX.. What I want to do is trigger a task in QV Management Console, once our overnight load sequency in SQL Server has finished. Can this be triggered as a command from SQL? And what do I nned to do?

Any help is appreciated.

Regards

[Anonymous]

Thanks!  That is what we were hoping to hear!

JS

[Anonymous]

Just to verify, this code still works on QV10 SR3!

JS

[Anonymous]

Thanks, i use the code from time to time!

I use it to reload the qlikview frontend from SSIS when i have an SQL datawarehouse.

I can see if the EDX has started succesfully, but i cannot see if the task's depending on the EDX also have completed succesfully.

Is it posible in the qvs-api to create a vba-code to se the status for a task?

Cause then i can send a message back to the SSIS that the qlikview reload has also finished succesfully.

If it is, please help me on this one with an example.

henrik

[Anonymous]

Here is what I use, hopefully you can pull out what you need to fit your code!

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum
 Public Sub Main()
        Dim xmlhttp, TaskName, EDXStatus, EDXStartStatus, EDXStartTime, EDXEndTime
        TaskName = Trim(Dts.Variables("User::EDXTaskName").Value.ToString())
        EDXStatus = "Running"                   'Set Status for Initial run through do while  loop
        EDXStartTime = FormatDateTime(Now())    'Get the start time for the QVW
        EDXEndTime = EDXStartTime               'Initial set of the EndTime
        If TaskName <> "NonEDXTask" Then
            EDXStartStatus = StartEDX(TaskName)
            Do While EDXStatus = "Running" And EDXStartStatus = "Success"
                EDXStatus = GetEDXValue(TaskName, "Status")
                If EDXStatus = "Running" Then
                    Thread.Sleep(10000)   '600000 = 10 min
                End If
            Loop
            If EDXStatus = "Waiting" Then       'Only assign new end time if status is waiting.
                EDXEndTime = FormatDateTime(Now())
            ElseIf EDXStatus = "Failed" Then
                EDXStartStatus = EDXStatus
            End If
        Else
            EDXStartStatus = TaskName           'Assigns NonEDXTask to the Status
        End If  ' If Not NonEDXTask
        Dts.Variables("User::EDXStatus").Value = EDXStartStatus
        Dts.Variables("User::EDXBeginTime").Value = EDXStartTime
        Dts.Variables("User::EDXEndTime").Value = EDXEndTime
    End Sub
    Function StartEDX(ByVal TaskName)
        '  This Function will Start an EDX Task and return the status of the call.  
        Dim requestKey, xmlhttp
        xmlhttp = CreateObject("msxml2.xmlhttp.3.0")
        xmlhttp.open("post", "http://YOUR_SERVER_NAME_HERE:4720/qtxs.asmx", False)
        xmlhttp.send("<Global method=""GetTimeLimitedRequestKey"" />" & vbCrLf)
        requestKey = xmlhttp.responseXML.selectSingleNode("//GetTimeLimitedRequestKeyResult").text
        xmlhttp.open("post", "http://YOUR_SERVER_NAME_HERE:4720/qtxs.asmx", False)
        xmlhttp.send("<Global method=""RequestEDX"" key=""" & requestKey & """><i_TaskIDOrTaskName>" & TaskName & "</i_TaskIDOrTaskName><i_Password /><i_VariableName /><i_VariableValueList /></Global>" & vbCrLf)
        StartEDX = xmlhttp.responseXML.selectSingleNode("//TaskStartResult").text
        Thread.Sleep(20000)   '600000 = 10 min  -- Needed here to give time for EDX to start
    End Function
    Function GetEDXValue(ByVal TaskName, ByVal StatusOrDate)
        Dim requestKey, xmlhttp
        xmlhttp = CreateObject("msxml2.xmlhttp.3.0")
        xmlhttp.open("post", "http://YOUR_SERVER_NAME_HERE:4720/qtxs.asmx", False)
        xmlhttp.send("<Global method=""GetTimeLimitedRequestKey"" />" & vbCrLf)
        requestKey = xmlhttp.responseXML.selectSingleNode("//GetTimeLimitedRequestKeyResult").text
        xmlhttp.open("POST", "http://YOUR_SERVER_NAME_HERE:4720/qtxs.asmx", False)
        xmlhttp.send("<Global method=""GetTaskStatus"" key=""" & requestKey & """><TaskNameOrId>" & TaskName & "</TaskNameOrId></Global>" & vbCrLf)
        If StatusOrDate = "Status" Then
            GetEDXValue = xmlhttp.responseXML.selectSingleNode("//Status").text
        ElseIf StatusOrDate = "Time" Then
            GetEDXValue = xmlhttp.responseXML.selectSingleNode("//LastExec").text
        End If
    End Function
End Class

[Anonymous]

Thanks,

If I'm reading your script correctly, it's evaluate the success of the EDX task, not the document depending on the EDX task or am I wrong?

Starting the EDX takes about a second to complete, but the document to reload may take a lot longer, and i need to se if the document don't fail for some reason during this reload.

henrik

[Anonymous]

Take a look at the loop.  Basically I keep looping on the EDX task and checking to see if it is running.  I only reload one qvw (not a chain) at a time from this script.  When the EDX task finishes or errors out the the script will assign a success or failure depending on what is returned.

[Anonymous]

Got it!

Thanks!

[Anonymous]

Just to inform you all,

I used a modification of the scrip from jsomsen.

It works well.

henrik

[#10]

Hi all

I´m trying to execute the code below in a VB 2012 environment, previous to paste the code on QV document, but I always receive the same exception on line:

requestKey = xmlhttp.responseXML.selectSingleNode("//GetTimeLimitedRequestKeyResult").text

"NullReferenceException"

Please, help me!!!!

Best Regards

Jeremias