SQLUNINTERRUPTED

I am just a medium, SQL Server the Goal

Tag Archives: .Net

Zipping and Unzipping Files – The Fast and Furious Way

Leave a comment Posted by on July 7, 2015

Recently while working on a customer project, we were required to zip and unzip hundreds of files of varying size. The objective was to import IIS Logs from over 100+ servers to Microsoft Analytics platform system for further analytics. The SLA’s we were working on was 1 hour, i.e. APS should have data available with a maximum latency of 1 hour. The processing required us to copy Logs from these IIS servers to a central NAS and from there to the APS landing zone. The hop to the central NAS was required because of some regulatory requirements at the client.

The IIS logs which were getting created every 1 hour, were anywhere between 1 MB – 50 MB, depending on the activity on the servers. Coping the raw files was out of question for obvious reasons. So we had to zip the files (thankfully IIS logs give tremendous compression) and copy them to the central NAS. From there copy and unzip the files on the Landing Zone

We tired multiple options like, calling exe’s (rar, unrar, 7-zip) from PowerShell, but that did not scale well. Eventually we settled on the .NET Compression routine (.NET 4 and above) to this.  We saw overwhelming results when using .NET routines (~13-15 times better execution speed) during compression and decompression.

For the purpose of this blog, I created a test folder, and filled it with 204 different types of files (images from our last trip, some PowerPoint presentations etc.) of varying size (max size 7 MB, min size 5 KB), for a total size of 320 MB.

Compression Results using rar/7-zip

$files = Get-ChildItem -Recurse "C:\Intel\BlogTests\TestFolder"
$starttime = Get-Date
foreach ($file in $files)
{
    $ZipUtility = 'C:\Program Files\WinRAR\rar.exe'
    $fileToCompress = $file.FullName
    $outputFile =  "C:\Intel\BlogTests\ZipFiles\" +$file.Name.Replace(".","_") + ".rar" 
    $zipCommandparam = " a " + $outputFile + " " + $fileToCompress
    $ps = Start-Process -FilePath $ZipUtility -ArgumentList $zipCommandparam -NoNewWindow -PassThru -Wait
    $ps.WaitForExit() # block till exe finish
}
$endTime = Get-Date
Write-Host "Time to Complete Using RAR -: "$endTime.Subtract($starttime).ToString()

Results With Rar.exe  —> Total Duration – 00:03:28 (3 minutes, 28 Seconds)image
Results With 7-zip.exe —> Total Duration – 00:03:48 (3 minutes 48 Seconds)

Compression Results using .Net Compression Routine


[System.Reflection.Assembly]::LoadWithPartialName('System.IO.Compression.FileSystem')
$files = Get-ChildItem -Recurse "C:\Intel\BlogTests\TestFolder"
$starttime = Get-Date
foreach ($file in $files)
{
$fileToCompress = $file.FullName
$directorypath = "C:\Intel\BlogTests\ZipFiles\" + $file.Name.Substring(0,$file.Name.LastIndexOf("."))
$outputzipfile =  "C:\Intel\BlogTests\ZipFiles\" + $file.Name.Replace(".","_") + ".zip"

#Write-Host $directory
$directory = [IO.Directory]::CreateDirectory($directorypath)
$filetocopy = $directorypath + "\" + $file.Name
[IO.File]::Copy($file.FullName,$filetocopy)
[System.IO.Compression.ZipFile]::CreateFromDirectory($directorypath,$outputzipfile)
[IO.Directory]::Delete($directorypath,$true)
}
$endTime = Get-Date
Write-Host "Time to Complete Using .Net Routine -: "$endTime.Subtract($starttime).ToString()

Results With .Net Routine —> Total Duration – 00:00:17 (17 Seconds)
image_thumb.png

We achieved similar results while decompressing the files.

Advertisement
.Net , ,

Uploading multiple Reports to the Report Server using .Net

Leave a comment Posted by on March 8, 2012

Continuing the series on Programming for Reporting Services, we will see how to upload multiple reports (*.rdl) files present in a folder, to the Report Server. While this can be done using Report Manager (uploading the reports one-by-one), this obviously is not an efficient way of doing it. In this post, we will see how we can automate the task using .Net.

The code, here assumes that all the *.rdl present in a file system folder would be uploaded to the same Report Server folder. if you require to add reports to multiple folder, then I suggest you create a folder hierarchy, to replicate your Report Server folder hierarchy.

