Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

EDX - SSIS - QV11

Hi All

I´m currently working on developing a script that can reload QV documents from within a Script Task in SSIS 2008.

I have been looking through most of the QV11 examples provided on the QV11 community. I followed the instructions from the "QMSAPIDocumentation.chm" file (see attachment), which resulted in a working project in Visual Studio 2010 and .Net 4.0. When I run my project in VS2010 the script is successfull and the reload is completed.

However after transferring the example to the Script Task in SSIS, that being VS2008 .Net 3.5, I simply can´t get it to work. I get the following error:

*********** Error ****************

+                    User::Test          {System.InvalidOperationException: Could not find default endpoint element that references contract 'QMSAPI.IQMS' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this contract could be found in the client element.\r\n   at System.ServiceModel.Description.ConfigLoader.LoadChannelBehaviors(ServiceEndpoint serviceEndpoint, String configurationName)\r\n   at System.ServiceModel.ChannelFactory.InitializeEndpoint(String configurationName, EndpointAddress address)\r\n   at System.ServiceModel.ChannelFactory`1..ctor(String endpointConfigurationName, EndpointAddress remoteAddress)\r\n   at System.ServiceModel.ChannelFactory`1..ctor(String endpointConfigurationName)\r\n   at System.ServiceModel.EndpointTrait`1.CreateSimplexFactory()\r\n   at System.ServiceModel.EndpointTrait`1.CreateChannelFactory()\r\n   at System.ServiceModel.ClientBase`1.CreateChannelFactoryRef(EndpointTrait`1 endpointTrait)\r\n   at System.ServiceModel.ClientBase`1.InitializeChannelFactoryRef()\r\n   at System.ServiceModel.ClientBase`1..ctor()\r\n   at ST_1537051e19424c23b8b76d36f9a6ecfb.csproj.QMSAPI.QMSClient..ctor()\r\n   at ST_1537051e19424c23b8b76d36f9a6ecfb.csproj.ScriptMain.Main()}          String

************************

The error has apparently something to do with the endpoint element that reference the webservice QMSAPI.IQMS. I find this very strange since the "Service Reference" located at http://localhost:4799/QMS/Service has been added to the project and given the name "QMSAPI".

It´s pretty difficult to explain all the things I have tried, but can just say that it worked in VS2010. I don´t really have any experience with SOAP, so it could just be me missing something very simple.

Hope that one of you know why it does not work

Best Regards

Daniel

********************* C# Script **********************

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.ServiceModel;

using System.ServiceModel.Channels;

using System.ServiceModel.Configuration;

using System.ServiceModel.Description;

using System.ServiceModel.Dispatcher;

using ST_1537051e19424c23b8b76d36f9a6ecfb.csproj.QMSAPI;

namespace ST_1537051e19424c23b8b76d36f9a6ecfb.csproj

{

    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    {

        #region VSTA generated code

        enum ScriptResults

        {

            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

        };

        #endregion

        public void Main()

        {

            // TODO: Add your code here           

            //new Program().ReloadTask(Dts);

            ST_1537051e19424c23b8b76d36f9a6ecfb.csproj.QMSAPI.TaskInfo[] MyTaskList;

            ST_1537051e19424c23b8b76d36f9a6ecfb.csproj.QMSAPI.TaskInfo MyTask;

            try

            {

                // create a QMS API client

                IQMS apiClient = new QMSClient();               

                //If you want to connect to a server different from the one used when creating the service reference,

                //do as follows:                               

                //

                //NTLM only (default installation)

                //IQMS apiClient = new QMSClient("BasicHttpBinding_IQMS", "http://localhost:4799/QMS/Service");

                //

                //Certificate security

                //IQMS apiClient = new QMSClient("WSHttpBinding_IQMS", "https://remotehost:4799/QMS/Service");

                // retrieve a time limited service key

                ServiceKeyClientMessageInspector.ServiceKey = apiClient.GetTimeLimitedServiceKey();

                MyTaskList = apiClient.FindEDX("Reload of Template_Blank.qvw");

                MyTask = MyTaskList[0];

                apiClient.RunTask(MyTask.ID);

                Dts.TaskResult = (int)ScriptResults.Success;

            }

            catch (System.Exception ex)

            {

                // = ex.ToString();

                //System.Console.Out.WriteLine("Error: " + ex.ToString());

                //Dts.Variables("test").Value = ex.ToString();

                Dts.Variables["User::Test"].Value = ex.ToString();

                Dts.TaskResult = (int)ScriptResults.Success;

            }

        }

        class ServiceKeyBehaviorExtensionElement : BehaviorExtensionElement

        {

            public override Type BehaviorType

            {

                get { return typeof(ServiceKeyEndpointBehavior); }

            }

            protected override object CreateBehavior()

            {

                return new ServiceKeyEndpointBehavior();

            }

        }

        class ServiceKeyEndpointBehavior : IEndpointBehavior

        {

            public void Validate(ServiceEndpoint endpoint) { }

            public void AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters) { }

            public void ApplyDispatchBehavior(ServiceEndpoint endpoint, EndpointDispatcher endpointDispatcher) { }

            public void ApplyClientBehavior(ServiceEndpoint endpoint, ClientRuntime clientRuntime)

            {

                clientRuntime.MessageInspectors.Add(new ServiceKeyClientMessageInspector());

            }

        }

