Silverlight PivotViewer

Silverlight PivotViewer is an amazing control to visualize large data.  It is highly adopted in many business scenarios.  The Channel9 talk touches key features of the control.

It makes use of DeepZoom technology to visualized large data sets.  it is a closed control, due to that we cannot applying styling in a direct way. 

Extending Control

The workaround is to understand the object hierarchy of the control and Extending the control with accessible interface of the underlying controls.  Using this technique you can customize visual appearance of the control, refer XPert360 blog.

To get the reference of existing object model you can use the SilverlightSpy.  You can refer the sample code here for exposing wrapper.  To take step by step lesson to customize the control refer the CodePlex Lessons in PivotViewer.

In addition to adding styles to the control, we can add new control or view to the control, you can refer Roger Noble blog for the same.  You can make use of MVVM pattern while using PivotViewer, refer Timmy Kokke blog for the same.

Collections

To use the power of PivotViewer, you must have solid collections in place.  There are array of tools to create collections.  PAuthor is my favorite tool, in addition to HTML Template based image generation, you can build your own extensions to build image using WPF controls.  For real time data, JIT collections are ideal.  We can enhance the JIT collection library based on your needs.

Sample Collections

There is a great collection of TechEd and PASS events as Pivot collections listed here.  We can also use Desktop tool to explore the public collections like these.

SSIS Programming reference

Generating SSIS using meta data is a powerful technique when you want to move large number of table from source to target.  In this post I will highlight the programming references to create the SSIS dynamically.

• Microsoft.SqlServer.Dts.Runtime.Package is the highlevel object which hold all the objects of the package.  You can create New Package object to start building you object mode.
• Connections collection holds connections. 
  Example: PackageObj.Connections.Add("OLEDB");
  Refer MSDN for list of connection types
• You can change the custom property of the connection by accessing the Property collection of the connection
  Example: ConnectionObj.Properties["Format"].SetValue(csvFile, "Delimited");
• Executable: By accessing the Executable collection you can add DFT, SQL Task or any other task to Package.
  Example: Package.Executables.Add(“STOCK:SEQUENCE”);
  For list of moniker, refer the SQLIS blog
• When you are dealing with Data Flow Task (DFT), it is complex due to the fact DFT are COM and we have a CManagedComponentWrapper to access them.
• The hierarchy of object structure is
• Executable
• TaskHost
• MainPipe
• IDTSComponentMetaData100

Package p = new Package();
Executable e = p.Executables.Add("DTS.Pipeline.1");
TaskHost thMainPipe = e as TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;

• In DFT, you can add pipeline items by using ComponentMetaDataCollection of MainPipe object

IDTSComponentMetaData100 pipeLineItem = MainPipeObj.ComponentMetaDataCollection.New();
pipeLineItem.ComponentClassID = "DTSAdapter.OleDbSource.2";

• When you are using the Source object in PipeLine, make sure it get initiated using IDTSComponentMetaData100.Instantiate & CManagedComponentWrapper.ProvideComponentProperties methods.  These methods are explicit interface implementation so make sure necessary type cast done when you call the methods.

CManagedComponentWrapper InstanceSource = IDTSComponentMetaData100Obj.Instantiate();
InstanceSource.ProvideComponentProperties();

• For Source, Destination tasks make sure the connection are associated.

public static void SetConnection(
IDTSComponentMetaData100 obj, ConnectionManager con)
{
obj.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(con);
obj.RuntimeConnectionCollection[0].ConnectionManagerID = con.ID;
}

• After the connection is set, it needs to be refreshed to get the meta data

CManagedComponentWrapperObj.AcquireConnections(null);
CManagedComponentWrapperObj.ReinitializeMetaData();
CManagedComponentWrapperObj.ReleaseConnections();

• You can attach multiple item to pipeline. 
• To link the pipeline objects, you can use Attach Path method

IDTSPath100 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(source.OutputCollection[0], target.InputCollection[0]);

• To map the columns between pipeline items, you need to use the column collections

