9 Replies Latest reply: Jul 22, 2013 5:39 PM by Michael Lam RSS

    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>

        • EDX - SSIS - QV11

          Anyone?

            • EDX - SSIS - QV11
              Dave Riley

              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

                • EDX - SSIS - QV11

                  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

                    • EDX - SSIS - QV11
                      Dave Riley

                      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.

                      • EDX - SSIS - QV11
                        Dave Riley

                        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

                  • EDX - SSIS - QV11

                    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>

                      • EDX - SSIS - QV11

                        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

                        • EDX - SSIS - QV11
                          Amien Amien

                          Thanks for this tip Daniel!

                          • Re: EDX - SSIS - QV11

                            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>