PPS Reports: Integrating Reporting Services Reports with PerformancePoint 2010 Dashboards

The success of Business Intelligence solution can be measured with Visualization of data, hence Visualization in proper manner is important. It is handled through many ways, providing easier access for information to business users. One of the key components in Microsoft Business Intelligence suite is PerformancePoint 2010 which supports visualizing data in number of ways. PPS allows business user to interact with data, organize as he wants and change the content as he wants with its reporting features. Some of reporting facilities available with PPS are Reporting Services reports, Analytic charts, grids, and Decomposition trees. This post focuses on Reporting Services reports, basically, how a Reporting Services report can be added to PPS dashboard.

Microsoft Report Viewer 2008
In order to use Reporting Services report with PPS Dashboard Designer, you need to make sure that Microsoft Report Viewer is installed in your machine. If not, you may face errors like “Unable to connect with Report Server” or “Could not load file or assembly ‘Microsoft.ReportViewer.WinForms’…..”. It is available for 2005, 2008 and 2010 now. It looks like PPS Dashboard Designer works only with 2008 (I have 2008 SP1) but you can try 2010 too. Here are links for downloading it:
Microsoft Report Viewer Distributable 2008
Microsoft Report Viewer Distributable 2008 SP1
Microsoft Report Viewer Distributable 2010

Getting Started
As usual, you have to start with PPS Dashboard Designer. If you have configured Business Intelligence Web Application and site properly with SharePoint 2010, there will a link available for downloading this ClickOnce application (see Configuring PerformancePoint 2010 for more details). Click on Run Dashboard Designer for downloading the designer.

Once open, Save it with a meaningful name. Then select PerformancePoint Content folder in left-pane and go to Create tab in the ribbon. Click on Reporting Services.

Report Setting
You need to set few settings in order to have a Reporting Services report in your dashboard.

Server Mode: This allows you to set the mode of Reporting Server you are going to connect with. If it is hosted in SharePoint, select SharePoint Integrated, else select Report Centre. In my case, it is SharePoint Integrated mode.

Report Server URL: This is the URL of Report Server Web Service which you set of Reporting Services Configuration Manager.

Report URL: This is the exact URL of the report. If it is hosted in SharePoint, make sure that you have given the proper URL (including the extension). If you select Report Centre, you will be able to browse the report and set.

Show toolbar, Show Parameters, Show DocMap: You can set the visibility of these items. Note that Document map will not be available for all reports. It has to be created with either Report Builder or BIDS.

Zoom: Allows you to set the viewing size.

Format: Default is HTML 4.0. You can select other types such as Excel or PDF. Based on the type you select, report will be either shown in the web page or will be downloading to client machine.

Section: This allows you to set the page to be displayed. If you set 3 for this, 3rd page will be open as the default page.

DocMap ID: It is supposed to show the element set with this as the default page. I could not figure it out the way of setting it, for me, it did not work.

Report Parameters: This allows to set parameters default values.

Once settings are done, add a dashboard and place the report, and publish to SharePoint site. SharePoint site can be set with File Button –> Designer Options –> Server –> SharePoint URL. Make sure it is set before publishing.

Here is the published dashboard.

PerformancePoint 2010: Decomposition Tree is not available

Are you unable to find the Decomposition Tree in the menu? Is it not visible?

Reason is simple, make sure SilverLight 3 (or 4) is installed. Once it is installed, it will be available for you. Thanks for Dinusha for reminding me on this.

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part III

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated mode is straight forward and simple. If you have read my post Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part I and Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part , then you know the way of configuring it. You will not face much issues after configuring if it is done with Standalone SharePoint 2010 architecture. If it is a SharePoint farm which components are distributed, few things should be considered. Here are some of points you need to consider when Reporting Services is configured in SharePoint 2010 Farm. Note that this was tested with a Web Application which Authentication is set as Classic and NTLM is set for Authentication Provider.

Domain accounts created
Reporting Services Account: Domain account, no admin privileges. Usually name it as a Domain\ReportingServices. Set this with Reporting Service Configuration Manager.

Application Pool Account: Domain account, no admin privileges. Usually name as Domain\SP_SSRSApp. Use this when the Web Application is created with SharePoint.