public void MapColumns(
IDTSComponentMetaData100 DestinationTask
, CManagedComponentWrapper InstanceDestination
, DTSUsageType dtsUsageType)
{
IDTSInput100 input = DestinationTask.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSInputColumn100 vCol = null;

if (dtsUsageType == DTSUsageType.UT_READONLY)
{
foreach (IDTSVirtualInputColumn100 vColumn in  vInput.VirtualInputColumnCollection)
{
  InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
}

foreach (IDTSInputColumn100 col in input.InputColumnCollection)
{
IDTSExternalMetadataColumn100 exCol = input.ExternalMetadataColumnCollection[col.Name];
InstanceDestination.MapInputColumn(input.ID, col.ID, exCol.ID);
}
}
else
{
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
IDTSExternalMetadataColumn100 exCol = input.ExternalMetadataColumnCollection[vColumn.Name];
InstanceDestination.MapInputColumn(input.ID, vCol.ID, exCol.ID);
                    }
                }
        }

• For connecting executable in Control Tasks, you can use PrecedenceConstraint class to associate the Task for sequential execution.

PrecedenceConstraint prePC = DFT.ParentContainer.PrecedenceConstraints.Add(preExec, postExec);

SSMS Cache

The Microsoft SQL Server Management Studio is the Visual Studio for DB guys, with SQL Server 2008 R2, the IntelliSense feature in SSMS is awesome.  It saves time and the experience is cool.  Some time the IntelliSense will not work.  There can be several reason for this. 

• You may be typing multiple statements with out proper batch separation (GO). 
• The underlying schema is changed.

If it is schema change you can try refreshing the Cache, must of the time it helps.

Reading Dynamic SQL Result

Usage of dynamic SQL Statement is very common when want to do some meta data based operations.  Basically you create the dynamic statement using string concatenation and get it executed using EXEC or SP_EXECUTESQL.

The biggest challenge with dynamic SQL is the dynamic SQL are get executed in its context so you cannot pass variable, read variable seamlessly.  You need to pass the main context variable to dynamic sql context.

The following sample shows how you can pass and read value from dynamic SQL Statement.  For more detail check MSDN!

 

    DECLARE @RC INT
    DECLARE @SourceDB SYSNAME
    DECLARE @SourceTable SYSNAME
    DECLARE @sql nVARCHAR(MAX)
    DECLARE @TableName VARCHAR(MAX)
    
    SET @TableName = 'Emp'
    
    SELECT @SourceDB = SourceDatabase, 
           @SourceTable = SourceTable
      FROM Metadata 
     WHERE DestinationTable = @TableName
     
    SET @sql = 'SELECT @dynRC = COUNT(*)
                  FROM ' + @SourceDB + '.dbo.' + @SourceTable

    EXEC SP_EXECUTESQL @sql,N'@dynRC INT OUTPUT', 
                       @dynRC = @RC OUTPUT
    
    SELECT @RC

Dependent Objects

How many time you need to scan thru a stored procedure to find what are all the tables it uses.  Specially when you are working on a legacy application, it becomes most frequent task.

To simplify the work you can use SYS.SQL_Expression_Dependencies system table.

You can use the following CTE to get deeper object references

CREATE PROC [dbo].GetDependentObjectList @ObjectName AS sysname
AS
WITH ObjectDepends(entity_name,referenced_database_name,referenced_schema, referenced_entity, referenced_id,level)
AS
(
    SELECT entity_name = CASE referencing_class
                              WHEN 1 THEN OBJECT_NAME(referencing_id)
                              WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t 
                                           WHERE t.object_id = sed.referencing_id)
                              WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
                                           WHERE st.object_id = sed.referencing_id) COLLATE database_default
                           END
                        ,referenced_database_name
                        ,referenced_schema_name
                        ,referenced_entity_name
                        ,referenced_id
                        ,0 AS level 
    FROM SYS.SQL_Expression_Dependencies AS sed 
    WHERE OBJECT_NAME(referencing_id) = @ObjectName 