        class ServiceKeyClientMessageInspector : IClientMessageInspector

        {

            private const string SERVICE_KEY_HTTP_HEADER = "X-Service-Key";

            public static string ServiceKey { get; set; }

            public object BeforeSendRequest(ref Message request, IClientChannel channel)

            {

                object httpRequestMessageObject;

                if (request.Properties.TryGetValue(HttpRequestMessageProperty.Name, out httpRequestMessageObject))

                {

                    HttpRequestMessageProperty httpRequestMessage = httpRequestMessageObject as HttpRequestMessageProperty;

                    if (httpRequestMessage != null)

                    {

                        httpRequestMessage.Headers[SERVICE_KEY_HTTP_HEADER] = (ServiceKey ?? string.Empty);

                    }

                    else

                    {

                        httpRequestMessage = new HttpRequestMessageProperty();

                        httpRequestMessage.Headers.Add(SERVICE_KEY_HTTP_HEADER, (ServiceKey ?? string.Empty));

                        request.Properties[HttpRequestMessageProperty.Name] = httpRequestMessage;

                    }

                }

                else

                {

                    HttpRequestMessageProperty httpRequestMessage = new HttpRequestMessageProperty();

                    httpRequestMessage.Headers.Add(SERVICE_KEY_HTTP_HEADER, (ServiceKey ?? string.Empty));

                    request.Properties.Add(HttpRequestMessageProperty.Name, httpRequestMessage);

                }

                return null;

            }

            public void AfterReceiveReply(ref Message reply, object correlationState) { }

        }

        class Program

        {

            //public String EDXTaskName;

            public ST_1537051e19424c23b8b76d36f9a6ecfb.csproj.QMSAPI.TaskInfo[] MyTaskList;

            public ST_1537051e19424c23b8b76d36f9a6ecfb.csproj.QMSAPI.TaskInfo MyTask;

            public void ReloadTask(Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel Dts)

            {

                try

                {

                    // create a QMS API client

                    IQMS apiClient = new QMSClient();

                    //If you want to connect to a server different from the one used when creating the service reference,

                    //do as follows:                               

                    //

                    //NTLM only (default installation)

                    //IQMS apiClient = new QMSClient("BasicHttpBinding_IQMS", "http://remotehost:4799/QMS/Service");

                    //

                    //Certificate security

                    //IQMS apiClient = new QMSClient("WSHttpBinding_IQMS", "https://remotehost:4799/QMS/Service");

                    // retrieve a time limited service key

                    ServiceKeyClientMessageInspector.ServiceKey = apiClient.GetTimeLimitedServiceKey();

                    MyTaskList = apiClient.FindEDX("Reload of Template_Blank.qvw");

                    MyTask = MyTaskList[0];

                    apiClient.RunTask(MyTask.ID);

                    Dts.TaskResult = (int)ScriptResults.Success;

                }

                catch (System.Exception ex)

                {

                    // = ex.ToString();

                    //System.Console.Out.WriteLine("Error: " + ex.ToString());

                    //Dts.Variables("test").Value = ex.ToString();

                    Dts.TaskResult = (int)ScriptResults.Failure;

                }

            }

        }

    }

}

