Archive

Posts Tagged ‘WCF’

Sending IDOCs to SAP using SSIS – MSSQL Integration Services

February 7, 2012 6 comments

Normally receiving and sending IDOCs can be easily done by using BizTalk Adapter for mySAP Business in BizTalk Server which is included in Microsoft BizTalk Adapter Pack 2.0. But what if I want to send an IDOC using SSIS and BizTalk is installed on a different server? Below are the steps on how you can do that.

Note: Since SSIS 2008 only supports <= .NET 3.5 all custom assemblies that will be called within it should be using this framework.

Prerequisites:

1. WCF LOB SDK Adapter (WCF-LOB-Adapter-SDK-2010-x86.exe) – Get it here. or in BizTalk DVD Installer under BizTalk Server\ASDK_X86

2. Microsoft BizTalk Adapter Pack 2.0 x86 – Get Evaluation version here. or in BizTalk DVD Installer under BizTalk Server\AdapterPack_x86

3. SAP Libraries x86 – this needs to be installed on C:\Windows\SysWOW64 folder. See section for SAP Adapter in this Microsoft Adapter Pack 2.0 install guide.

IDOC Library:

Next step is to create an IDOC WCF Client that you can call inside SSIS.

1. Open Visual Studio -> Create a new Project Library.

2. Right click the Project -> Click Add Adapter Service Reference.

3. Set the binding to sapBinding and set the configure a URI to:

sap://CLIENT={SAP Client ID};LANG=EN;@A/{Application Server Host}/{System Number}?ListenerGwServ={Gateway Service}&ListenerGwHost={Gateway Host}&ListenerProgramId={Program Id}&RfcSdkTrace=False&AbapDebug=False.  See this help for the configuration

4. Click Configure and set the credentials, then click Connect. Since we are sending IDOCS the contract type should be set to Client (Outbound operations).

5. In the Category, browse for the specific type of IDOC and version, when generating the schema you might encounter an error : “Error returned by RfcCallReceiveEx while calling RFC: IDOCTYPE_READ_COMPLETE..”, It means that you’re selecting an incorrect version of the IDOC, you should ask your SAP resource to identify the Release Number.

Select the Send and click Add. Click OK. This will generate a WCF client that you can call inside SSIS.

6. Sign the Assembly, compile and deploy to GAC then copy to C:\windows\Microsoft.NET\Framework\v2.0.50727.

It’s necessary to copy to this folder so you can add a reference to it in SSIS.

SSIS:

1. Add a data flow.

2. Set OLE DB Source / File / etc.

3. Add a Script Task, set the script task to Script Destination. Double click the script task and change the target framework to .NET 3.5 by going to project properties.

Add reference to the IDOC Library.

Create the IDOC object and pass it to the IDOC WCF Client.

References:

Using Scripting Destination in SSIS

Sample on how to call WCF client in SSIS
To call IDOC WCF Client:

I’ve modified the code and copied it from http://technet.microsoft.com/en-us/library/cc185231(BTS.10).aspx


using System;
using System.Collections.Generic;
using System.Text;

// Add WCF, WCF LOB Adapter SDK, and SAP adapter namepaces
using System.ServiceModel;     //Change the Project to target .NET 3.5 and reference System.ServiceModel
using Microsoft.Adapters.SAP; //This assembly is under the Microsoft Adapter Pack 2.0 install folder
using Microsoft.ServiceModel.Channels; //This assembly is under WCF LOB SDK Install folder

// Include this namespace for WCF LOB Adapter SDK and SAP exceptions
using Microsoft.ServiceModel.Channels.Common;