UNION ALL
    SELECT entity_name = CASE sed.referencing_class
                              WHEN 1 THEN OBJECT_NAME(sed.referencing_id)
                              WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t 
                                           WHERE t.object_id = sed.referencing_id)
                              WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
                                           WHERE st.object_id = sed.referencing_id) COLLATE database_default
                           END
                        ,sed.referenced_database_name
                        ,sed.referenced_schema_name
                        ,sed.referenced_entity_name
                        ,sed.referenced_id
                        ,level + 1   
    FROM ObjectDepends AS o
    JOIN SYS.SQL_Expression_Dependencies AS sed ON sed.referencing_id = o.referenced_id
)
 
SELECT entity_name AS referencing_entity, referenced_database_name,referenced_schema,referenced_entity, level
  FROM ObjectDepends
 ORDER BY level;

GO

Run program always as an Admin

It become necessary to run the Visual Studio most of the time in Admin mode specially when working on Azure project. Instead of every time selecting “Run as administrator”

we can update the shortcut itself. It will save time! Select the shortcut property and in Compatibility tab set privilege level

Thanks to Deepak for sharing this tips.

Hold on Ctrl + Shift while clicking on an Task bar shortcut to open the app in Admin mode.

Thanks to Karunakar for this tips.

SQL Server Tools

SQL Server 2008 R2 comes with set of command line tools.  They are handy tools which can be used in any scripting, batch program.

Category	Utility
SSIS	dtexec Utility (SSIS Tool)
	dtutil Utility (SSIS Tool)
SSAS	Deployment Utility (Analysis Services - Multidimensional Data)
SSRS	rs Utility
	rsconfig Utility
	rskeymgmt Utility
Data Tools	tablediff Utility
	bcp Utility
TSQL	sqlcmd Utility
	osql Utility (Deprecated)
Profiler	Profiler Utility
Diagnostic	SQLdiag Utility
Backup	sqllogship Application
maintenance plan	sqlmaint Utility
Power Shell	sqlps Utility
DB Engine	sqlservr Application
SSMS	Ssms Utility
DB Tuning	dta Utility
SQL Agent	sqlagent90 Application

The blue highlight are frequently used commands.

For details on each command check out MSDN http://msdn.microsoft.com/en-us/library/ms162843.aspx

SQL Server Statistics

SQL server maintains statistics on distribution of column values across the row.  This helps SQL server to decide the right Execution Plan while reading data.  We can see the statistics of a table by using SSMS.

If you see the properties of statistics, you can notice it capture the distribution of values in the table.

In the above case, it capture the selectivity of each National ID,  This helps SQL server to know upfront how many rows a filter condition will fetch.  These statistics are updated periodically if you configured your data base for “Auto Statistic Update”.   You can also update the statistic by running TSQL Statement.  The more your statistics are up to date, the SQL server can create better execution plan.

UPDATE STATISTICS table_or_indexed_view_name

When you configured your DB for Auto create statistics.  When ever you use a column in Join Clause or Where clause the SQL server will create an statistics on the column automatically.

When you configured your DB for Auto Update Statistics, the statistics are get updated when there is more then 20%+500 row changes happens on the table.

SELECT rowmodctr
FROM SYSINDEXES WHERE ID = OBJECT_ID('P')
AND name = '_WA_Sys_00000001_36D11DD4'

You can see the changes of a particular column (not table change) are tracked in the row counter.  Using this SQL Server will automatically trigger the Update Statistics.

Based on your system need you can disable the Auto Create/Update Statistics, which will give some performance gain.  But in such case you make sure that you have some manual mechanisms to have proper statistics and update on frequent intervals.

The problem with Auto Create/Update Statistics is the system will no know you Peek user time, system ideal time.  Due to this Update Statistics may get triggered at you peek performance load.  In such case disable Auto update feature and have a nightly schedule to update the statistics.

SQL Server storage internals

SQL Server stores table data in Pages.  A Page is a the smallest unit of data storage in MS SQL Server.  The page size is 8 KB unit, due to this, the maximum size of a row is 8 KB.