For the purpose of this post, I have created the below mentioned folder hierarchy. Each folder contains some report, which needs to be uploaded to the report server. The reports would be uploaded to a similar folder hierarchy on the Report Server. If the folders do not exists on the Report Server, it would be first created and then the report uploaded.

image

Each of this folders contain some RDL file, which needs to be uploaded to the Report Server. I have created a Console Application for this purpose.

The First step would be create a method to iterate through the folder structure and read the RDL files.  Then we would need a method read the RDL files and get the report definition. This can be done using one of the IO stream classes in .Net. In my console Application, I have added a C# class named ReadReportDefinition.cs. Code included below.

   1: public static class ReadReportDefinition

   2:     {

   3:         public static void IterateFolderHierarchy(string folderpath)

   4:         {

   5:             ConfigureReports cr = new ConfigureReports();

   6:             string connectionString = "data source=." + ";Initial Catalog=PowerSaverLogs";

   7:             cr.UploadDataSource("PowerManagementDataSource", "SQL", connectionString);

   8:             try

   9:             {

  10:                 DirectoryInfo dir = new DirectoryInfo(folderpath);

  11:                 try

  12:                     { 

  13:                         DirectoryInfo[] subdirectories = 

  14:                                     dir.GetDirectories("*", SearchOption.TopDirectoryOnly);

  15:                         byte[] ReportDefinition = null;

  16:                         foreach (DirectoryInfo Dir in subdirectories)

  17:                         {

  18:                             cr.CheckReportServerFolder(Dir.Name);

  19:                             FileInfo[] ReportFiles = Dir.GetFiles("*.rdl");

  20:                             foreach (FileInfo file in ReportFiles)

  21:                             {

  22:                                 FileStream stream = File.OpenRead(file.FullName);

  23:                                 ReportDefinition = new Byte[stream.Length];

  24:                                 stream.Read(ReportDefinition, 0, (int)stream.Length);

  25:                                 stream.Close();

  26:                                 cr.UploadReports(Dir.Name,ReportDefinition, file.Name.Substring(0, (file.Name.Length - 4)));

  27:                                 ReportDefinition = null;

  28:                             }

  29:  

  30:                         }

  31:                 }

  32:                 catch(System.IO.IOException E)

  33:                     {

  34:                         Console.WriteLine("System Exception Message" + E.Message);

  35:                     }

  36:                 

  37:             }

  38:             catch(System.IO.DirectoryNotFoundException e)

  39:             {

  40:                 Console.WriteLine("System Exception Message" + e.Message);

  41:             }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The next step, would be to add a reference to one of the Reporting Services endpoints, and then dynamically binding it to the Report Server, where the reports needs to be uploaded. For information about, how to add a reporting service endpoint and dynamically bind it to a Report Server, click here.

The next step, is to create a class or methods to call the ReportingService2010 endpoint (this is what I am using in my code) and perform the required actions. In this case, I created a new Class “Configure Reports” to perform the desired operations.

This class has 3 methods, and the constructor. In the constructor, I dynamically bind the endpoint to my Report Server instance.

  • CheckReportServerFolder: Checks if a particular folder exists on the report server on not. This uses the ReportingService2010 Listchildren method.
  • UploadReports : Uploads reports to the Report Server. This function calls the ReportingService2010 CreateReport method.
  • UploadDataSource: To upload the datasource to the report server. Uses the ReportingService2010 CreateDataSource function. 
   1: using System;
   3: using System.Linq;

   2: using System.Collections.Generic;

   4: using System.Text;

   5: using System.Web.Services;

   6:  

   7: namespace UploadMultipleReports

   8: {

   9:     public class ConfigureReports

  10:     {

  11:         private const string Root = "/";

  12:         private ReportingService2010 rs;

  13:  

  14:         public ConfigureReports()

  15:         {

  16:             try

  17:             {

  18:                 // Connect to Reporting Services

  19:                 rs = new ReportingService2010();

  20:                 rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

  21:                 rs.Url = "http://localhost/reportserver/ReportService2010.asmx";

  22:             }

  23:             catch (System.Exception ex)

  24:             {

  25:                 Console.WriteLine(ex.Message);

  26:             }

  27:  

  28:         }

  29:         public void CheckReportServerFolder(string foldername)

  30:         {

  31:             int count = 0;

  32:             int datasourcecount =0;

  33:             try

  34:             {

  35:                 CatalogItem[] list = rs.ListChildren("/", false);

  36:                 foreach (CatalogItem c in list)

  37:                 {

  38:                     if ((c.TypeName == "Folder") & (c.Name == foldername))

  39:                         count += 1;

  40:                     if((c.TypeName=="Data Source")& (c.Name == "PowerManagementDataSource"))

  41:                         datasourcecount+=1;

  42:                 }

  43:                 if (count == 0)

  44:                     rs.CreateFolder(foldername, "/", null);

  45:             }

  46:             catch (Exception e)

  47:             {

  48:                 Console.WriteLine(e.Message);

  49:             }

  50:         }

  51:         public void UploadReports(string ParentFolder,byte[] definition,string ReportName)

  52:         {

  53:             try

  54:             {

  55:                 string parent = "/" + ParentFolder;

  56:                 /// Create the Reports

  57:                 ///

  58:                 Warning[] warnings = null;

  59:                 rs.CreateCatalogItem("Report", ReportName, parent, true, definition, null, out warnings);

  60:                 if (warnings != null)

  61:                 {

  62:                     Console.WriteLine("Report {0} could not be uploaded to folder {1}", ReportName, ParentFolder);

  63:                     foreach(Warning war in warnings)                    

  64:                         Console.WriteLine("Warning Generated {0}", war.Message);

  65:                 }

  66:             }

  67:             catch (Exception e)

  68:             {

  69:                 Console.WriteLine(e.Message);

  70:             }

  71:  

  72:         }

  73:         public void UploadDataSource(string name, string extension, string connstring)

  74:         {

  75:             const string Parentpath = "/";

  76:             DataSourceDefinition definition = new DataSourceDefinition();

  77:             definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated;

  78:             definition.ConnectString = connstring;

  79:             definition.Extension = extension;

  80:             definition.Enabled = true;

  81:             definition.EnabledSpecified = true;

  82:             definition.ImpersonateUserSpecified = true;

  83:             // Use the default prompt string.

  84:             definition.Prompt = null;

  85:             definition.WindowsCredentials = false;

  86:             try

  87:             {

  88:                 rs.CreateDataSource(name, Parentpath, false, definition, null);

  89:             }

  90:             catch (Exception e)

  91:             {

  92:                 Console.WriteLine(e.Message);

  93:             }

  94:         }

  95:     }

  96: }

Once the classes are in place, all we need to do is call them in the application main function.

   1: class Program

   2: {

   3:     static void Main(string[] args)

   4:     {

   5:         Console.WriteLine("Enter Report File Root Directory");

   6:         string folderpath = Console.ReadLine();

   7:         ReadReportDefinition.IterateFolderHierarchy(folderpath);

   8:         Console.ReadLine();

   9:     }

  10: }

Reporting Services , , , ,

Dynamically binding a Web Service reference to a Report Server Instance

3 Comments Posted by on March 4, 2012

One of the most important aspect of SQL Server Reporting Services, which makes it stand out amongst competition is its extensibility. If you know .Net programming, then you can almost anything with Reporting services. In this series of posts I would be talking about some things which can be done with .Net and Reporting Services.

Typically speaking, .Net with Reporting Services can be used to create any of the following.

  • .Net Applications (console, UI bases) to automate SSRS tasks.
  • Reporting Services extensions (data, security, delivery etc.) to be used with Reporting Services.
  • Reference libraries to be used directly with the Reports in Reporting Services.
  • .Net code to (web service, Forms application) to consume Reporting Services Reports.

In this post, I would be talking about how to dynamically bind a report server instance using a web service reference.

Reporting Services Web service provides a SOAP over HTTP interface for communication with the Report Server. It provides two endpoints, one for execution and another for management. The most important thing, when creating an application for SSRS is to the add the Reporting services proxy class to the project to the class. This can be done using one of the two ways.

Once you have added the web service reference, using any of the two methods described above, the most important thing is to dynamically bind the web service to a Report Server instance.

To dynamically bind the web service to any Report Server of your choice.

  • First create an instance of the SSRS web service, which was added to the project. For example, if you added the ReportService2010 endpoint, create an object for the same.
  • Set the URL property of this object to the desired Report Server web service URL.
  • Define appropriate security credentials.
ReportingService2010 rs = new ReportingService2010();

rs.Url = "http://localhost/reportserver/ReportService2010.asmx";

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

To make sure that the URL property is recognized by the compiler, you would need to add the following reference to your code.

using System.Web.Services;

At this point, we can call any method defined in the Web Service and perform the desired operation.

In the next post, I will talk about, creating an Application, which can upload, all the .RDL files present in a folder to a report server.

Reporting Services , , , , ,