namespace SapTypeIDOCClient
{
    class Program
    {
        static void Main(string[] args)
        {
            // variable for the IDOC client
            IdocClient idocClient = null;

            Console.WriteLine("IDOC XML client sample started");
            try
            {

		//Construct IDOC Object here. MATMAS01, PHRMAS01, SUBMAS
		var idocObj = new {IDOC OBJECT}

		//Assign Properties and segments of IDOCS here.
		//idocObj.

                // Variable for the GUID
                System.Nullable<System.Guid> adapterTxGuid;
                // string to hold the Idoc data
                string idocData;
                // string to hold the SAP transaction ID (TID)
                string sapTxId;

                // The client can be configured from app.config, but it is
                // explicitly configured here for demonstration.
                // set AutoConfirmSentIdocs property to true
                SAPBinding binding = new SAPBinding();
                binding.AutoConfirmSentIdocs = true;

                // Set endpoint address
                EndpointAddress endpointAddress = new EndpointAddress("{SAP Connection String see settings above");

                // Create client and set credentials
                idocClient = new {IDOC WCF Client}(binding, endpointAddress);
                idocClient.ClientCredentials.UserName.UserName = "YourUserName";
                idocClient.ClientCredentials.UserName.Password = "YourPassword";

                // Open the client and send the Idoc
                idocClient.Open();

                //Get a new GUID to pass to SendIdoc. You can also assign a null.
                //value to have the adapter generate a GUID.
                adapterTxGuid = Guid.NewGuid().ToString();

		//We are using the Send Method, it accepts a strongly typed iDOC (XML), SendIdoc sends FlatFile IDOC
                idocClient.Send(idocData, ref adapterTxGuid);

                // The AutoConfirmSentIdocs binding property is set to true, so there is no need to
                // confirm the IDOC. If this property is not set to true, you must call the
                // RfcConfirmTransID method of a TrfcClient with adapterTxGuid to
                // confirm the transaction on the SAP system.

                // Get SAP tx id from GUID
                sapTxId = SAPAdapterUtilities.ConvertGuidToTid((Guid) adapterTxGuid);

                Console.WriteLine("IDOC sent");
                Console.WriteLine("The SAP Transaction Id is : " + sapTxId);

            catch (Exception ex)
            {
                Console.WriteLine("Exception is: " + ex.Message);
                if (ex.InnerException != null)
                {
                    Console.WriteLine("Inner Exception is: " + ex.InnerException.Message);
                }
            }
            finally
            {
                // Close the IDOC client
                if (idocClient != null)
                {
                    if (idocClient.State == CommunicationState.Opened)
                        idocClient.Close();
                    else
                        idocClient.Abort();
                }
            }

        }
    }
}
Advertisements

IIS 7 The protocol ‘net.tcp’ is not supported, Enabling net.tcp in IIS using WAS

November 14, 2011 Leave a comment

Without the proper configuration, IIS will throw the error below wheneven you try to use net.tcp:

The protocol ‘net.tcp’ is not supported

Below is the configuration on how you can enable net.tcp in IIS 7.

1. Add WCF Activation, to do this go to Server Management -> Features -> Click Add Features

2. After installing the WCF Activation it may throw an error:

Could not load type ‘System.ServiceModel.Activation.HttpModule’ from assembly ‘System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’

To fix it, open the command prompt (Run as administrator) and enter the ff:

 C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis -iru

3. Issue an iireset on command prompt.

4. Go to the Web Site -> Right Click -> Edit Bindings – > Click Add, in the type select net.tcp and in the binding information set it to 808:*

5. Go to the Web Site -> In the Actions Pane click Advance Settings  -> In the Behavior change it to http,net.tcp.

Repeat steps for every WCF Service Application (Virtual Directories) under the Web Site.

Categories: IIS Tags: , , ,

SSRS – Calling Reporting Services using WCF and using the HTML output for email notification

October 27, 2011 6 comments

Recently we had a project that requires us to use Reporting Server for email notification. In short,  the HTML output of the Report is assigned to the body of the email notification.

1) First step is to create a proxy library utility containing reference to Reporting Server Web Service. To do this Click Add Service Reference and set url to:

 http://{ReportServerName}/ReportServer/ReportExecution2005.asmx, In the collection type set it to System.Collections.Generic.List

Note: Version of SSRS is 2005

Update!: Problem with Authorization:

“The HTTP request is unauthorized with client authentication scheme ‘Negotiate’. The authentication header received from the server was ‘NTLM’.”

To fix problem with Authorization issue in LoadReport method:

1. Added new method called GetDefaultBinding.

2.Also a new parameter in Export method named  useDefaultBinding with value set to false was added. If this value is set to true, it would use the binding from GetDefaultBinding and will use DefaultNetworkCredentials.

Note: Network credential is not being used when useDefaultBinding is set to true.

