Sep 292015

Hi with the advent of the new generation of SSIS deployment model (via Integration Catalog) on to the SQL server, if you would have paid a keen attention to the list of databases in your SSMS, you would also see a new addition “SSISDB” and believe me this new database has tons of features which would enable you to have a great insight into your deployed packages.



Firstly I would like to mention if you use this new deployment model of SSIS out of the box you would be able to utilize good standard reports based in SSMS for tracking the errors in the SSIS and performance and other component level details.




But on top of it if again you would like to distribute these information across departments and not troubling your DBA team, SSRS comes in handy and there I step in to build the basic mock ups to provide the vital information relating to the SSIS execution runs, user details and more.


Feel free to upgrade and re-design the reports to your heart’s content with all the visualization required as per your needs and hope it helps you plan you IT set-up better.

Hope it helps.


Sep 292015

At times in some organizations I have found certain DBA’s struggling with the SQL job history window. Which to me is little inconvenient and tiny to look into window for the major bugs causing the job failures.


And to access this window, one needs SSMS access to the server thus singling out the DBA and their team to deal with the issues which can be even dealt by the support desk and business user for themselves where failure is cause by input data issues and visible by the history.

So I thought to build a better reporting mechanism for the same which could render out the report for the operations status of the SQL job and easy to read report of the same which can be even subscription driven feature of SSRS.


The above is simple report which can be easily deployed to one of your server’s and tweaked more to build more complex and better visualized report than just the table to you hearts content.

Hope it helps.


Sep 282015

Hi, ever caught up in dilemma where you try map your query parameter to pass in your OLEDB source and it seems to go into abyss, meaning nothing happens or no rows get returned with no clue in the log messages.

Hmm… Just that recently happened with me where I was trying to pass in one of the query parameters to my OLE DB source as follows:

SELECT * FROM [SomeTable] --This is a query for sample demo
WHERE [SomeKey] > <<Parameter>>


And eventually with loads of debugging with multiple drivers and login permissions we ended up in the discovering that the issue was aroused due the use of single line comments in the SQL which I just copied and bumped off in the OLE DB source component.

This comment in the SQL query was causing this issue when I queries the SQL 2008 R2 from the SSIS ETL package.

So finally the solution with other references I found was to use the block comments in SQL while bumping off in any place in SSIS.

SELECT * FROM [SomeTable] /*This is a query for sample demo*/
WHERE [SomeKey] > <<Parameter>>


The following article proved me quite helpful for the same and thought this experience of mine helps others.

Hope it helps.

Sep 272015

With the advent of the new age and technological advances, Reporting and BI is taking on a new shape in multiple dimensions.

One arena is expanding in terms of the data volumes (terming Big Data) another arena into more complex calculations and analysis and ease of dimensional modelling (products such as SSAS, Jedox, R, Python and more..) some in fancy reporting and interactive dashboards (like Power View/Maps/BI, SSRS).

But some thing caught up my attention recently was the usage of HTML 5 web sockets and its implementation in ASP MVC framework via Signal R framework. The combination of two opened up a whole new world of imaginations and solutions, I was holding aback in my wishes for a fair amount of time.

One of the imaginations I would like to put forth in this blog is as follows:

Real Time Monitoring Solutions for the Corporate Networks Servers


Many times in my previous experiences across organizations, Servers went down or application crashes occurred as disk ran out of space, or thread pool recycles, or processes/memory starvation occurred during the peak hours. All due to no insight into the server status across the network and their real-time status.

Though periodic jobs can help out extract the vital statistics (via Performance Counters) to the common database, of which the reports could be ran, but still no real time insight into the server was a real road-block.

Many times the commercial solutions though available are as usual with all the cost and managerial approvals are either un-affordable or just not right in some ways or other as off-the shelf products never fit the bill in all aspects of the business requirements. So to over come I mocked a quick solution to show case the power of the above frameworks in a simple POC which monitors any machine off its vital statistics (via Performance Counters) just by providing the Machine name.

The source code is attached in this blog for the same, and its details can be interpreted via the code as its simple enough with all the relevant documentation for the frameworks.

Just to add some pointers for my code references:

Note: Once the machine name is passed by the User from the UI for the statistics requested, the web app creates a timer on the server side which has an interval defined of 1 second on which it extracts the performance counter of the target machine and send it across to the request user via the signal R/ Web socket methodology.

In order to run the code only one change is required where the credentials need to be set in the user code (not in web.config as its a POC) which has access to the requested machine performance counters information.



Hope it helps.


Sep 252015

Well its been a long time blogging and as Title suggest I have been putting quite some time in past few months on SSRS reporting, and came up with an interesting challenge to blog about.

Recently I was mocking up a SSRS report where suddenly business users (testing the same) came up with a query that the reports rendered fine on the UI (SSRS Viewer) but when they would export it out to the MS-Word format the report would render quite out of sync in terms of page width and content over-flows.

And eventually with all my efforts of getting it in shape with all page margins, page width and other page layout orientation, the report would often break apart into other pages and hardly fitting in within the borders no matter the effort.

So finally coming down to the magic solution for the same was breaking the wide strings as they rendered on the report with the line breaks “vbCrLf” (i.e. VB code for line break) using custom function in SSRS.


Function LineBreaks(strVal as String, charNo as Int32) AS String
	Dim slen as Int32
	Dim rStr as String
	Dim iCount as Int32
	slen = strVal.Length
	iCount = 1
	while iCount <= slen
		rStr = rStr +Mid(strVal ,iCount, charNo) + vbcrlf
		iCount  = iCount + charNo
	End While
	rStr = rStr +Mid(strVal ,iCount, charNo)
	Return rStr
End Function

And using the above code in the text box expressions of Table/Matrix as:

= Code.LineBreaks(Fields!FieldName.Value, Variables!MaxLengthOfLine.Value)

Now in some cases this may not be essential, but in my instance with all the head scratching this code was a life saver. So as for few who are stuck in similar situation, feel free to leverage the above.

Hope it helps !!!