CREATE TABLE Tbl
(
    ID INT,
    Data CHAR(8000),
    Info CHAR(50)
)

The above code will throw error:

Msg 1701, Level 16, State 1, Line 1
Creating or altering table 'T' failed because the minimum row size would be 16011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
But when we create table with VARCHAR, TEXT, BLOB types the data get stored out side of the data Page and pointer is get stored in the data row, that the reason we can have table with multiple VARCHAR(MAX) columns.

Every heap table contains a Page called IAM which holds the pointers to all data pages.  You can find this information by querying  SYSINDEXES table.

SELECT [first],[root],[FirstIAM] 
FROM SYSINDEXES 
WHERE ID = OBJECT_ID('Tbl')

For tiny table which has less then 64 KB information the memory allocated page by page.  The moment table reaches more than 64 KB (8 Pages), the memory allocation happens Extend by Extend.  An extend is 8 consecutive pages.  So for the table which are more than 64 KB, the IAM Page consists of pointer to all the Extends of the table.

So internally for any heap table IAM is the starting point which says where the data is stored.  The similar concept applies for Clustered and non-clustered tables.

The following query fill give information about how many pages used for a given table.

SELECT dpages,reserved, [rows]
 FROM SYSINDEXES 
WHERE ID = OBJECT_ID('Tbl') 

To know the data structure of each table type check MSDN

Extend

The next level of memory unit in SQL server is Extend.  An Extend can be Mixed Extend or Uniform Extend.  When an extend consists of pages of single table then it is Uniform Extend.  If an Extend consists of pages which are belong to different tables then it is called Mixed Extend.



File

The MDF, NDF files (data files) which we create for a Database are divided into Extends and used.  The first Extend’s first page in each file is a file header Page that contains information about the file. The header page has details about the address of available free uniform extends GAM, available free mixed extends SGAM, Available pages with free spaces PFS.

• GAM – Global Allocation Map
• List of free uniform extends, which can be allocated.  When ever the Engine needs a new uniform extends, it takes up one from GAM.
• SGAM – Shared Global Allocation Map
• List of free mixed extends, which can be allocated. When ever the Engine needs a new new page, it takes up one from SGAM, scan thru the Extend to occupy the free Page. 
• PFS – Page free space
• List of pages which has some free space to hold new data row.  When a new data row needs to be inserted it checks the table’s associated pages which has free space to accommodate the new row.  If no page available with free space, it takes up new Page / Extend.

For further read check the blog, though it is very old still most of them applicable.

To summarize Database data can be stored in one or more files. Each file consists of logical unit called Extend.  Each extend consists of 8 Pages.  Each Page can hold one or more data rows.

SSMS Tips

Comma separated string

There are many times where you want to convert list of values to comma separated string.  This can be achieved by simple File & Replace.

Make sure in File Options, you selected “Use: Regular Expressions” option.  The same you can do by replacing “\n” with “’,’” for string.  Note: you may need to add beginning and end quotes, after Find & Replace.

Find in Files

Some time you may need to check for some dependency of a object, by checking all the DB Scripts stored in File System.  In such case you can make use of File in Files feature of SSMS.

Scroll keyboard shortcut

When you want to scroll window text with out using mouse you can use Ctrl+Arrow Key short cut to scroll the window.

Fiddler & Local host

Fiddle will not detect local host traffic.  There are different tricks to make it work.  The most known are

1. http://localhost.:30408/Page.asmx – Not the dot after localhost
2. http://127.0.0.1.:30408/Page.asmx – Fixed IP followed by dot then : port number

Are you slicing CSV row data

When you slice the CSV row data with embedded commas, double quotes and line breaks.  it becomes complex to handle.  An well defined RegEx will help you here.  Here is handy RegEx “,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))” which works great.  Read more detail here.

Color code your SSMS Status bar for diff server

If you are working on multiple database at the same time and by mistake executing queries on different server (TRUNCATE on PROD!!).  Here is a cool tips for you.