Updated code is as follows:

  /// <summary>
    /// Utility class that renders and exports a SQL Reporting Services report into the specified output format.
    /// </summary>
    public static class ReportExporter
    {
        private static string GetExportFormat(ExportFormat f)
        {
            switch (f)
            {
                case ExportFormat.XML: return "XML";
                case ExportFormat.CSV: return "CSV";
                case ExportFormat.Image: return "IMAGE";
                case ExportFormat.PDF: return "PDF";
                case ExportFormat.MHTML: return "MHTML";
                case ExportFormat.HTML4: return "HTML4.0";
                case ExportFormat.HTML32: return "HTML3.2";
                case ExportFormat.Excel: return "EXCEL";
                case ExportFormat.Word: return "WORD";

                default:
                    return "PDF";
            }
        }

        /// <summary>
        /// Exports a Reporting Service Report to the specified format using Windows Communication Foundation (WCF) endpoint configuration specified.
        /// </summary>
        /// <param name="url">Address of Report Web Service</param>
        /// <param name="clientCredentials">Network Credential to use to connect to the web service</param>
        /// <param name="reportName">Friendly name of Reporting Services Report to execute</param>
        /// <param name="report">Reporting Services Report to execute</param>
        /// <param name="parameters">report parameters</param>
        /// <param name="format"></param>
        /// <param name="output">rendering output result in bytes</param>
        /// <param name="extension">output format file extension</param>
        /// <param name="mimeType">output MIME type</param>
        /// <param name="encoding">output encoding</param>
        /// <param name="warnings">warnings (if any)</param>
        /// <param name="streamIds">stream identifiers for external resources (images, etc) that are associated with a given report</param>
        /// <param name="dataSourceCredentials"></param>
        public static void Export(string url, System.Net.NetworkCredential clientCredentials, string reportName, string report, ParameterValue[] parameters,
            ExportFormat format, out byte[] output, out string extension, out string mimeType, out string encoding, out Warning[] warnings, out string[] streamIds, DataSourceCredentials[] dataSourceCredentials=null, bool UseDefaultBinding=false)
        {

            Binding binding;
            if (UseDefaultBinding)
            {
                binding = GetDefaultBinding();
            }
            else
            {
                binding = GetBinding();
            }

            using (var webServiceProxy = new ReportExecutionServiceSoapClient(binding, new EndpointAddress(url)))
            {
                webServiceProxy.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

                if (UseDefaultBinding)
                {
                    webServiceProxy.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
                }
                else
                {
                    webServiceProxy.ClientCredentials.Windows.ClientCredential = clientCredentials;
                }

                // Init Report to execute
                ServerInfoHeader serverInfoHeader;
                ExecutionInfo executionInfo;

                try
                {

                    ExecutionHeader executionHeader = webServiceProxy.LoadReport(null, report, null, out serverInfoHeader, out executionInfo);

                    // Attach Report Parameters
                    webServiceProxy.SetExecutionParameters(executionHeader, null, parameters, null, out executionInfo);

                    //Set Credentials
                    if(dataSourceCredentials!=null)
                        webServiceProxy.SetExecutionCredentials(executionHeader, null, dataSourceCredentials, out executionInfo);

                    // Render
                    webServiceProxy.Render(executionHeader, null, GetExportFormat(format), null, out output, out extension, out mimeType, out encoding, out warnings, out streamIds);
                }
                catch (FaultException faultExc)
                {
                    throw new Exception(string.Format("Encountered error while generating the report: '{0}', Report Url: '{1}', Web Service Url: '{2}' Message: '{3}'",  reportName, report, url, faultExc.Message ), faultExc);
                }
            }
        }

        /// <summary>
        /// Export a Reporting Services to HTML
        /// </summary>
        /// <param name="url">Address of Report Web Service</param>
        /// <param name="clientCredentials">Network Credential to use to connect to the web service</param>
        /// <param name="reportName">Friendly name of Reporting Services Report to execute</param>
        /// <param name="report">Reporting Services Report to execute</param>
        /// <param name="parameters">report parameters</param>
        /// <param name="dataSourceCredentials"></param>
        /// <returns></returns>
        public static string ExportToHTML(string url, System.Net.NetworkCredential clientCredentials, string reportName, string report, ParameterValue[] parameters, DataSourceCredentials[] dataSourceCredentials = null, bool UseDefaultBinding = false)
        {
            Warning[] warn;
            string[] p;

            byte[] output;
            string extension, mimeType, encoding;

            //Get HTML
            ReportExporter.Export(url,
                                  clientCredentials,
                                  reportName,
                                  report,
                                  parameters.ToArray(),
                                  ExportFormat.HTML4,
                                  out output,
                                    out extension,
                                    out mimeType,
                                    out encoding, out warn, out p, dataSourceCredentials, UseDefaultBinding
                                    );

            System.Text.Encoding enc = System.Text.Encoding.GetEncoding("UTF-8");
            string html = enc.GetString(output);
            return html;
        }

        /// <summary>
        /// Returns the binding to use, eliminates the app.config
        /// </summary>
        /// <returns></returns>
        internal static Binding GetBinding()
        {
            BasicHttpBinding binding = new BasicHttpBinding();
            binding.MaxBufferPoolSize = 0;
            binding.MaxReceivedMessageSize = 5242880;
            binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
            binding.TextEncoding = System.Text.Encoding.UTF8;
            binding.MessageEncoding = WSMessageEncoding.Text;
            binding.TransferMode = TransferMode.Buffered;
            binding.UseDefaultWebProxy = true;
            binding.ReaderQuotas = new System.Xml.XmlDictionaryReaderQuotas
              {
                  MaxArrayLength = 2147483647,
                  MaxBytesPerRead = 2147483647,
                  MaxDepth = 2147483647,
                  MaxNameTableCharCount = 2147483647,
                  MaxStringContentLength = 2147483647
              };
            binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows;
            return binding;
        }

        /// <summary>
        /// Returns the binding to use, eliminates the app.config
        /// </summary>
        /// <returns></returns>
        internal static Binding GetDefaultBinding()
        {
            BasicHttpBinding binding = new BasicHttpBinding();
            binding.MaxBufferPoolSize = 0;
            binding.MaxReceivedMessageSize = 5242880;
            binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
            binding.TextEncoding = System.Text.Encoding.UTF8;
            binding.MessageEncoding = WSMessageEncoding.Text;
            binding.TransferMode = TransferMode.Buffered;
            binding.UseDefaultWebProxy = true;
            binding.ReaderQuotas = new System.Xml.XmlDictionaryReaderQuotas
            {
                MaxArrayLength = 2147483647,
                MaxBytesPerRead = 2147483647,
                MaxDepth = 2147483647,
                MaxNameTableCharCount = 2147483647,
                MaxStringContentLength = 2147483647
            };
            binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm;
            return binding;
        }
        #endregion
    }

