.Net

  • Regular Expression to get tables from SQL query

    If you’ve not come across regular expressions (RegEx) before then you’re missing out, they’re an incredibly powerful tool for matching, extracting or validating patterns of text. I had a use case this week where I needed to take a SQL query, and quickly identify every table or view that was used in the query. RegEx to the rescue! (?<=((FROM[ \n\r]+)|(JOIN[ \n\r]+)|(APPLY[…

    » Read more
  • BIML – What is it?

    I’ve noticed a growing trend over the last year – the ever growing presence of BIML (Business Intelligence Markup Language). So what is it? What does it do? And do you need to learn it? What is BIML? Simply, it’s a way of defining the functionality of an SSIS (Integration Services) package. If you’ve ever opened an SSIS .dtsx file in notepad you’ll see a daunting mess of GUIDs that you really don’t want to…

    » Read more
  • Pattern matching in SSIS using Regular Expressions and the Script component

    One of my favourite features of SSIS is the script component, and I know I’m not alone. Why? Because it brings the entire might of the .NET framework to SSIS, providing C# (in SQL 2008 onwards) and VB.NET extensibility for all those times where SSIS doesn’t quite have enough functionality out of the box. Such as? Well a problem I’ve come across a number of times is string parsing. Trying to search for and extract a specific pattern of…

    » Read more
  • Clearing SSRS Query cache

    When developing SQL Server Reporting Services (SSRS) reports, BIDS caches the query results when you preview the report. This cache is then used next time you run a preview. This has the benefit of speeding up report development, but it does cause a problem when you want to test changing data. A simple way of forcing the cache to refresh is to open the folder containing the .rdl report files, and delete the corresponding .rdl.data files. The…

    » Read more
  • Loan Amortisation in SQL Server (PMT, FV, IPMT, PPMT)

    Whilst designing a data warehouse for a banking client recently, I needed to calculate projected future loan payments (including breaking this down by interest and capital payments) for every customer throughout the life of the loan. In Excel this is a pretty simple job, as Microsoft helpfully provide a number of functions to do just that (namely PMT, FV, IPMT and PPMT). In SQL Server however we do not have the luxury of having ready made…

    » Read more