When you are connecting to a server go to Connection Property tab and provide Customer Color. (may be RED for PROD).  The color will be visible in the Status bar.

You can notice the selected color in status bar.

SQL Code formatter

It is annoying to see improperly formatted lengthy SQL code.  For C# Visual Studio does the job neatly.  For SQL I was looking for some plug-in or Tool which can format SQL code.  The long search come to end with an online tool.

Check out here http://www.dpriver.com/pp/sqlformat.htm

Double-Hop

After really spending two hours of time learned that impersonation will work only within same server and will not work across the servers.

Here is the guy neatly explained the scenario I went thru.

Virtualization Jump Start

Virtualization is the future.  The complete Azure platform is based on Virtualization.  Already Microsoft is making impact on this space.

Here is the Jump Start guide for Microsoft Virtualization

http://technet.microsoft.com/en-us/edge/Video/hh124559

SSIS Interview Questions

Entry Level Questions

1. What is the Control flow?
2. What is a Data flow?
3. How do you do Error handling in SSIS
4. How do you do Logging in SSIS? How do you do Custom Logging?
5. How do you deploy SSIS packages? And explain different deployment strategy?
6. How do you schedule SSIS packages to run on the fly
7. How do you run stored procedure and get data
8. Give a scenario: Want to insert a text file into database table, but during the upload want to
   change a column called as months - January, Feb, etc to a code, - 1,2,3.. .This code can be read
   from another database table called months. After the conversion of the data , upload the file. If
   there are any errors, write to error table. Then for all errors, read errors from database, create a file, and mail it to the supervisor.
9. What are variables and what is variable scope?
10. How do you debug a Packages
11. Explain Package configuration and how do you create custom configurations: XML and DB
12. How do you control flow and data flow in Transactions
13. Explain Multicast transformation –
14. Identify True or False - Using a checkpoint file in SSIS is just like issuing the CHECKPOINT
    command against the relational engine. It commits all of the data to the database.
15. How do you do set of operations in a Transaction?
16. Can you explain the Import\Export tool wizard?
17. What are the command line tools to execute SQL Server Integration Services
    packages?
18. Can you name some of the core SSIS components in the Business Intelligence
    Development Studio you work with on a regular basis when building an SSIS package?

For experienced candidate

1. True or False: SSIS has a default means to log all records updated, deleted or inserted on a per table basis.
2. What is a breakpoint in SSIS? How is it setup? How do you disable it?
3. Can you name 5 or more of the native SSIS connection managers?
4. How do you eliminate quotes from being uploaded from a flat file to SQL Server?
5. Can you name 5 or more of the main SSIS tool box widgets and their functionality?
6. Explain the pros and cons of deploying to a file system vs MSDB?
7. Explain architecture of SSIS?
8. Difference between Control Flow and Data Flow?
9. How to pass property value at Run time? How do you implement Package Configuration?
10. What are new features in SSIS 2008?
11. How would you pass a variable value to Child Package?
12. What is Execution Tree?
13. What are the points to keep in mind for performance improvement of the package?
14. How would you configure a data flow task so that it can transfer data to different table based on the
    city name in a source table column?
15. Difference between Union All and Merge Join?
16. Explain Lookup, fuzzy lookup, fuzzy grouping transformation?
17. How would you restart package from previous failure point? What are Checkpoints and how can we implement in SSIS?
18. Difference between asynchronous and synchronous transformations?
19. Asynchronous transformation have different Input and Output buffers and it is up to the component designer
    in an Async component to provide a column structure to the output buffer and hook up the data from the
    input.
20. How to achieve multiple threading in SSIS?
21. What is the default string data type of Excel source?
22. How to copy and rename a file to a dynamic location using SSIS?

SSIS Script Component

In SQL Server Integration services, we have Script Component, which can be used in Data Flow Task as Source, Destination or Transformation.  This one task needs little more understanding than the other components we have in SSIS.

1. How you want to use the Script Component?

