SaaS, PaaS, IaaS

SOFTWARE AS A SERVICE

In some ways, SaaS is very similar to the old thin-client model of software provision, where clients, in this case usually web browsers, provide the point of access to software running on servers. SaaS is the most familiar form of cloud service for consumers. SaaS moves the task of managing software and its deployment to third-party services. Among the most familiar SaaS applications for business are customer relationship management applications like Salesforce, productivity software suites like Google Apps, and storage solutions brothers like Box and Dropbox.

Use of SaaS applications tends to reduce the cost of software ownership by removing the need for technical staff to manage install, manage, and upgrade software, as well as reduce the cost of licensing software. SaaS applications are usually provided on a subscription model.

PLATFORM AS A SERVICE

PaaS functions at a lower level than SaaS, typically providing a platform on which software can be developed and deployed. PaaS providers abstract much of the work of dealing with servers and give clients an environment in which the operating system and server software, as well as the underlying server hardware and network infrastructure are taken care of, leaving users free to focus on the business side of scalability, and the application development of their product or service.

As with most cloud services, PaaS is built on top of virtualization technology. Businesses can requisition resources as they need them, scaling as demand grows, rather than investing in hardware with redundant resources.

Examples of PaaS providers include Heroku, Google App Engine, and Red Hat’s OpenShift.

INFRASTRUCTURE AS A SERVICE

Moving down the stack, we get to the fundamental building blocks for cloud services. IaaS is comprised of highly automated and scalable compute resources, complemented by cloud storage and network capability which can be self-provisioned, metered, and available on-demand.

IaaS providers offer these cloud servers and their associated resources via dashboard and/or API. IaaS clients have direct access to their servers and storage, just as they would with traditional servers but gain access to a much higher order of scalability. Users of IaaS can outsource and build a “virtual data center” in the cloud and have access to many of the same technologies and resource capabilities of a traditional data center without having to invest in capacity planning or the physical maintenance and management of it.

IaaS is the most flexible cloud computing model and allows for automated deployment of servers, processing power, storage, and networking. IaaS clients have true control over their infrastructure than users of PaaS or SaaS services. The main uses of IaaS include the actual development and deployment of PaaS, SaaS, and web-scale applications.

Report Metadata