************************* app.config ******************************

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

          <system.serviceModel>

                    <extensions>

                              <behaviorExtensions>

                                        <add name="serviceKeyBehavior" type="ST_1537051e19424c23b8b76d36f9a6ecfb.csproj.ServiceKeyBehaviorExtensionElement, ST_1537051e19424c23b8b76d36f9a6ecfb.csproj, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>

                              </behaviorExtensions>

                    </extensions>

                    <behaviors>

                              <endpointBehaviors>

                                        <behavior name="ServiceKeyEndpointBehavior">

                                                  <serviceKeyBehavior/>

                                        </behavior>

                              </endpointBehaviors>

                    </behaviors>

                    <bindings>

                              <basicHttpBinding>

                                        <binding name="BasicHttpBinding_IQMS" closeTimeout="00:01:00"

                                          openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"

                                          allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"

                                          maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"

                                          messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"

                                          useDefaultWebProxy="true">

                                                  <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"

                                                    maxBytesPerRead="4096" maxNameTableCharCount="16384" />

                                                  <security mode="TransportCredentialOnly">

                                                            <transport clientCredentialType="Ntlm" proxyCredentialType="None"

                                                              realm="" />

                                                            <message clientCredentialType="UserName" algorithmSuite="Default" />

                                                  </security>

                                        </binding>

                              </basicHttpBinding>

                    </bindings>

                    <client>

                              <endpoint address="http://localhost:4799/QMS/Service" binding="basicHttpBinding"

                                          bindingConfiguration="BasicHttpBinding_IQMS" contract="QMSAPI.IQMS"

                                          name="BasicHttpBinding_IQMS" behaviorConfiguration="ServiceKeyEndpointBehavior"/>

                    </client>

          </system.serviceModel>

</configuration>

1 Solution

Accepted Solutions
Not applicable
Author

Hi All

I found the solution to why it didn´t work in .Net Framework 3.5.. I was missing a element parameter in the app.config file:

Missing element parameter was behaviorConfiguration="ServiceKeyEndpointBehavior" in the endpoint element

The correct file should look like this:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <system.serviceModel>

    <extensions>

      <behaviorExtensions>

        <add name="serviceKeyBehavior" type="QlikViewReloadTask.ServiceKeyBehaviorExtensionElement, QlikViewReloadTask, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>

      </behaviorExtensions>

    </extensions>

    <behaviors>

      <endpointBehaviors>

        <behavior name="ServiceKeyEndpointBehavior">

          <serviceKeyBehavior/>

        </behavior>

      </endpointBehaviors>

    </behaviors>

    <bindings>

      <basicHttpBinding>

        <binding name="BasicHttpBinding_IQMS" closeTimeout="00:01:00"

          openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"

          allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"

          maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"

          messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"

          useDefaultWebProxy="true">

          <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"

            maxBytesPerRead="4096" maxNameTableCharCount="16384" />

          <security mode="TransportCredentialOnly">

            <transport clientCredentialType="Ntlm" proxyCredentialType="None"

              realm="" />

            <message clientCredentialType="UserName" algorithmSuite="Default" />

          </security>

        </binding>

      </basicHttpBinding>

    </bindings>

    <client>

      <endpoint address="http://localhost:4799/QMS/Service" binding="basicHttpBinding"

            bindingConfiguration="BasicHttpBinding_IQMS" contract="QMSAPI.IQMS"

            name="BasicHttpBinding_IQMS" behaviorConfiguration="ServiceKeyEndpointBehavior"/>

    </client>

  </system.serviceModel>

</configuration>

View solution in original post

9 Replies
Not applicable
Author

Anyone?

flipside
Partner - Specialist II
Partner - Specialist II

I've always used the Execute Process Task in SSIS to reload QV documents - is there a reason you cannot use that rather than going through scripts?

flipside

Not applicable
Author

Hi Flipside

The script is to be used as part of a QlikView framework for the organization that i work.

The amount og document and their names are therefore not known to me. They should be extracted and reloaded dynamically from the SQL server.

And after the introduction of QlikView 11, this process has changed and is now using QMS API.

Just out of curioussity. Are you using the Execute Process Task directly on the document or how do you do it?

/Daniel

flipside
Partner - Specialist II
Partner - Specialist II

I'm not using this method currently, actually over 2 years ago since I last did.

It was directly on the document, and pretty simple to use, you just point the task at the document and working directories (using the /r QV reload command switch if I recall correctly). You should be able to pass SSIS variables into the locations so could use the script tasks to populate the variables for the execute process task to use.

Sorry I can't help more.

flipside
Partner - Specialist II
Partner - Specialist II

I've been looking at this and as I thought, it's just a case of setting up an SSIS variable at package or container level (as long as the script task can see it to manipulate the value), set the executable to qv.exe location, and use an expression to set the argument to the variable value. (My example just hard codes the value).

qv_exe_exePkgTsk.JPG

flipside

Not applicable
Author

Hi All

I found the solution to why it didn´t work in .Net Framework 3.5.. I was missing a element parameter in the app.config file:

Missing element parameter was behaviorConfiguration="ServiceKeyEndpointBehavior" in the endpoint element