Updated Unit Test to use default bindings:

[TestMethod]
        public void GetHTMLReportTest()
        {

            //Set network credentials
            string userName = "NotUsed";
            string password = "NotUsed";
            string domain = "NotUsed";
            System.Net.NetworkCredential credentials = new System.Net.NetworkCredential(userName, password, domain);
            string outputPath = @"C:\TestFolder";

            //Url
            string path = "/MyReport/Invoice";

            //Parameters
            List<ParameterValue> parameters = new List<ParameterValue>();
            parameters.Add(new ParameterValue { Name = "AcctId", Value = "0001" });

            //Get HTML
            string html = ReportExporter.ExportToHTML("http://HypV-Rpaulo/ReportServer/ReportExecution2005.asmx",
                                  credentials,
                                  "My invoice report",
                                  path ,
                                  parameters.ToArray(),UseDefaultBinding:true
                                  );

            Assert.AreEqual(true, html.Length > 0, "No HTML generated");
            Console.WriteLine(html);
            System.IO.File.WriteAllText(outputPath + "result.html", html);

        }

The library can be used in BizTalk (deployed in GAC) , SSIS 2005 or 2008(deployed in GAC, .NET version is <=3.5 and is copied to %Windir%\Microsoft.NET\Framework\v2.0.50727 folder to be able to referenced in a SSIS Package..

WCF Using Windows Authentication and SqlRoleProvider over basicHttp

July 13, 2011 3 comments

This tutorial is about building a sample WCF Service that makes use of  Windows(Active Directory) as Authentication mechanism and SQLRoleProvider for the Authorization.

1. Lets start by creating a new WCF Service Application. Open VS2010, File New Project under WCF select WCF Service Application. For the name just use the default: WcfService1.

2.  New Project was created, Go to the code of Service1.svc

The goal is: when a client application calls the GetData method, it should have a valid windows account (active directory account) and should be a member of a Role (SqlRole) that we will create later.

Press F6 to compile the project.

3. Next step is to configure service to use Windows and Sql Role Provider.

Right click web.config from WcfService1 project and select Edit WCF Configuration (if this is not available, Click Tools -> WCF Service Configuration Editor then close it, right click the web.config again the menu should be available)

WCF Configuration Editor Opens.

 

WCF Editor Step 1:  Create Service

In the Services Tab, click Create New Service.  In the service type, click Browse -> bin folder-> Select WcfService1.dll -> then Select WcfService1.Service1 then click Open.

Click Next, Contract should be WcfService1.IService1.

Click Next, for the communication mode select HTTP.

Click Next, for the interoperability select Basic Web Services interoperability.

Click Next, for the address type leave it empty, click Finish. New service was added:

Add BaseAddress, Go to Host -> In Base Address click New:  http://localhost:8000/WcfService1

 

WCF Editor Step 2:  Add Binding Configuration

In the Configuration Tab, go to Bindings -> Click New Binding Configuration, select basicHttpBinding from the list. Set the name to basicHttpBindingConfig.

Go to security tab set the following:

(General) Mode: TransportCredentialOnly

(TransportSecurityProperties) TransportClientCredentialType: Ntlm

Set the existing endPoint (basicHttp) to use the binding configuration that we just created.

To do this: go to Endpoints -> Click the first endPoint then on the general tab -> Endpoint Properties ->

Click Binding Configuration then select basicHttpBindingConfig.

 

 

WCF Editor Step 3:  Configure Service Behaviors

Go to Advance -> Service Behaviors

Modify the existing behavior and rename it to WcfService1.ServiceBehavior.

Click Add and select serviceAuthorization from the list.

Go to general Tab and set the ff:

PrincipalPermissionMode: UseAspNetRoles

RoleProviderName: WcfService1RoleProvider (we would configure this later on).

Update the service to use the service behavior. Go to Services -> WcfService1.Service1 in Behavior configuration select WcfService1.ServiceBehavior.

Click File -> Save.

4. After the WCF editor configuration next step is we need to configure the SQLRoleProvider.

SQLRoleProvider Step 1:  Create SqlRoleProvider database

Open Visual Studio 2010 command prompt and type the following command:

aspnet_reqsql -S {YourDatabaseServer} -E -A r

SQLRoleProvider Step 2:  Update Web.config

Copy and paste the following after configuration tag.

  <connectionStrings>
    <!–Setup Connection String for SQLRoleProvider –>
    <add name=”ASPNetDBConnString” connectionString=”Initial Catalog=aspnetdb;data source={YourDatabaseServer};Integrated Security=SSPI;”/>
  </connectionStrings>

Under system.web add the ff:

  <!–Setup Role Provider to be used for authorizations –>
    <roleManager enabled=”true” defaultProvider=”WcfService1RoleProvider” >
      <providers>
        <clear/>
        <add name=”WcfService1RoleProvider
             connectionStringName=”ASPNetDBConnString”
             applicationName=”WcfService1
             type=”System.Web.Security.SqlRoleProvider” />
      </providers>
    </roleManager>

Click File -> Save.

SQLRoleProvider Step 3:  Assign Roles to windows account

We will create a new role named: PowerUsers and only windows account that has this role will be able to execute the GetData method.

Execute the following script:

USE aspnetdb
GO

— Create a new role
EXEC aspnet_Roles_CreateRole ‘WcfService1’, ‘PowerUsers’

— Assign Windows Account to a Role
EXEC aspnet_UsersInRoles_AddUsersToRoles ‘WcfService1’, ‘{YourDomain\YourUserName}‘, ‘PowerUsers’, 8

5. Last step is to implement the role-based security on GetData method.  Go to WcfService1 project and do the ff:

Add reference to: System.Security

Open Service1.svc code – > Add : using System.Security.Permissions

Update GetData method to:

 

Testing:

For the testing i created a console application that calls the webservice with the following code:

 WcfService1.Service1Client client = new WcfService1.Service1Client();
Console.WriteLine(“Data From server: ” + client.GetData(4));
Console.ReadKey();

 

Sample Output (Calling service with an active directory account that is member of PowerUsers role):

 Sample Output (Calling service with an active directory account that is not a member of PowerUsers role):

 To remove the active directory account from a specific role (PowerUsers), execute the following script:

EXEC [aspnet_UsersInRoles_RemoveUsersFromRoles] ‘WcfService1′,’Domain\ActiveDirectoryAccount’, ‘PowerUsers’

%d bloggers like this: