MSBI Mentalist

How to create Standard Subscriptions in SSRS

11 Jun 2013 Leave a Comment

Subscription is used to execute a report on scheduled time and deliver it to the End User as an E-mail attachment or to be dropped in a file share in any specific format (like pdf, doc, etc.,). If the report server is configured for SharePoint integrated mode, you can also send a report to a SharePoint library.

Simply we can say that subscriptions can be used to schedule and then automate the delivery of a report.

SSRS supports two types of subscriptions, Standard and Data Driven Subscription.

Standard subscriptions are created and managed by individual users. A standard subscription consists of static values that cannot be varied during subscription processing.

We cannot use the expression to specify the values for the parameter when creating subscriptions.

Creating Standard Subscription:

I have a report which gives last 24 hours Report Execution log details. Please go through below figure which shows the preview of the report.

Please go through the below script which uses ReportServer database to get last 24 hours Execution log details of Reports .

   1:  SELECT c.name,

   2:  CASE

   3:  WHEN el.RequestType = 0 THEN 'Interactive'

   4:  WHEN el.RequestType = 1 THEN 'Subscription'

   5:  WHEN el.RequestType = 2 THEN 'Refresh Cache'

   6:  END AS RequestType,

   7:  el.Format,

   8:  el.TimeStart,

   9:  el.TimeEnd,

  10:  DATEDIFF(ss,el.TimeStart,el.TimeEnd) AS 'TotalDuration(Sec)',

  11:  (el.TimeDataRetrieval/1000.00) AS 'Data Retrieval Time(Sec)',

  12:  (el.Timeprocessing/1000.00) AS 'Processing Time(Sec)',

  13:  (el.TimeRendering/1000.00) AS 'Rendering Time(Sec)',

  14:  CASE

  15:  WHEN el.Source=1 THEN 'LIVE'

  16:  WHEN el.Source=2 THEN 'Cache'

  17:  WHEN el.Source=3 THEN 'Snapshot'

  18:  WHEN el.Source=4 THEN 'History'

  19:  WHEN el.Source=5 THEN 'Ad hoc(Report Builder)'

  20:  WHEN el.Source=6 THEN 'Session'

  21:  WHEN el.Source=7 THEN

  22:              'Report Definition Customization Extension(RDCE)'

  23:  END AS 'Source',

  24:  el.status,

  25:  el.ByteCount/1024.00 AS 'Size(Kb)',

  26:  el.[RowCount] AS 'Number of Records'

  27:  FROM ReportServer..ExecutionLogStorage EL

  28:  INNER JOIN ReportServer..Catalog c ON c.itemid=el.reportid

  29:  WHERE TimeStart>= @Date

  30:  ORDER BY TimeStart DESC

I have @Date parameter in the above script and configured default value(which gives yesterday’s date as a value) as shown in the below figure.

The first thing while creating subscription is to add Credentials for the DataSource of the Report.

Open Report Manager and Click on the Data Source(ReportServerDB) of the

report(ReportsExecutionLog) to which we need to create subscription.

Check Credentials stored securely in the report server option and specify windows credentials. also select the checkbox use as Windows credentials when connecting to the data source. Click on Test Connection button to check the connection. Now click on Apply button.

Click on Drop down button of the Report to which we need to create subscription and select Subscribe option.

On the Subscription page, select the delivery option as E-Mail/Windows File Share.

Windows File Share Delivery

Select Windows File Share as delivery option and create a shared folder and specify in the path tab. Specify the credentials to access the file and click on Select Schedule to automate the Report Execution.

Specify the time and day of current week and select radio button to repeat the schedule.Click OK button.

Now we have scheduled the report, Click on check box Use Default option as we can’t pass value to the parameter of the Report. Click OK button.

Subscription is created and is scheduled to 03:03PM, check the status of the subscription in the below figure. It has generated pdf file of the report and placed in the mentioned shared folder path.

Please go through the below figure of shared path folder.

E-Mail option

Again click on the drop down button of the Report. Select Subscribe option.

Note: E-Mail option will be available only after setting mail configuration in the Reporting Services Configuration Manager. Please go through the below figure to know the process.

After setting the Sender Address and SMTP Server property values, click on Apply button to complete the mail configuration.

Select the Delivery by Option as Email. Specify the Render Format and click on Select Schedule button to automate the report execution.

Specify the schedule time and click on OK button.

Check Use Default option to pass default value as the parameter to the report. Click on OK button to complete the Subscription configuration.

Subscription is created and is scheduled at 4:10PM. Check the status of the subscription in the below figure. Mail was sent to given E-mail ID.

Now we have created Standard Subscriptions in two possible way. Once the Subscription is created for a Report, you’ll find a job under Jobs folder in the SQL Server Management Studio as shown in the below figure.

As we have created two subscription, we have seen two jobs in the above figure.

To know the details of subscription, please run the below script in ReportServer database.

   1:  SELECT R.ScheduleID AS JobName

   2:      ,C.NAME AS ReportName

   3:      ,C.[Path] AS ReportPath

   4:      ,S.SubscriptionID

   5:      ,SUBSTRING(S.DeliveryExtension

   6:               , 14

   7:               , len(S.DeliveryExtension))

   8:               AS DeleveryType

   9:      ,S.[Description] AS Subscription_Desc

  10:      ,S.LastStatus AS Subscription_Exec_Status

  11:      ,C.CreationDate

  12:      ,C.ModifiedDate

  13:      ,S.LastRunTime

  14:  FROM dbo.[Catalog] C

  15:  INNER JOIN dbo.Subscriptions S

  16:  ON S.Report_OID = C.ItemID

  17:  INNER JOIN dbo.ReportSchedule R

  18:  ON R.SubscriptionID = S.SubscriptionID

  19:  WHERE C.NAME = 'ReportsExecutionLog'

  20:  --Pass ReportName as parameter

Let me know your comments and queries.

I’ll meet you again with my post on Data Driven Subscription.

Track file attributes

06 Jun 2013 Leave a Comment

by Sunilreddy in Distinct Categories

Why you need to go for custom task or script task to get the source file attributes. If you are familiar with SQL Server T-SQL than you can get file details using OLE Automation Procedures.

Code to enable OLE Automation:

   1:  Sp_configure 'show advanced options', 1;

   2:  Go

   3:  Reconfigure;

   4:  Go

   5:  Sp_configure 'ole automation procedures', 1;

   6:  Go

   7:  Reconfigure;

   8:  Go

.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; }

Script to check the existence of the file, if the file exists than get the file attributes.

   1:  DECLARE

   2:  @Filename VARCHAR(100) = 'D:\DelmeSoon.txt'

   3:      --give your file name with path

   4:  ,@hr INT

   5:  ,@objFileSystem INT

   6:  ,@objFile INT

   7:  ,@ErrorObject INT

   8:  ,@ErrorMessage VARCHAR(255)

   9:  ,@Path VARCHAR(100)

  10:  ,@size INT

  11:  ,@ShortPath VARCHAR(100)

  12:  ,@Type VARCHAR(100)

  13:  ,@DateCreated DATETIME

  14:  ,@DateLastAccessed DATETIME

  15:  ,@DateLastModified DATETIME

  16:  ,@Attributes INT

17:

  18:  EXEC @hr = sp_OACreate 'Scripting.FileSystemObject'

  19:      ,@objFileSystem OUT

20:

  21:  IF @hr <> 0

  22:  BEGIN

  23:      SET @errorMessage = 'Error in creating the file system object.'

24:

  25:      RAISERROR (

  26:              @errorMessage

  27:              ,16

  28:              ,1

  29:              )

  30:  END

  31:  ELSE

  32:  BEGIN

  33:      EXEC @hr = sp_OAMethod @objFileSystem

  34:          ,'GetFile'

  35:          ,@objFile out

  36:          ,@Filename

37:

  38:      IF @hr <> 0

  39:      BEGIN

  40:          SET @errorMessage = 'File not found '

41:

  42:          SELECT @Filename AS [FileName]

  43:              ,@errorMessage AS [Status]

  44:      END

  45:      ELSE

  46:      BEGIN

  47:          -- to get the file path

  48:          EXEC sp_OAGetProperty @objFile

  49:              ,'Path'

  50:              ,@path OUT

51:

  52:          -- to get the file size

  53:          EXEC sp_OAGetProperty @objFile

  54:              ,'size'

  55:              ,@size OUT

56:

  57:          IF @hr = 0

  58:              EXEC @hr = sp_OAGetProperty @objFile

  59:                  ,'ShortPath'

  60:                  ,@ShortPath OUT

61:

  62:          IF @hr = 0

  63:              EXEC @hr = sp_OAGetProperty @objFile

  64:                  ,'Type'

  65:                  ,@Type OUT

66:

  67:          IF @hr = 0

  68:              EXEC @hr = sp_OAGetProperty @objFile

  69:                  ,'DateCreated'

  70:                  ,@DateCreated OUT

71:

  72:          IF @hr = 0

  73:              EXEC @hr = sp_OAGetProperty @objFile

  74:                  ,'DateLastAccessed'

  75:                  ,@DateLastAccessed OUT

76:

  77:          IF @hr = 0

  78:              EXEC @hr = sp_OAGetProperty @objFile

  79:                  ,'DateLastModified'

  80:                  ,@DateLastModified OUT

81:

  82:          IF @hr = 0

  83:              EXEC @hr = sp_OAGetProperty @objFile

  84:                  ,'Attributes'

  85:                  ,@Attributes OUT

86:

  87:          IF @size = 0

  88:          BEGIN

  89:              SET @errorMessage = 'Empty file  '

90:

  91:              SELECT @Filename AS [FileName]

  92:                  ,@errorMessage AS [Status]

  93:          END

  94:          ELSE

  95:          BEGIN

  96:              SET @size = (@size / 1024.0)

  97:              SET @errorMessage = 'File Exists  '

98:

  99:              SELECT @Filename AS [FileName]

 100:                  ,@errorMessage AS [Status]

 101:                  ,@size AS [FileSize_kb]

 102:                  ,@ShortPath AS 'ShortPath'

 103:                  ,@Type AS 'Type'

 104:                  ,@DateCreated AS 'DateCreated'

 105:                  ,@DateLastAccessed AS 'DateLastAccessed'

 106:                  ,@DateLastModified AS 'DateLastModified'

 107:                  ,CASE

 108:                      WHEN @Attributes = 0

 109:                          THEN 'Normal'

 110:                      WHEN @Attributes = 1

 111:                          THEN 'ReadOnly'

 112:                      WHEN @Attributes = 2

 113:                          THEN 'Hidden'

 114:                      WHEN @Attributes = 4

 115:                          THEN 'System'

 116:                      WHEN @Attributes = 8

 117:                          THEN 'Volume'

 118:                      WHEN @Attributes = 16

 119:                          THEN 'Directory'

 120:                      WHEN @Attributes = 32

 121:                          THEN 'Archive'

 122:                      WHEN @Attributes = 64

 123:                          THEN 'Alias'

 124:                      WHEN @Attributes = 128

 125:                          THEN 'Compressed'

 126:                      END AS 'Attributes'

 127:  /*

 128:  Attribute values table

 129:  ----------------------------

 130:  Normal        0    Normal file. No attributes are set.

 131:  ReadOnly    1    Read-only file. Attribute is read/write.

 132:  Hidden        2    Hidden file. Attribute is read/write.

 133:  System        4    System file. Attribute is read/write.

 134:  Volume        8    Disk drive volume label. Attribute is read-only.

 135:  Directory    16    Folder or directory. Attribute is read-only.

 136:  Archive        32    File has changed since last backup. Attribute

                             is read/write.

 137:  Alias        64    Link or shortcut. Attribute is read-only.

 138:  Compressed    128    Compressed file. Attribute is read-only.

 139:  */

 140:          END

 141:      END

 142:  END

 143:

 144:  EXEC sp_OADestroy @objFileSystem

 145:

 146:  EXEC sp_OADestroy @objFile

Let me know your comments and queries.

OLE Automation Procedures to check the Source File Existence

06 Jun 2013 1 Comment

by Sunilreddy in Scripts

To check existence of file in a given folder, we can use OLE automation procedures. Please go through the below SQL Script to know the process.

We need to enable ole automation procedures property of the SQL Server to use it.

There are two ways to enable the property, first process is using below code.

   1:  Sp_configure 'show advanced options', 1;
   2:  Go
   3:  Reconfigure;
   4:  Go
   5:  Sp_configure 'ole automation procedures', 1;
   6:  Go
   7:  Reconfigure;
   8:  Go

Second process is to select SQL Server Properties –> Click on Facets –> Select Surface Area Configuration as a facet –> set OLE automation procedures to True.

Please go through the below script code:

   1:  DECLARE 
   2:  @Filename VARCHAR(100) = 'D:\DelmeSoon.txt'  
   3:                 --give your filename with folderpath
   4:  ,@hr INT 
   5:  ,@objFileSystem INT
   6:  ,@objFile INT
   7:  ,@ErrorObject INT
   8:  ,@ErrorMessage VARCHAR(255)
   9:  ,@Path VARCHAR(100)
  10:  ,@size INT
  11:  
  12:  
  13:   
  14:  EXEC @hr = sp_OACreate 'Scripting.FileSystemObject'
  15:      ,@objFileSystem OUT
  16:   
  17:  IF @hr <> 0
  18:  BEGIN
  19:      SET @errorMessage = 'Error in creating the file system object.'
  20:   
  21:      RAISERROR (
  22:              @errorMessage
  23:              ,16
  24:              ,1
  25:              )
  26:  END
  27:  ELSE
  28:  BEGIN
  29:      EXEC @hr = sp_OAMethod @objFileSystem
  30:          ,'GetFile'
  31:          ,@objFile out
  32:          ,@Filename
  33:   
  34:      IF @hr <> 0
  35:      BEGIN
  36:          SET @errorMessage = 'File not found '
  37:   
  38:          SELECT @Filename AS [FileName]
  39:              ,@errorMessage AS [Status]
  40:      END
  41:      ELSE
  42:      BEGIN
  43:          -- to get the file path                        
  44:          EXEC sp_OAGetProperty @objFile
  45:              ,'Path'
  46:              ,@path OUT
  47:   
  48:          -- to get the file size                        
  49:          EXEC sp_OAGetProperty @objFile
  50:              ,'size'
  51:              ,@size OUT
  52:   
  53:          IF @size = 0
  54:          BEGIN
  55:              SET @errorMessage = 'Empty file  '
  56:   
  57:              SELECT @Filename AS [FileName]
  58:                  ,@errorMessage AS [Status]
  59:          END
  60:          ELSE
  61:          BEGIN
  62:              SET @size = (@size / 1024.0)
  63:              SET @errorMessage = 'File Exists  '
  64:   
  65:              SELECT @Filename AS [FileName]
  66:                  ,@errorMessage AS [Status]
  67:                  ,@size AS [FileSize_kb]
  68:          END
  69:      END
  70:  END
  71:   
  72:  EXEC sp_OADestroy @objFileSystem
  73:   
  74:  EXEC sp_OADestroy @objFile

Let me know your comments and Queries.

Execute Java Class file from SSIS – Part 3

04 Jun 2013 Leave a Comment

by Sunilreddy in MS BI Related

Recently we have discussed about two processes of executing Java Class file.

Execute Java Class file from SSIS

Execute Java Class file from SSIS – Part 2

Let’s discuss about another process to execute Java Class file from SSIS.

Process 3:

In this process we will use C# code to execute Java Class file
This process is as flexible as process 1
Knowledge of C# is pre-requisite Drag a Script Task component to the SSIS Control Flow Designer pane

Drag a Script Task component to SSIS Control Floe designer pane.

Create five SSIS variables as follows.

FilePath: Path of the file which need to be deleted.

In our example Filepath value is D:\\FileDeletion\\DellmeSoon\\DelmeSoon.txt

JavaClass: Path of the Java Class file. Value is D:\\FileDeletion\\DeleteFile.class
JavaDir: Path of the java.exe application.

Value is C:\\Program Files\\Java\\jdk1.7.0_17\\bin\\java.exe

WorkingDir: Folder path of the java.exe application.

We need to enable the EvaluateAsExpression property of WorkingDir variable and set the expression as shown in the below figure.

SUBSTRING( @[User::JavaDir] , 1, LEN( @[User::JavaDir] )-FINDSTRING(REVERSE(@[User::JavaDir]),"\\",1)+1)

ExecStatement: Contains Query syntax to execute Java Class file.

We need to enable the EvaluateAsExpression property of ExecStatement variable and set the expression as shown in the below figure.

"-classpath " + SUBSTRING( @[User::JavaClass] , 1, LEN( @[User::JavaClass] )-FINDSTRING(REVERSE(@[User::JavaClass]),"\\",1)+1) + " "+SUBSTRING (@[User::JavaClass], LEN( @[User::JavaClass] ) – FINDSTRING( REVERSE( @[User::JavaClass] ), "\\", 1) + 2, LEN (RIGHT( @[User::JavaClass], FINDSTRING( REVERSE( @[User::JavaClass] ), "\\", 1 ) – 1 ) ) – FINDSTRING( REVERSE( @[User::JavaClass] ), ".", 1 ) ) + " "+ @[User::FilePath]

Now double click on Script task and select ExecStatement, JavaDir and WorkingDir as Script task ReadOnlyVariables as shown in below figure.

click on Edit Script to write the script to execute Java Class file as shown in below figure.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;