Script Component as Source

If you selected Source, configure the Column of the source in the Script Component Editor.  You can have one more by using OUT OUTPUT buffer.  For each output buffer add COLUMN and configure the data types of columns.  Click on EDIT SCRIPT to provide details.

    public override void PreExecute()
    {
        base.PreExecute();
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void CreateNewOutputRows()
    {
        for (int i = 1; i <= 100; i++)
        {
            Output0Buffer.AddRow();
            Output0Buffer.RollNum = i;
            Output0Buffer.Name = "Ram " + i.ToString();
        }
    }

In the generated code (main.cs) you will see three place holder methods. 

• PreExecute – Execute once at the starting of the component.  Useful to set variable values.



• PostExecute – Executes once at the end of component execution.



• CreateNewOutputRows – This is the method you can generate the new rows.  In the above code I am using a loop to generate 100 rows of dummy data.

Based on the name you provided in in “Input and Output Column” setting of Script Component Editor.  The name is chosen.  In the above example it was Output0Buffer, which is default.

Script Component as Transformation

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.CustomerName = Row.CustomerName + " - " + Row.CustomerCode;
    }

You can mark the columns ReadWrite and edit the value using the ProcessInputRow method placeholder.  You can set ExclusionGroup property to non-zero value to create more OUTPUT buffers.

Script Component as Destination

It is very similar to SC as Transformation, except that you can not have more output buffers as it is irrelevant.  You can process the data row by row using ProcessInputRow method.

Refer Script task as Source 

Japan Earthquake

The big natural disaster and IT worlds reaction has some interesting observations.

1. How do you see the Earthquake impact, check out the sliding image.
2. How do you analyze the disaster, check out Microsoft Power Pivot.
3. How Microsoft & Apple does donation and using the opportunity as a marketing strategy by involving people in the donation process.

Unable to connect Remote SQL server using SSMS

The day started with an issue, I could not able to connect to our new SQL Server 2008 R2, but I could able to connect to it locally.

I went to thru the standard check list.

1. Is SQL Server Browser service is running?
2. Is SQL server configured for Remote connection?
3. Is the SQL Server port 1433 (SQL Server), 1434 (SQL Admin console), 4022 (Service Broker), 135 (T-SQL Debug), 2383 (Analysis services), 2382 (SQL Browser) are added in the exclusion list
   Ref: KB968872
4. Is the server able to ping and available in network.
5. Is the server able to connect via SQL Authentication.

The last one was the catch, I could able to connect via SQL Auth but on with Windows Auth, so the problem is Windows Auth, It happens when you are trying to use TCP/IP instead of Named pipes / Shared Memory.  In the SQL Login the Network protocol must be Named Pipes / Shared Memory.
For detailed read.

Great! with this change I could able to connect to the SQL Server!!

Later I figure out we can change the order of Protocol in Sql Server config Manager as well.

Controlling Windows Update - WUaUserv

For the last few days, the Windows Update was not working, because of that I could not able to connect to our Office Network.   The Windows update keep showing 80072efd exception.  Spend some time on the suggestions given in the site, but no luck.

When I connect to our Help desk team they suggested to STOP & START windows update service from command prompt, which worked absolutely fine.

1. Start > Run > Cmd to launch the Command Prompt
2. Type “sc stop wuauserv”  to stop and use start to start the service.

The same technique you can use when when you want to get right of Update Restart Popup!!

Windows Phone 7–Push Notification

Windows Phone 7 offers push notification feature, which helps you to avoid polling server for updates.  Any app can subscribe for push notification.  There are three types of notification.

1. Toast – A notification shown at the top of current screen.  Like the SMS alert shown.
2. Tile – The tile image or data in tile updated.  Like the Email / Message count updated as and when they arrive.
3. Raw – This is XML data send to the app to process.  This is available only when application is running.  The application can process the data in the manner it want.

How it works