/******************************************************************************** * PROCEDURE NAME: dbo.usp_ReportGlossary * PROCEDURE DESC: Provides details around reports including connections, users, data sources and report glossary details @QueryPart provides particular section of detail -- 1 = Created by -- 2 = Parameters -- 3 = Data Sources, Sets, Commands and Fields -- 4 = Subscriptions -- 5 = Snapshot -- 6 = Authorised Users -- 7 = Execution Log -- 8 = Report Glossary Details *********************************************************************************/ CREATE PROCEDURE [dbo].[usp_ReportGlossary] ( @ReportName VARCHAR(850), @QueryPart INT ) AS BEGIN SET NOCOUNT ON DECLARE @Namespace VARCHAR(500) DECLARE @SQL VARCHAR(MAX) DECLARE @ReportID VARCHAR(50) = (SELECT C.ItemID FROM [ReportServer].[dbo].[Catalog] C WHERE C.Content is not null AND C.Type = 2 AND C.Name = @ReportName) SELECT @Namespace= SUBSTRING( x.CatContent ,x.CIndex ,CHARINDEX('"',x.CatContent,x.CIndex+7) - x.CIndex ) FROM ( SELECT CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))) ,CIndex = CHARINDEX('xmlns="',CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))) FROM Reportserver.dbo.Catalog C WHERE C.Content is not null AND C.Type = 2 ) X SELECT @Namespace = REPLACE(@Namespace,'xmlns="','') + '' --------------------------------- -- Get report creation details --------------------------------- IF @QueryPart = 1 SELECT Name,CreatedBy = U.UserName ,CreationDate = C.CreationDate ,ModifiedBy = UM.UserName ,ModifiedDate FROM Reportserver.dbo.Catalog C JOIN Reportserver.dbo.Users U ON C.CreatedByID = U.UserID JOIN Reportserver.dbo.Users UM ON c.ModifiedByID = UM.UserID WHERE Name = @ReportName --------------------------------- -- Get parameters of the report --------------------------------- IF @QueryPart = 2 SELECT Name = Paravalue.value('Name[1]', 'VARCHAR(250)') ,Type = Paravalue.value('Type[1]', 'VARCHAR(250)') ,Nullable = Paravalue.value('Nullable[1]', 'VARCHAR(250)') ,AllowBlank = Paravalue.value('AllowBlank[1]', 'VARCHAR(250)') ,MultiValue = Paravalue.value('MultiValue[1]', 'VARCHAR(250)') ,UsedInQuery = Paravalue.value('UsedInQuery[1]', 'VARCHAR(250)') ,Prompt = Paravalue.value('Prompt[1]', 'VARCHAR(250)') ,DynamicPrompt = Paravalue.value('DynamicPrompt[1]', 'VARCHAR(250)') ,PromptUser = Paravalue.value('PromptUser[1]', 'VARCHAR(250)') ,State = Paravalue.value('State[1]', 'VARCHAR(250)') FROM ( SELECT C.Name,CONVERT(XML,C.Parameter) AS ParameterXML FROM ReportServer.dbo.Catalog C WHERE C.Content is not null AND C.Type = 2 AND C.Name = @ReportName ) a CROSS APPLY ParameterXML.nodes('//Parameters/Parameter') p ( Paravalue ) ---------------------------------------------------------------------------------------------------------------------------------------------------------- -- Get Data Sets , Command , Data fields Associated with the report ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @QueryPart = 3 SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace + ''', ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd ) SELECT DISTINCT ReportName = name ,DataSetName = x.value(''(@Name)[1]'', ''VARCHAR(250)'') ,DataSourceName = x.value(''(Query/DataSourceName)[1]'',''VARCHAR(250)'') ,CommandText = x.value(''(Query/CommandText)[1]'',''VARCHAR(MAX)'') FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML FROM ReportServer.dbo.Catalog C WHERE C.Content is not null AND C.Type = 2 AND C.Name = ''' + @ReportName + ''' ) a CROSS APPLY reportXML.nodes(''/Report/DataSets/DataSet'') r ( x ) CROSS APPLY x.nodes(''Fields/Field'') f(df) ORDER BY name ' EXEC(@SQL) ---------------------------------------------------------------------------------------------------------------------------------------------------------- -- Get subscription Associated with the report ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @QueryPart = 4 SELECT Reportname = c.Name ,SubscriptionDesc=su.Description ,Subscriptiontype=su.EventType ,su.LastStatus ,su.LastRunTime ,Schedulename=sch.Name ,ScheduleType = sch.EventType ,ScheduleFrequency = CASE sch.RecurrenceType WHEN 1 THEN 'Once' WHEN 2 THEN 'Hourly' WHEN 4 THEN 'Daily/Weekly' WHEN 5 THEN 'Monthly' END ,su.Parameters FROM Reportserver.dbo.Subscriptions su JOIN Reportserver.dbo.Catalog c ON su.Report_OID = c.ItemID JOIN Reportserver.dbo.ReportSchedule rsc ON rsc.ReportID = c.ItemID AND rsc.SubscriptionID = su.SubscriptionID JOIN Reportserver.dbo.Schedule Sch ON rsc.ScheduleID = sch.ScheduleID WHERE c.Name = @ReportName ---------------------------------------------------------------------------------------------------------------------------------------------------------- -- Get Snapshot associated with the report ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @QueryPart = 5 SELECT C.Name ,H.SnapshotDate ,S.Description ,ScheduleForSnapshot = ISNULL(Sc.Name,'No Schedule available for Snapshot') ,ScheduleType = sc.EventType ,ScheduleFrequency = CASE sc.RecurrenceType WHEN 1 THEN 'Once' WHEN 2 THEN 'Hourly' WHEN 4 THEN 'Daily/Weekly' WHEN 5 THEN 'Monthly' END ,sc.LastRunTime ,sc.LastRunStatus ,ScheduleNextRuntime = SC.NextRunTime ,S.EffectiveParams ,S.QueryParams FROM Reportserver.dbo.History H JOIN Reportserver.dbo.SnapshotData S ON H.SnapshotDataID = S.SnapshotDataID JOIN Reportserver.dbo.Catalog c ON C.ItemID = H.ReportID LEFT JOIN Reportserver.dbo.ReportSchedule Rs ON RS.ReportID = H.ReportID AND RS.ReportAction = 2 LEFT JOIN Reportserver.dbo.Schedule Sc ON Sc.ScheduleID = rs.ScheduleID WHERE C.Name = @ReportName ---------------------------------------------------------------------------------------------------------------------------------------------------------- -- Get Users List having access to reports and tasks they can perform on the report ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @QueryPart = 6 SELECT C.Name ,U.UserName ,R.RoleName ,R.Description ,U.AuthType FROM Reportserver.dbo.Users U JOIN Reportserver.dbo.PolicyUserRole PUR ON U.UserID = PUR.UserID JOIN Reportserver.dbo.Policies P ON P.PolicyID = PUR.PolicyID JOIN Reportserver.dbo.Roles R ON R.RoleID = PUR.RoleID JOIN Reportserver.dbo.Catalog c ON C.PolicyID = P.PolicyID WHERE c.Name = @ReportName ORDER BY U.UserName ---------------------------------------------------------------------------------------------------------------------------------------------------------- -- Execution Log fo the report ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @QueryPart = 7 SELECT C.Name ,Case E.Requesttype WHEN 1 THEN 'Subscription' WHEN 0 THEN 'Report Launch' ELSE '' END ,E.TimeStart ,E.TimeProcessing ,E.TimeRendering ,E.TimeEnd ,E.Status ,E.InstanceName ,E.UserName FROM Reportserver.dbo.ExecutionLog E JOIN Reportserver.dbo.Catalog C ON E.ReportID = C.ItemID WHERE C.Name = @ReportName ORDER BY E.TimeStart DESC ---------------------------------------------------------------------------------------------------------------------------------------------------------- -- Report Glossary ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @QueryPart = 8 SELECT rg.ReportGlossaryID,rg.ReportID, rg.TypeID, rgt.Description , rg.context, rg.CreatedOn, rg.ModifiedOn FROM [DW_CTL].[dbo].[ReportGlossary] rg LEFT JOIN [DW_CTL].[dbo].[ReportGlossaryType] rgt ON rgt.TypeID = rg.TypeID WHERE ReportID IN(@ReportID) END

Read the List of SSIS Packages from the directory using Powershell

$packages = Get-ChildItem "C:\DEV\SSISProject\" -Filter *.dtsx

foreach ($package in $packages)
{
    Write-Host $package.Name
}

NOTE: Change the filter to read the document type of your choice.

Programatically invoking multiple instances of SSIS package to execute them in parallel

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.IO;
using System.Data.OleDb;
using System.Threading;
using System.Collections.Generic;

 public void Main()
        {
           
            var totalInstances = Convert.ToInt32(Dts.Variables["User::ParallelExecutionCount"].Value);
            var RowIteratorPkg = Dts.Variables["User::RowIteratorFilePath"].Value.ToString();

            try
            {
                if (File.Exists(RowIteratorPkg))
                {
                    var taskList = new System.Threading.Tasks.Task[totalInstances];

                    for (var i = 0; i < totalInstances; i++)
                    {
                        int runningInstance = i;
                        taskList[i] = System.Threading.Tasks.Task.Factory.StartNew(() => TriggerNewPackageInstance(RowIteratorPkg, runningInstance, totalInstances));
                    }

                    System.Threading.Tasks.Task.WaitAll(taskList);
                Dts.TaskResult = (int)ScriptResults.Success;
            }
                else
                {
                    logException(999, "RowIterator package does not exist in the configured location: " + RowIteratorPkg);
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
            }
            catch (Exception ex)
            {
                logException(999, "Exception in executing the RowIterator package: " + ex.Message);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        private void TriggerNewPackageInstance(string RowIteratorPkg, Int32 runningInstance, Int32 totalInstances)
        {
            try
            {
            var App = new Application();
            var pkg = new Package();
            pkg = App.LoadPackage(RowIteratorPkg, null);
                pkg.Variables["PackageInstanceNumber"].Value = (Int32)runningInstance;
                pkg.Variables["ParallelInstances"].Value = (Int32)totalInstances;
                pkg.Variables["DBConString"].Value = DbConnString;
            pkg.Execute();
        }
            catch (Exception ex)
            {
                logException(999, "Exception in executing the package: " + ex.Message + " Running Instance: " + runningInstance.ToString() + "totalInstance : " + totalInstances.ToString());
            }
        }

Installing / Uninstalling a Windows Service

Steps to install a Windows Service:

1. Open the Windows Service Solution in Visual Studio.
2. Make sure the target platform was set properly according to the machine you are trying to install the service.

3. Build the Solution in Debug / Release mode.
4. Copy the files generated in the Debug / Release folder (based on the mode selected in the above step) to some new folder.
5. Open the command prompt as an administrator.
6. Type the below command:
C:\Windows\Microsoft.NET\Framework\v4.0.30319>InstallUtil.exe <Full File path of the Executable generated for your service (new folder path created in step 4)>

(Ex: C:\Pardha\NewService\SchedulerService\SampleService.exe)

7. Press Enter.
8. The below message appears at the end if the installation is successfully completed.

• "The Commit phase completed successfully."
• "The transacted install has completed."

9. Check your service in the services list. (services.msc)
10. Set the user context under which the service has to run & then Start it Manually.

To Uninstall:

C:\Windows\Microsoft.NET\Framework\v4.0.30319>InstallUtil.exe /u <Full File path of the Executable generated for your service (new folder path created in step 4)>

SSIS - How to allow the foreach loop continue its execution even if any of the task inside it fails

1. Click on Event Handlers.
2. Click on the Grid icon under variables tab.
3. Check the Show System Variables option.
4. Under the variable list search for the system variable called Propagate.
5. Change its value to False.

Possible issues for a script task to get stuck in In-progress status

Possible Issues:

1. For Ex: EmpID is declared as an Integer variable in the SSIS Package.

While writing value to this variable with in Script Task, do make sure to explicitly type case the input like below

Dts.Variables["User::EmpID"].Value =  (int) 0;

more content .. in soon