The correct file should look like this:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <system.serviceModel>

    <extensions>

      <behaviorExtensions>

        <add name="serviceKeyBehavior" type="QlikViewReloadTask.ServiceKeyBehaviorExtensionElement, QlikViewReloadTask, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"/>

      </behaviorExtensions>

    </extensions>

    <behaviors>

      <endpointBehaviors>

        <behavior name="ServiceKeyEndpointBehavior">

          <serviceKeyBehavior/>

        </behavior>

      </endpointBehaviors>

    </behaviors>

    <bindings>

      <basicHttpBinding>

        <binding name="BasicHttpBinding_IQMS" closeTimeout="00:01:00"

          openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"

          allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"

          maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"

          messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"

          useDefaultWebProxy="true">

          <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"

            maxBytesPerRead="4096" maxNameTableCharCount="16384" />

          <security mode="TransportCredentialOnly">

            <transport clientCredentialType="Ntlm" proxyCredentialType="None"

              realm="" />

            <message clientCredentialType="UserName" algorithmSuite="Default" />

          </security>

        </binding>

      </basicHttpBinding>

    </bindings>

    <client>

      <endpoint address="http://localhost:4799/QMS/Service" binding="basicHttpBinding"

            bindingConfiguration="BasicHttpBinding_IQMS" contract="QMSAPI.IQMS"

            name="BasicHttpBinding_IQMS" behaviorConfiguration="ServiceKeyEndpointBehavior"/>

    </client>

  </system.serviceModel>

</configuration>

Not applicable
Author

Hi Daniel,

Excellent job. So this code is working for you?

I have modifed the config file , added web reference, other assemblies but i am stuck at following:

    // create a QMS API client

                IQMS apiClient = new QMSClient();

The type or namespace IQMS could not be found. I am not sure what am i missing. Can you please help me out?

Thanks,

Ruby

amien
Specialist
Specialist

Thanks for this tip Daniel!

Not applicable
Author

Hi Daniel:

I tried the using your example and I still get the same type of error.  Below is my app.config

<?xml version="1.0"?>

<configuration>

          <system.serviceModel>

                    <extensions>

                              <behaviorExtensions>

                                        <add name="serviceKeyBehavior" type="ST_e7c60077b2a74b648fdb5185f981190b.csproj.ServiceSupport.ServiceKeyBehaviorExtensionElement,

                                                   ST_e7c60077b2a74b648fdb5185f981190b.csproj, Version=1.0.0.0, Culture=neutral,

                                                   PublicKeyToken=null"/>

                              </behaviorExtensions>

                    </extensions>

                    <behaviors>

                              <endpointBehaviors>

                                        <behavior name="ServiceKeyEndpointBehavior">

                                                  <serviceKeyBehavior/>

                                        </behavior>

                              </endpointBehaviors>

                    </behaviors>

                    <bindings>

                              <basicHttpBinding>

                                        <binding name="BasicHttpBinding_IQMS" closeTimeout="00:01:00" openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00" allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard" maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536" messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered" useDefaultWebProxy="true">

                                                  <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384" maxBytesPerRead="4096" maxNameTableCharCount="16384"/>

                                                  <security mode="TransportCredentialOnly">

                                                            <transport clientCredentialType="Ntlm" proxyCredentialType="None" realm=""/>

                                                            <message clientCredentialType="UserName" algorithmSuite="Default"/>

                                                  </security>

                                        </binding>

                                        <binding name="BasicHttpBinding_IQTService" closeTimeout="00:01:00" openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00" allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard" maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536" messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered" useDefaultWebProxy="true">

                                                  <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384" maxBytesPerRead="4096" maxNameTableCharCount="16384"/>

                                                  <security mode="TransportCredentialOnly">

                                                            <transport clientCredentialType="Ntlm" proxyCredentialType="None" realm=""/>

                                                            <message clientCredentialType="UserName" algorithmSuite="Default"/>

                                                  </security>

                                        </binding>

                              </basicHttpBinding>

                    </bindings>

                    <client>

                              <endpoint address="http://sct-inf-qv:4799/QMS/Service" binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_IQMS" contract="QMSAPIService.IQMS" name="BasicHttpBinding_IQMS" behaviorConfiguration="ServiceKeyEndpointBehavior"/>

                              <endpoint address="http://localhost:4799/ANY/Service" binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_IQTService" contract="QMSAPIService.IQTService" name="BasicHttpBinding_IQTService"/>

                    </client>

          </system.serviceModel>

<startup><supportedRuntime version="v2.0.50727"/></startup></configuration>