Site Collection (or Site) account: Domain account, need local admin rights. Usually name as Domain\SP_Admin.

** All accounts are maintained as Managed Accounts in SharePoint.

Reporting Services Windows Service Account
This should be a domain account. Once it is set with Reporting Services Configuration Manager, you need add the same account to SharePoint too. First, have it as a Managed Account (Central Administration –> Security –> General Security –> Configure Managed Accounts). Then add the account to the Site where you have configured Reporting Services Libraries. You can add this account to Home Members group. Errors you may get if it is not added are “The permissions granted to user ‘Domain\User’ (what ever account you used for logged in) are insufficient for performing this operation.” and “rsAccessDenied”. Note that error has no link with the service account but it is related to it. You may get this error with Report Builder or BIDS when deploying reports, or viewing reports.

In addition to that, you need to make sure that the account is added to Web Application content database which is created for Web Application. If not, you may get and error “Report Server has encountered a SharePoint error”.

Other than that, this account is in ReportServer database too. It is automatically added, but make sure that it is there.

Allow Anonymous Setting
Make sure that Allow Anonymous is set as false when the Web Application is created. I faced an issue related deployment from BIDS if this is not set to false (read my post on it). Once the application is created, double-check with IIS (Site –> Authentication) and see whether it is disabled. As per the Authentication and Authentication Provider we set, Only ASP.NET Authentication and Windows Authentication should be enabled.

SharePoint Integrated Reporting Services: BIDS pops up Login Dialog box indefinitely

Annoying…. This is all about SharePoint integrated Reporting Services deployment via BIDS. Web application for Reporting Services has been created with SharePoint 2010 and all necessary libraries are added (if you need to see the way of configuring SSRS 2008 R2 in SharePoint 2010, please see my posts Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part I and Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part II).

Now the problem is, popping up login dialog box indefinitely in BIDS when try to deploy reports, regardless of what account is supplied. As usual, went through the internet, found many solution but none of them worked for me. One of picked solutions by me is, disabling anonymous user. But it did not work too.

Solution
After all unsuccessful attempts, I created a new web application and set the Anonymous Access to No when creating the Web Application. Then I pointed the project’s target URL to newly created site, Wow, no login dialog box. I tried the same with old site too (by disabling Anonymous Authentication in IIS) but did not work. So, not sure about this solution but it worked with the new site. If you face the same, try a new site and see.

PerformancePoint 2010 Error: The URL is not available, does not reference a SharePoint site, or you do not have permission to connect.

If you face for this error when connecting to SharePoint site through PerformancePoint designer;

… then check the domain account which has been used for PerformancePoint service application has been added to site content database as an owner. Once added, you are not going to see this error .

PerformancePoint 2010 Error: The URL must be in one of the following zones: Local, Intranet, or Trusted

If you face for this error when connecting to SharePoint server from Dashboard Designer;

… simply add the site for Trusted Sites in your Internet Explorer.

MCTS: SQL Server Database Administrator – Maintaining a MS SQL Server Database 2008 – 6231A Course