1. When the Windows Phone Application start it register itself for Push Notification with "Microsoft Push Notification Service", to get unique URI for the device and for the App
2. The Windows Phone Application passes the URI to it Cloud Service.
3. Cloud Service sends notification with URI to MPN.
4. MPN routes the notifications to devices.
5. The device receives the notification and interoperates.

URI is unique for each device and application. 

Ref: Yochay kiriaty blog

Khan Academic

Bill Gates’ favorite teacher that’s how the web pages calls out.  Here is the guy Khan who teaches academic lessons online, he is teaching style become popular all over the world. 

He has the collection of his lectures on different topics in his web site.  www.khanacademy.org

SSRS with IE9

SSRS reports UI is not compatible with IE9, so you need to select compatibility mode to view SSRS in IE9.  The error message shown is misleading.  The following is the screenshot when you try to view SSRS report in IE9.

Build a better App

Microsoft rolled out one more learning portal to help developer.

Check out the “Build a better App” learning videos

http://www.microsoft.com/visualstudio/buildabetterapp/learning.aspx

Find Next in MS Word

I discovered a way to search the last searched word in MS Word.  (It was pain…  F3 doesn’t works here)

You press CTRL-F for Find; after the first occurrence of the word found, you want to close the window and hit some short-cut to repeat the search, for this you have two ways

Shift + F4
Ctrl + Alt + Y

May be a very silly post… but was lacking this knowledge for so long.

SSIS Tips & Tricks

In this blog I listed the simple tips & tricks which can be used in SQL Server SSIS packages.

• On Error Event:
  On Error event is a cascading event.  So when an error occurs on a task the event is raised for all the level up from the task to package.  So you should not place Error Email Notification tasks in OnError task.  You can log or accumulate the error details to send it across.
• Constraints
  You can implement logical And, Or on task constraints.  You need to set the flat in constraint property.
  
• Store configuration in SQL Server
  You can store the Package configuration in SQL, while doing that it is recommended to store the connection string of the configuration database in Environmental Variable.  While configuring environment variable make sure you are using System Variable and not User Variable.
  
• Source with variables
  In my ETL I had a scenario in which I need to keep the variable as a source data.  Just as work around I have selected dummy data from database and using derived column to add variables as additional column.  It is simple tip to create variable values as a Source Component.
• Event instead of Exception
  In Script task don’t use throw new Exception.  Instead use Dts.Events.FireError method.

Art of Interview

In a recent discussion with one of senior architect, he shared few tips to smartly identifying candidates skills.

• Instead of asking question of Design Patterns discussions around principles of patterns will show the candidates understanding on design.  For example Interface uses inversions of principles.
• Instead of asking questions on particular architecture, discussion on the candidates experience with different architectural patterns.  Get his experience on his experience with architectural patterns. He can talk about moments he compromised or committed the rules of architecture, which will help to understand the candidates exposure.  For example if the architecture is MVVM implementing simple Popup will needs more code.
• Simple check on latest language feature which he get implemented in his current project will show his passion around technology and getting implementing in the projects.
• Asking simple list to tools the candidate uses frequently will help to judge the kind of day to day work he does
• Start the  discussion around generic title like Build automation and get thoughts on what are different level of build automation possible and challenges.  Which will help to judge the broad spectrum of thinking and influencing capabilities.  or topic like ORM will be good for this.

The discussion revealed me that an interview can be just a conversion and knowledge sharing kind of session.

Moving SSRS Report DB

Recently we upgraded our SSRS servers host OS from Windows Server 2003 to Windows Server 2008 R2.  The reporting databases are in different server.  So we had the following plan.

1. Flatten SSRS Server
2. Install Windows Server 2008 R2
3. Install SSRS
4. Attach the existing Database.

The plan looks good, but the challenge was waiting for us after the update.  The step 4 was failing with an error “The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database.”

After a bit of Bing! we realized the importance of symmetric key.  We need to have a backup of symmetric key when ever we perform any of the following operations.

• Changing the service account for the Report Server service.

• Migrating a Reporting Services installation to a different computer.

• Configuring a new report server instance to share or use an existing report server database.