namespace ST_18e16a8ea284440083cdac12d6812db0.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0"
            , Publisher = "", Description = "")]
    public partial class ScriptMain : 
    Microsoft.SqlServer.Dts.Tasks.ScriptTask
                      .VSTARTScriptObjectModelBase
    {
        private int ExecuteProcess(string cmd
                                    , string cmdParams
                                    , string workingDirectory
                                    , int timeout
                                    , out string stdOutput)
        {
            using (Process process = Process.Start(
                     new ProcessStartInfo(cmd, cmdParams)))
            {
                process.StartInfo.WorkingDirectory = workingDirectory;
                process.StartInfo.UseShellExecute = false;
                process.StartInfo.RedirectStandardOutput = true;
                process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
                process.Start();
                stdOutput = process.StandardOutput.ReadToEnd();
                return process.ExitCode;
            }
        }
        public void Main()
        {
          //  Dts.TaskResult = (int)ScriptResults.Success;
            string stdOutput;
            string varStatement = 
                        (string)Dts.Variables["ExecStatement"].Value;
            string varWorkingDir = 
                        (string) Dts.Variables["WorkingDir"].Value;
            string varJavaDir = 
                         (string)Dts.Variables["JavaDir"].Value;                
            int myTimeout = 5;
            int exitCode = new ScriptMain().ExecuteProcess(
                                             @varJavaDir
                                             ,varStatement
                                             ,@varWorkingDir
                                             ,myTimeout
                                             ,out stdOutput);
            Console.WriteLine(stdOutput);
        }
    }
}

Now build the script and close the editor. Click Ok on Script Task wizard.

Now press f5 to execute the Script task.

Let me know your comments and queries.

Execute Java Class File from SSIS

02 Jun 2013 4 Comments

by Sunilreddy in MS BI Related

Hi, recently I came across interesting requirement through my friend Goutham Aakiti. The requirement is like processing Java Class file using SSIS. I was very curious to know how to achieve this, spent some time and found that there are two ways to process Java Class File.

Let me show how we can do this.

Prcoess 1:

I have a Java Class File which deletes a file. Please go through the below Java code which accepts file path as argument to delete a file.

If you compile the above Java Code, the compiler will generate binary code file which is called as Class File. We will be using the Java Class file further to delete file.

Drag a Execute Process task component to the SSIS Control Flow designer pane

Create four variable to pass the property values dynamically.

Variable Descriptions

FilePath: Represents the path of the file which we are about to delete.

JavaClass: Represents the path of Java Class file which we use to delete a file.

JavaDir: Represents the path of Java.exe, which we use to execute Java Class file.

WorkingDir: Represents the Folder path of Java.exe.

Only variables like FilePath, JavaClass and JavaDir needs input. We need to set variable expression for WorkingDir variable.

Please go through the below figure to know about setting of variable expression for WorkingDir variable. Click on WorkingDir variable and select properties(press f4 ). Set the EvaluateExpression value to True. Set the Expression value as below.

SUBSTRING( @[User::JavaDir] , 1, LEN( @[User::JavaDir] )-FINDSTRING(REVERSE(@[User::JavaDir]),"\\",1)+1)

Please go through the below figure to know about the property values of Execute Process task.

Executable property needs the path value of Java.exe, we will be passing the value through JavaDir variable as an property expression.

Arguments property needs statement to execute the Java Class File.

WorkingDirectory property needs the folder path of Java.exe, we’ll be passing the value through WorkingDir variable.

Please go through the below figure to know about the setting of above properties values. Properties like Executable and WorkingDirectory will be getting values directly from variables like JavaDir and WorkingDir.

We need to set an expression for Arguments property, please go through the below figure to know about the setting of expression.

"-classpath " + SUBSTRING( @[User::JavaClass] , 1, LEN( @[User::JavaClass] )-FINDSTRING(REVERSE(@[User::JavaClass]),"\\",1)+1) + " "+SUBSTRING (@[User::JavaClass], LEN( @[User::JavaClass] ) – FINDSTRING( REVERSE( @[User::JavaClass] ), "\\", 1) + 2, LEN (RIGHT( @[User::JavaClass], FINDSTRING( REVERSE( @[User::JavaClass] ), "\\", 1 ) – 1 ) ) – FINDSTRING( REVERSE( @[User::JavaClass] ), ".", 1 ) ) + " "+ @[User::FilePath]

Lets see how it works.

I want to delete a file DelmeSoon.txt from path D:FileDeletion\DellmeSoon\

press f5 and see, the Java Class file will deletes the.

Let me know your comments and queries on my post.

MSBI Mentalist

Track file attributes

OLE Automation Procedures to check the Source File Existence

RSS

Subscribe Me..!!

Categories

Archives

Recent Comments

Top viewed Posts & Pages

Blogroll

Follow “MSBI Mentalist”