Welcome to my blog!

SQL Server Integration Services (SSIS), Enterprise Information Management (EIM) and general Data Movement
Parsing Binary data using Power Query formulas

Parsing Binary data using Power Query formulas

The Power Query formula language (M) contains a number of library functions that allow you to parse binary data. You can use these functions to build queries which parse custom binary file formats. This post contains two samples – a simple query which parses X & Y coordinate values from a binary field, and a more advanced query which reads PNG file headers. Parsing binary data The following query defines some binary data (Source), a record format (PointFormat), and a parsing format...

Iterating over an unknown number of pages in Power Query

Iterating over an unknown number of pages in Power Query

My previous blog post showed how to iterate over a set of web pages in Power Query using a parameterized function. The post contained two queries – the GetData function, and a query to invoke it over a set number...

Iterating over multiple pages of web data using Power Query

Iterating over multiple pages of web data using Power Query

This is one of my go-to demos for showing off the Power Query Formula Language (M). This approach works well with websites that spread data over multiple pages, and have one or more query parameters to specify which page or...

Converting a Query to a Function in Power Query

Converting a Query to a Function in Power Query

You can turn a query into a function by adding a single line before your M query: () => That is a pair of parenthesis ( () ), followed by a goes-to symbol ( => ). Any parameters for your function would...

Notepad++ language file for the Power Query formula language (“M”)

Notepad++ language file for the Power Query formula language (“M”)

Power Query doesn’t have syntax highlighting in its advanced editor (yet?). In the meantime, here is a language file for Notepad++. It’s not perfect, but I find it useful when writing M queries. It lists all of the current library...

Defining Configurable Settings for your Queries

Defining Configurable Settings for your Queries

(The information in this blog post is current as of the March 2014 release of Power Query.) The Problem A colleague sent me a copy of the Excel workbook they used for a blog post about using Power Query with Project Online (see the fantastic article: Creating burndown charts for Project using Power Pivot and Power Query). The workbook has a query which uses the Project OData API to pull down Task information to produce a “burndown” chart. The query was configured...

OData Source for SSIS

OData Source for SSIS

A new OData Source for SSIS is now available. The 2012 version is available as a standalone download, and the 2014 version is part of the SQL Server 2014 Feature Pack. OData Source for SQL Server 2012 OData Source for SQL Server 2014 The documentation for the 2012 is available in Books...

Presenting at SharePoint Conference 2014

Presenting at SharePoint Conference 2014

I’m looking forward to attending and presenting at my first SharePoint Conference next week in Las Vegas! I’ll be presenting two Power BI deep dive sessions (1): #SPC397 - Deep Dive on the Data Management Gateway in Power BI for...

Creating a Date Dimension with a Power Query Script

Creating a Date Dimension with a Power Query Script

Chris Webb has previously blogged a similar approach, but I thought I'd shared my own version of a Power Query function that generates a table of dates that could be used to create a Date Dimension. I first saw Faisal...

Generate an Empty Raw File Without Running your SSIS Package

Generate an Empty Raw File Without Running your SSIS Package

A new feature was added to the Raw File Destination in SQL Server 2012 which allows you to output an empty raw file from the editor UI. Add a Raw File Destination to your data flow, and connect...

Using Hive ODBC from SSIS

Using Hive ODBC from SSIS

Microsoft announced the general availability of Windows Azure HDInsight last week, and released an updated version of the Microsoft Hive ODBC Driver. This post describes how to use this driver to pull data from your HDInsight cluster with an SSIS...

Links from the SSIS Roadmap Session

Links from the SSIS Roadmap Session

Here are some of the resources I mentioned in the SSIS Roadmap session at the PASS Summit. SSIS Reporting Pack from Jamie Thomson   DQS Matching Transform from OH22 Data   DQS Domain Value Import Destination from OH22 Data There is also a great series of blog posts (part 1, part 2, part 3) on using these transforms on the DQS team blog. Data Feed Publishing Components ...

SSIS Content from PASS Summit 2013 on PASS TV

SSIS Content from PASS Summit 2013 on PASS TV

You can now stream sessions from the 2013 PASS Summit on-demand from the PASStv site. There's a lot of amazing content on there, including the Day 1 and Day 2 keynotes. If you're interested in SSIS (and if you're reading this, there is a good chance you are), there are two SSIS focused sessions available: SQL Server Integration Services Roadmap (Matt Masson, Wee Hyong Tok) Automate Your ETL Infrastructure with SSIS and PowerShell (Allen...

SQL Server Data Tools – Business Intelligence downloads

SQL Server Data Tools – Business Intelligence downloads

(update 2014/11/14: added link to the VS 2013 release which came out in April) Looking for the SSIS development tools? Formerly known as Business Intelligence Developer Studio (BIDS), the designer is now called SQL Server Data Tools – Business Intelligence (SSDT-BI)...

Skylanders Power View Workbook

Skylanders Power View Workbook

...

BI Power Hour at PASS Summit 2013

BI Power Hour at PASS Summit 2013

A big thank you to the PASS Summit 2013 attendees who made our BI Power Hour a great success! I have to admit I was a little worried when we lost all power 15 minutes before the session started (highly...

SELECT * From SSIS.DataFlow

SELECT * From SSIS.DataFlow

If you've been looking through the documentation for the Power BI preview, you might have noticed a section on Publishing SSIS Packages as OData Feeds. This functionality lets you create a T-SQL View over an SSIS data flow using a...

PASS Summit 2013

PASS Summit 2013

Wow, has it really been two months since my last blog post? I guess I’ve been busy! I’ve been hard at work on SSIS and some components for Power BI. I hope to blog about all of the fun projects I’ve been working on this past year, but in case I don’t get a chance, you can come hear me talk all about it at this years PASS Summit! Yes boys and girls, I’ve once again tricked the conference organizers into letting...

Filtering in Data Explorer

Filtering in Data Explorer

I was been manning the Data Discovery & Visualization and Data Warehousing booths at TechEd Europe last week, and we saw lots of excitement over Data Explorer. One of the questions I got was about how Data Explorer does its filtering, and I realized there hasn’t been much shared about this yet. It seems like the general assumption is that Data Explorer would pull all rows into Excel, and then perform its filtering in memory (as that’s how you’d build...

Server Execution Parameters with DTEXEC

Server Execution Parameters with DTEXEC

The SSIS team blogged about executing packages deployed to the SSIS Catalog using DTEXEC a while ago. The post mentions the $ServerOption::SYNCHRONIZED parameter as a way to control whether the execution is synchronous or asynchronous, but there are some other...