Course 6231A: Maintaining a SQL Server 2008 Database – This is my first class for 2011. Class will be started on Sunday, 23rd January 2011. If you are a DBA or interest on SQL Server Administration, join with us. Some topics that are covered with this course are Installing and Configuring, Security, Back up strategies, Disaster Recovery, High Availability, Replication and data transferring (see more on http://www.microsoft.com/learning/en/us/course.aspx?id=6231A).

Good news! Microsoft gives 40% discount for students for Microsoft exams. All you have to do is, go to an authorized Centre and prove that you are a student of any university (both private and government), then you get 40% discount for any exam. Good Luck!

Cannot login to SharePoint 2010 site when the Host Header is set

Everybody prefers a good URL for sites rather than server name with ports. Same for SharePoint Web Applications (SharePoint sites). When a Web Application is created, it allows us to set the URL for the site as we want through Host Header as well as the port. For example, when a Web Application is created as http://SPServerName:3000 , URL can be set as http://MyTestWeb.com with Host Header input box. Once created you need to make sure that a DNS record is created and pointed to SharePoint server.

My environment is set up with two Windows 2008 R2 Enterprise Editions. Once server is configured as the Domain Controller and other has SharePoint 2010 and SQL Server 2008 R2.

Here is the way I set;

Add URL I need for the Web Application:

Set DNS record in DNS Server, in my case it is DC:

Done. I should be able to navigate my site (after creating a Site Collection for the Web Application) using the URL set with Host Header (in my case, it is http://bi.andromeda.com) without using http://W2008R2-SP:30000 . But I am unable….

My Problem
When I open the browser for http://bi.andromeda.com , browser prompts me the authentication dialog box and asks for an user id and a password. It prompts three times even with correct user id and password and then opens a blank page. Could not figure out the reason for a while, finally Prabath sent me an URL that contains a solution for this. The solution is given by Tech Battle Field and post is http://spiderwool.blogspot.com/2010/09/access-denied-host-header-sharepoint.html.

In summary, solution is adding a new Multi-String registry key named BackConnectionHostNames in SharePoint server at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0 and set the host names with values (Read the post for more details). This is what I did:

Finally it worked. Thanks for Tech Battle Field for sharing this and thanks for Prabath for guiding me onto it.

Awarded Microsoft MVP again for 6th time

MVP again!

Microsoft has awarded the title again for the 6th time, thanks for Microsoft, Microsoft Sri Lanka, Wellignton and Lilian for recommending me.

Two new MVPs are introduced; Prabath Fonseka (SharePoint) and Preethiviraj (SQL), Congratulation guys!

Analysis Services Security: Dimension Data – Select all members or Deselect all members?

While configuring Analysis Services security on Dimension Data, I did not pay much attention on two options available with the screen; Select all members and Deselect all members. However the importance of them was raised up when my colleague Asanka Padmakumara showed a point on them. Which one should we select? See the screen below;

I have selected the first option which is Select all members and granted permission on Accessories and Bikes. I can do the same, select the other option too. Let’s move into Advanced section and see how it has been set;

Note that other two categories (Clothing and Components) have been added under Denied member set. What we have set? Yes, we granted permission on Accessories and Bikes but we have done indirectly is, denying permission on Clothing and Components. Main thing we need to consider is, permission on future categories. Since the items we did not select have gone to Denied member list, all future items will be available for this role. If you need to let this role to access future categories without explicitly setting them, use Select all members for setting permission.

Now let’s use the other option; Deselect all members;

I have given same permission set but the option selected is Deselect all members. Have a look on Advanced section now;

Now you can see the items we selected have been added to Allowed member set. This means all other items we did not select are denied to this role. What will happen to future categories? This is the difference. Since we have explicitly added Accessories and Bikes to Allowed member set, future items will NOT be available under Allowed member set. If you need to disallow this role to access future categories without explicitly setting them, use Deselect all members for setting permission.

Analysis Services Security: DENY does not override GRANT

Is the famous rule of thumb broken? I am sure that you all are aware the rule and you know that DENY always wins over GRANT. If you want to see an exception (or a situation) read one of my previous posts “Column GRANT overrides DENY TABLE, Avoiding with Common Criteria Compliance”.

This post discusses another situation, it is not with SQL Server Relational Engine, it is will SQL Server Analysis Services.

Analysis Services behaves bit differently. As you know, if an object of SQL Server database is denied explicitly, it is always denied even with an explicit grant. For example, if you are in a Role that grants SELECT on Table1 and in another role that denies SELECT on Table1, you will not be able to execute SELECT on Table1. Analysis Services authorizes on items differently. It unions all grants and authorizes. If you are in a Role that grants on Item1 and Item2 (Note that in Analysis Services, once items are granted, all other items are denied, no need to explicitly deny them as SQL Server database engine objects) and in another Role that grants Item2 and Item3, you are granted for Item1, Item2, and Item3.

Let me show you a sample on this. The below image shows a Role in Analysis Services Adventure Works DW 2008R2 database. This is how it is created;

• Name: Business Users
• Membership: Dinesh
• Cubes: Adventure Works – Read
• Dimension Data:
  • Product Dimension, Category – granted for Accessories and Bikes
  • Other Dimensions – granted for all

Second image shows another role in same database. Its properties are as follows;

• Name: Coordinators
• Membership: Dinesh
• Cubes: Adventure Works – Read
• Dimension Data:
• Product Dimension, Category – granted for Bikes and Clothing
• Other Dimensions – granted for all

Business Users role denies for Clothing and Components. Coordinators role denies for Accessories and Components. When Dinesh sees data, he sees union of both; Accessories, Clothing, and Bikes. The reason for this is, Analysis Services uses all GRANTs from all Roles for connected user and then authorizes. That is why he sees Accessories and Clothing even though they are denied from each role. This is how he sees when Categories are loaded with Excel.

Note: This type of security cab be tested with Management Studio too. Start Cube Browser and click on Change User icon. The opened window allows you to set the context with Current User, Other User, or Roles.

Way to Visualize: Project Crescent; What is it?

The Era that Business User is used to carry an armload of tools for analyzing and visualizing data, has come to an end!!!!! Yes, Microsoft never stops making business user’s life easier. Microsoft never stop supporting business user to manage and visualize data in a rich way. Microsoft has come out with an exciting facility; The Project Crescent.

What is Project Crescent?

Project Crescent is the recent Microsoft Business Intelligence team innovation that helps to manage and visualize large amount of data quickly in very rich format. It has been built entirely on Silverlight and will be available as a BI client. It allows to show the insights of the business with reports, with highly interactive visualization and animations.

The releasing date of this is unknown. Most probably it will be released with Denali, at the end of 2011 or beginning of 2012. Long time to wait…..

Here is a sneak peak of project Crescent, in Amir Netz’ demo at PASS Summit 2010:

Here is a post from SQL Server Reporting Services team blog: http://www.silverlightshow.net/news/Project-Crescent-built-entirely-on-Silverlight.aspx

Reporting Services: Difference between RDL and RDLC files

Microsoft Reporting Services is an enterprise-capable reporting solution that can be used to create all types of reports such as Production, Analytical, and Dashboard. Building reports is done through Business Intelligence Development Studio which is an extension of Visual Studio. Reports are created as RDL (Report Definition Language) files. Once the reports are created with BIDS, they (RDLs) are hosted in Reporting Services and users can be accessed them in many ways.

What are RDLC files?
RDLC: Report Definition Language, Client Side files are files that are generated with either ReportViewer Control that comes with Visual Studio 2008 Windows or ASP.NET project templates, or Reporing –> Report Application project template. It is a XML file, just like the RDL. It uses the same XML schema that is used by RDL files but RDLC does not require to have values for all elements such as query text. If RDLC file is needed to be hosted in Reporting Services, it needs to be renamed as RDL and all empty elements must be filled manually.

Reporting with ReportViewer Control
The ReportViewer Control allows us to embed a report to ASP.NET web page or Windows form. It supports two modes: Remote Processing mode and Local Processing mode. The Remote Processing mode allows to connect with reports that have been already deployed to SSRS instance. In this case, all the processing take place at the SSRS server. The Local Processing mode allows to create reports within Visual Studio itself. The extension of them are RDLC. This does not require an instance of Reporting Services, hence processing is handled by the client application.

Here are some of important points to remember about these two modes:

• Local Processing mode does not require an instance of Reporting Services, hence a license for Reporting Services is not required.
• Local Processing mode is not recommended for large reports. It is used for small, OLTP types of reports. They may run infrequently.
• Remote Processing can be scaled out but Local Processing.
• Local Processing mode supports following extensions only:
• Visual Studio 2008: PDF and Excel
• Visual Studio 2010: PDF, Excel and Word
• Local Processing mode with Visual Studio 2008 does not support Tablix data region. Visual Studio 2010 supports.
• Mapping parameters with query parameters has to be done manually with Local Processing mode.

Future version of SQL Server: “Denali”, CTP available for downloading

Microsoft announced that Community Technology Preview of SQL Server code-named “Denali” available for downloading. This CTP contains enhancements mainly on SQL Server high availability and engine not with BI components such as Analysis Services and Reporting Services.

To see details: http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx

To download: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en

To see the enhancements: http://msdn.microsoft.com/en-us/library/bb500435(SQL.110).aspx

Ready-made Virtual Machine for SharePoint 2010

Are you looking for pre-built virtual machine that includes SharePoint 2010? If yes, here it is… it is available for downloading.

Not only SharePoint 2010, it contains:

• Microsoft Office Communication Server 2007 R2
• Microsoft Visual Studio 2010
• Microsoft SharePoint Server 2010 Enterprise Edition
• Microsoft Office Web Applications
• Microsoft FAST Search for SharePoint 2010
• Microsoft Project Server 2010
• Microsoft Office Professional Plus 2010
• Microsoft Visio 2010
• Microsoft Project 2010
• Microsoft Office Communicator 2007 R2

For more details and downloading: 2010 Information Worker Demonstration and Evaluation Virtual Machine.

SharePoint Videos: SharePoint 2010 Developer Training Course

Looking for resources to learn SharePoint 2010 development? So many books, articles, and blog posts? Here is an easy way to learn it;

Site: SharePoint 2010 Developer Training Course
Download link: SharePoint 2010 Developer Training Kit

This training kit provides videos and hands on labs which are very useful to understand how SharePoint 2010 works and how it can be used.

We must thank Chris Mayo, Donovan Follette, Girish Raja, Paul Stubbs, and Steve Fox for preparing something like this for developers.

SSIS: Replacing Slowly Changing Dimension (SCD) wizard with the MERGE statement

Slowly Changing Dimensions are business entities in DW/BI systems that have attributes that do not change very often. In order to handle loading of SCDs, we use SQL Server Integration Services Data Flow component: Slowly Changing Dimension Component. It is a wizard that helps us to handle changing attributes.

If you are a SSIS developer and have used SCD wizard, you have already seen the bad performance on it. It works fine with a dataset that contains records less than something around 5000 (this is what I have seen, please do not consider it is as a benchmark) but gives very slow performance on beyond. Recently, I noticed that one of the projects done had used SCD wizard for almost all data loading and the time it takes for completing the load has gone from 5 minutes for 5-6 hours. It significantly increases the time when the data load is getting higher and higher.

How to improve the performance of loading of SCDs?
I had to give a solution for this…. two things came into my mind, one is Kimball Method SSIS Slowly Changing Dimension Component (will make a separate post on this) and the other is MERGE T-SQL statement. Although the MERGE implementation is bit complex, I thought to recommend the MERGE because many have accepted that the performance it gives is superb. The below table shows a comparison made on three different approaches on SCDs loading with some criteria (which is quoted from Matt Masson’s presentation named Performance Design Pattern).

You can easily notice that MERGE gives the best performance though there are drawbacks.

Type of Slowly Changing Dimensions
Slowly Changing Dimensions are categorized into three types named: Type 1, Type 2, and Type3. The Type 1 SCD does not maintain the history of changing attributes, it overwrites values of the attributes. Type 2 maintains historical values for changing attributes. Type 3 that we do not use much (I have not used it at all) maintains separate columns for changed attributes. SSIS SCD wizard supports both Type 1 and Type 2.

Using MERGE instead of SCD wizard
Replacement of SCD wizard with MERGE is not a straightforward technique. If the SCD has both Type 1 and Type 2 types attributes, they need to be handled separately. Let’s create a simple table with following set of data, in order to see the way of handling them with MERGE.
Following attributes in this table are Type 1:

• FirstName
• LastName

Following attributes are Type 2:

• MaritalStatus
• Country

Following script creates the Dimension table and inserts data;

CREATE TABLE dbo.DimCustomer

(

    CustomerKey int IDENTITY(1,1) PRIMARY KEY

    , SourceCode char(5) NOT NULL

    , FirstName varchar(50) NOT NULL -- Type 1

    , LastName varchar(50) NOT NULL -- Type 1

    , MaritalStatus char(1) NOT NULL -- Type 2

    , Country  varchar(50) NOT NULL -- Type 2

    , StartDate date NOT NULL

    , EndDate date NOT NULL

    , CONSTRAINT IX_DimCustomer_SourceCode UNIQUE (SourceCode)

)

GO

INSERT INTO dbo.DimCustomer

    (SourceCode, FirstName, LastName, MaritalStatus, Country

    , StartDate, EndDate)

VALUES

    ('AAAA1', 'Dinesh', 'Priyankara', 'M', 'Sri Lanka'

        , '01/01/2009', '12/31/9999')

    , ('AAAA2', 'Yeshan', 'Senthush', 'S', 'Sri Lanka'

        , '01/01/2009', '12/31/9999')

    , ('AAAA3', 'Jane', 'Alvarez', 'S', 'France'

        , '01/01/2009', '12/31/9999')

    , ('AAAA4', 'Shannon', 'Carlson', 'M', 'India'

        , '01/01/2009', '12/31/9999')

GO

When data loading of SCDs are handled with SSIS,what we usually do is, once the data set is captured, we send it through SCD component. The SCD component inserts new data, updates Type 1 data, and finally inserts Type 2 data. If we are to use MERGE, we need to send the captured data to a temporary table and then use Execute SQL Task for executing the MERGE statement. Assume that following table is the temporary table and it is loaded with captured data. 

This code creates the table and populates data;

CREATE TABLE dbo.TempDimCustomer

(

    SourceCode char(5) NOT NULL

    , FirstName varchar(50) NOT NULL

    , LastName varchar(50) NOT NULL

    , MaritalStatus char(1) NOT NULL

    , Country  varchar(50) NOT NULL

)    

GO

INSERT INTO dbo.TempDimCustomer

    (SourceCode, FirstName, LastName, MaritalStatus, Country)

VALUES

    ('AAAA1', 'Dinesh', 'Priyankara', 'M', 'Sri Lanka')

    , ('AAAA2', 'Yeshan', 'Santhush', 'S', 'Sri Lanka')

    , ('AAAA3', 'Jane', 'Alvarez', 'M', 'France')

    , ('AAAA4', 'Shannon', 'Carlson', 'M', 'German')

    , ('AAAA5', 'Jon', 'Yang', 'M', 'Japan')

GO

Handling Type 1 attributes
The below codes shows the MERGE statement that updates Type 1 attributes. Note that it goes through all records and updates.

MERGE INTO dbo.DimCustomer c

USING dbo.TempDimCustomer tc

ON (c.SourceCode = tc.SourceCode)

WHEN MATCHED 

    AND 

        (c.FirstName != tc.FirstName

        OR c.LastName != tc.LastName)

    THEN UPDATE

        SET c.FirstName = tc.FirstName

            , c.LastName = tc.LastName;

Handling Type 2 attributes
Handling Type 2 is tricky. If a record is changed, we need to update the old record, setting the EndDate as current date, and then insert a new record for it, with StartDate as current date. See the code below;

INSERT INTO DimCustomer

    (SourceCode, FirstName, LastName, MaritalStatus, Country

    , StartDate, EndDate)

SELECT Modified.SourceCode, Modified.FirstName

    , Modified.LastName, Modified.MaritalStatus

    , Modified.Country, Modified.StartDate

    , Modified.EndDate

FROM

    (MERGE INTO dbo.DimCustomer c

    USING dbo.TempDImCustomer tc

    ON (c.SourceCode = tc.SourceCode)

    WHEN NOT MATCHED 

        THEN INSERT

            (SourceCode, FirstName, LastName, MaritalStatus, Country

            , StartDate, EndDate)

            VALUES

            (tc.SourceCode, tc.FirstName, tc.LastName, tc.MaritalStatus, tc.Country

            , GetDate(), '12/31/9999')

    WHEN MATCHED 

        AND c.EndDate = '12/31/9999'

        AND (c.MaritalStatus != tc.MaritalStatus

            OR c.Country != tc.Country)

        THEN UPDATE

            SET c.EndDate = getDate()

        OUTPUT 

            $Action Action, tc.SourceCode, tc.FirstName, tc.LastName

            , tc.MaritalStatus, tc.Country

            , '10/28/2010' StartDate, '12/31/9999' EndDate) AS Modified

WHERE Modified.Action = 'UPDATE';

See the inner MERGE first. With first NOT MATCHED section, it inserts new records. Next is for finding MACTHED and Type 2 changes. Note that the check is done only on latest records (EndDate = ‘12/31/9999’). If found, EndDate is updated and records are returned as a OUTPUT of the MERGE. This output is captured by outer INSERT statement and inserts them as new records. Done!

Please do test both approaches before deciding the technique. If the data set is large, it would be better to use MERGE instead of SSIS SCD component.