Changing SharePoint Execution Timeout

In SharePoint, the default time allowed to export reports to different formats (Excel, PDF, etc) is 2 mins -and to me that’s already a lot of time for a report to render-, but there are some unfortunate cases where you need to render large amounts of information and therefore, you need to ensure that the SSRS reports are actually able to export.

To do so, you need to make sure that the execution timeout is increased to not have the process fail.

Since SharePoint is a web platform, we need to include a parameter in the web.config for IIS to increase the timeout.

  1. Open IIS Manager on servers that have Reporting Services installed.
  2. Select the Site on Port 80.
  3. Right Click the site and select Explore.
  4. Edit the web.config file in Notepad.
  5. Search for the first occurrence of “httpRuntime” located under the system.web.

    ssrs_sharepoint_execution1
  6.  Add “executionTimeout=”1200”” to the httpRuntime entry located in the above image. (Value is in seconds, 1200 is 20 minutes)

    ssrs_sharepoint_execution2
  7.  Save the file and Close the file.
  8. Close the Windows explorer window.
  9. Restart IIS.
  10. Close IIS Manager.

Fix: PowerPivot for SharePoint Data Refresh

ImageError 

Fix:

  1. On the Analysis Services Server running in SharePoint mode, Add the Analysis Services service account to the “Act as part of the operating system” privilege:
    1. Run “secpol.msc”
    2. Click Local Security Policy, then click Local policies, and then click User rights assignment.
    3. Add the service account.
  2. Restart Excel Services and reboot the Analysis Services server.

Delegation from the Excel Services service account or from Claims to Windows token service (C2WTS) to the Analysis services instance is not required. Therefore no configuration for KCD from Excel Services or C2WTS to PowerPivot AS service is necessary. Note: If the backend data source is on the same server as the Analysis Services instance, delegation is not required.

Querying Active Directory from SQL Server

Sometimes source systems do not store Windows credentials as an attribute of a person, and when that happens security becomes an interesting challenge.

For this project, I needed to retrieve the EmployeeNumber attribute from Active Directory to be able to link the user back to the source system (very useful for Reporting, but same logic applies for any service from the SQL Server BI stack).

First we need to create a ‘Linked Server’ from a SQL Server instance with the following system stored prod:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
GO

After you just create a Data Source pointing to the instance where you added that Linked Server and start querying the attributes you need from the Active Directory domain you need:

SELECT 
     WinUsername
     ,EmployeeNumber 
FROM OPENQUERY(ADSI,
    'SELECT 
          sAMAccountName
          ,employeeNumber
     FROM 
          ''LDAP://ou=users,ou=YourDomain,dc=YourDomainControler''
     WHERE 
          objectCategory = ''Person'' 
          AND objectClass= ''user'' 
          AND sAMAccountName='''+ YourUsername + '''')

If you need to get groups:

SELECT 
     User
     ,GroupName
FROM OPENQUERY( ADSI, 
     'SELECT 
          Name
          ,distinguishedname
     FROM 
          ''LDAP://ou=YourDomain,dc=YourDomainControler'' 
     WHERE 
          objectCategory = ''GROUP'' 
          AND CN = ''* *''')

If you need to know the people that belong to a specific Group:

SELECT 
     samaccountname
     ,Name
FROM OPENQUERY(ADSI, 
     'SELECT 
          samaccountname
          ,Name
     FROM 
          ''LDAP://ou=YourDomain,dc=YourDomainControler'' 
     WHERE 
          objectCategory = ''user'' 
          AND memberof = ''CN=NameOfTheGroup,OU=Groups,OU=YourDomain,DC=YourDomainControler''')

Passing Multi-value Parameters to a MDX Query in SSRS

Let’s say our report needed ProductCategory as a parameter.  The ProductCategoryKey in the relational database for Bikes is 1, and our cube member for Bikes is [Product].[Category].&[1].  So we can transform our parameter value to pass into our mdx query like so:

“[Product].[Category].&[”+Cstr(Parameters!ProductCategory.Value) + “]”

Simple right?

BUT, I needed to be able to pass multiple values, which meant I had to take some extra steps before I could parse my string.  If ProductCategory is a multi-value parameter, then Parameters!ProductCategory.Value is an object – an array of values.  So we need to create a single string out of that array, format that string, and then break it up again back into an array since that is what the MDX query is expecting.  Whew!

Here’s the magic:

Split(“[Product].[Category].&["+Replace(Join(Parameters!ProductCategory.Value,"],”)+”]”,”,”,”,[Product].[Category].&["),",")

Cool eh? but, what’s happening here?

Join() is a handy little function that takes our array and joins the values into a string.  We can delimit the values with a string, in this case “],”, like so:

Join(Parameters!ProductCategory.Value, “],”)

Bikes, Components, and Clothing for our parameter values, our string after using the join function will be“1],2],3″.  We can then add an ending bracket to the end via simple string concatenation so now we have “1],2],3]”.

Applying the Replace() function, we replace “,” with “,[Product].[Category].&[“ and now we have “1],[Product].[Category].&[2],[Product].[Category].&[3]“.  Finish with one final string concatenation in the front and we have “[Product].[Category].&1],[Product].[Category].&[2],[Product].[Category].&[3]“

Unfortunately we can’t just pass this into our MDX query.  It’s expecting an array object, not a string!  But luckily, the Split() function is the counterpart to Join() and breaks up our string into that array object we want via the commas as a delimiter.

Importing Excel files with Aspose.Cells

On a recent project I had to deal with pretty interesting scenario:

  1. Import multiple Excel Files with different formats (.xls, .xlsx, xlsm)
  2. Server must not have any office component installed (leaving us with the only option of finding a third party component ‘compatible’ with SSIS)
  3. The solution must be capable of handling large Excel files, around 100 MB.

Most of the tools out there can only handle one format, or they break when the file is too big, but talking to a colleague from Imaginet, I was pointed to Aspose.Cells.

Aspose.Cells is a  .NET component that allows developers to manipulate and convert spreadsheet files from within their own applications.

At the end, I was able to dynamically load multiple (big and small) files  in multiple formats using a SSIS Script Task and I want to share my code with the community that is having the same or similar requirements.

Keep in  mind that Aspose.Cells is not free, but definitely worth it.

Enjoy and feel free to give me a shout if you have any question:

C# Code

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
//Include utility to handle Excel data extraction. Add DLL to the solution's references.
using Aspose.Cells;
using System.Windows.Forms;

/*
 * Script to extract Excel file data and bulk insert it in DS_Stage
*/
namespace ST_d5edcbc1092a4dec8c51e8c674cfd73e
{
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{

        //The Main function will run per file passed by the SQL Task only if the file name is valid
	public void Main()
	{
            //Instantiate the License class
            Aspose.Cells.License license = new Aspose.Cells.License();

            //Pass only the name of the license file embedded in the assembly
            license.SetLicense("Aspose.Cells.lic");

            try
            {
                //DataTable object to store the Excel Data
                DataTable excelTable;

                    //We are going to populate the DataTable from the result of the ExcelILITableExtraction function
                    excelTable = ExcelTableExtraction(Dts.Variables["Control::WorkspaceFilePath"].Value.ToString(), DS_CONTROL);

                //Dynamically reusing a Data Source previously created in the Connection Manager 
                string DS_STAGE = Dts.Connections["DS_Stage"].ConnectionString.ToString();
                SqlConnection cn_stage = new SqlConnection(DS_STAGE.Replace("Provider=SQLNCLI11.1;", ""));
                cn_stage.Open();

                using (cn_stage)
                {
                    //if the cn_stage connection is opened we initialize a SqlBulkCopy object pointing to the connection
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cn_stage))
                    {

                            //insert the data in the SCHEMA.TABLENAME table with a Batch size of 50,000 to handle insertions with lots of data
                            bulkCopy.DestinationTableName = "SCHEMA.TABLENAME";
                            bulkCopy.BatchSize = 50000;

                        try
                        {
                            // Write from the source to the destination.
                            bulkCopy.WriteToServer(excelTable);

                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        //Fuction that receives an Aspose object Row and a total of Columns and returns a boolean that specifies if the Row cointains only NULLS or if there is data in any of their columns
        private static bool IsNotBlankRow(Row rowItms, int totalColumns)
        {

            int counter = 0;
            bool isDataFound = false;

            //loops all the columns in a row while isDataFound is false and the counter is lower than the total of columns
            while (!isDataFound && (counter < totalColumns))
            {
                //If there is data in a cell, we change the boolean value to true and return the value
                if (rowItms[counter].Value != null)
                {
                    isDataFound = true;
                }
                counter++;
            }
            return isDataFound;
        }

        //Function that handles the Excel Extraction
        private static DataTable ExcelTableExtraction(string fileName, string DS_CONTROL)
        {
            //We create another DataTable object that will replicate the schema of the destination table
            DataTable table = CreateTable();
            string workSheetName = null;

            //We create a Workbook object and initialize it with the path to the file, this will load the file in memory
            Workbook workbook = new Workbook(fileName);

            //We loop every Worksheet in the Wookbook/Excel file
            foreach (Worksheet sheet in workbook.Worksheets)
            {
                workSheetName = sheet.Name; 
                Worksheet worksheet = GetWorkSheet(workSheetName, workbook);
		int maxRowsCount = worksheet.Cells.MaxRow + 1;

        int maxColumnsCount = worksheet.Cells.MaxColumn + 1;

                //We use the Aspose function ClearFormats to clear all the formats from cell 0,0 to maxRowsCount,maxColumnsCount. 
                //This step will clear any numerical format assigned to an specific column, leaving the raw number
                worksheet.Cells.ClearFormats(0, 0, maxRowsCount, maxColumnsCount);

                //We loop every row in the worksheet
                for (int rowIndex = 0; rowIndex < maxRowsCount; rowIndex++)
                {
		    //if the row is not blank, we extract the data, otherwise we skip it
                    if (IsNotBlankRow(worksheet.Cells.Rows[rowIndex], maxColumnsCount))

	            {
                        DataRow row = table.NewRow();
                        row["Column1"] = "Column1";
                        row["Column2"] = "Column2";
                        row["Column3"] = "Column3";
                        row["ColumnN"] = "ColumnN";

                        //we create an array of strings to add every cell in the worsheet to it
                        string[] letterMapper = new string[]
                    {
                        "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB"
                    };
                        //loop through every column in the letterMapper array
                        for (int columnIndex = 0; columnIndex < letterMapper.Count(); columnIndex++)
                        {
                            //we will just loop to the maximum column in the worksheet
                            if (columnIndex < maxColumnsCount)
                            {
                                //we will get the cell value if is different than null
                                if (worksheet.Cells.GetCell(rowIndex, columnIndex) != null)
                                {
                                    //or if is empty
                                    if (worksheet.Cells.GetCell(rowIndex, columnIndex).Value != null)
                                    {
                                        //we insert the value into the right column in the letterMapper array
                                        row[letterMapper[columnIndex]] = worksheet.Cells.GetCell(rowIndex, columnIndex).Value.ToString();
                                    }
                                }
                            }
                        }
                        //we add the letterMapper array to the table to complete mapping the database destination database design
                        table.Rows.Add(row);
                }
                }
            //Commit all the changes to the table object
            table.AcceptChanges();
            //return the table to perform the bulk insertion
            return table;
        }

        //Function created to loop and find an specific worksheet on a workbook
        private static Worksheet GetWorkSheet(string worksheetName, Workbook workbook)
        {
            bool isWorkSheetFound = false;
            int totalWorksheets = workbook.Worksheets.Count;
            int counter = 0;
            Worksheet workSheet = null;

            //loop through the worksheet until the worksheet name is the same as the input parameter
            while (!isWorkSheetFound && (counter < totalWorksheets))
            {
                if (workbook.Worksheets[counter].Name == worksheetName)
                {
                    //if the worksheet is found, change the flag and store the wanted worsheet on the Worksheet object
                    workSheet = workbook.Worksheets[counter];
                    isWorkSheetFound = true;

                }
                counter++;
            }
            //return the workSheet 
            return workSheet;
        }

        //This function will create a column with an specific type and add it to a dataTable
        private static void DataColumnCreation(string columnName, string columnType, DataTable excelTable)
        {
            DataColumn dataColumn = new DataColumn();
            dataColumn.DataType = System.Type.GetType(columnType);
            dataColumn.ColumnName = columnName;

            excelTable.Columns.Add(dataColumn);
        }

        //Function responsible to duplicate in memory the Destination table design
	 //By duplicating the design of the destination table, we are able to perform bulk insertions and improve the server's performance
        private static DataTable CreateTable()
        {
            DataTable Generic = new DataTable("Generic");

            DataColumnCreation("Column1", "System.String", Generic);
            DataColumnCreation("Column2", "System.String", Generic);
            DataColumnCreation("Column3", "System.String", Generic);
            DataColumnCreation("ColumnN", "System.String", Generic);

	    //Array of columns to be extracted from the Excel file
            string[] letterMapper = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB" };

            foreach (var letter in letterMapper)
            {
                DataColumnCreation(letter, "System.String", Generic);
            }

            return Generic;
        }

    }
}

MDS HttpWebRequest WebException RemoteServer issue

Here is a small description of the issue:

I have in my box Windows Server 2012 64-bit, SQL Server 2012 SP1 64-bit and SharePoint 2013.

After installing and configuring MDS on my server, I can go to the web app’s home page but if I click on the Explorer (or pretty much anything running SilverLight), I get the following error:

MDS_HttpWebRequest_WebException_RemoteServer

 

Then I downloaded and install the Excel Add-in for Excel 2013, but when I try to connect to the server, I’m getting this error:

MDS_Add_In_Excel

 

After hours of more research, and lots of patience I finally was able to solve a very weird issue with Master Data Services.

It turns out that that for some odd reason, the applicationHost.config file located in the windows/system32/inetsrv/config folder  was missing a XML tag for the MDS ‘service’.

1) Make sure you have all the MDS Web Application requirements installed on your server: http://msdn.microsoft.com/en-us/library/ee633744.aspx

2) Make sure you created your site with the MDS Configuration Manager and that you in fact can browse the Home Page.

3) You need to have Admin permissions and edit the windows/system32/inetsrv/config/applicationHost.config file to make sure it contains the following tags:

<location path="MDS">
 <system.webServer>
  <security>
   <authentication>
    <digestAuthentication enabled="false" />
    <basicAuthentication enabled="false" />
    <anonymousAuthentication enabled="false" />
    <windowsAuthentication enabled="true" />
   </authentication>
  </security>
 </system.webServer>
</location>

<location path="MDS/Service">
 <system.webServer>
  <security>
   <authentication>
    <basicAuthentication enabled="false" />
    <anonymousAuthentication enabled="true" />
    <windowsAuthentication enabled="true" />
   </authentication>
  </security>
 </system.webServer>
</location>

If I have to guess what exactly caused the issue, I’ll say that the ‘Service’ tag gets created the first time you create a site, so if you for any reason end up deleting or creating another site, IIS is actually not updating this file. But, that’s just my guess.