Power BI Visualization – Hands on

I was very happy to get the news, that I have been selected to speak at Campus Days 2014 in Copenhagen, Denmark. The session will mainly be focusing on how to integrate data into Power Map and Power View, what are the new features and how do I present that to the business… The session level is 200, which means that some prior knowledge about Power Pivot is to be expected, but if your new to it, you would probably be able to tag along.

See abstract here: http://channel9.msdn.com/Events/Microsoft-Campus-Days/Microsoft-Campus-Days-2014/SQL2008

Not sure which day the presentation will be, as the schedule is not yet public. Will update when public.

Loading

Filtering SQL Agent Jobs

I suspect that any developer who has been near a SQL Server for more than just a short while, you will be able to recognize a list of SQL Agent jobs that can no longer can be seen in its full extent without having to scroll.
A tip to make it easier for yourself is that you can filter the list.

Just right-click ‘Jobs’ and select Filter->Filter Settings and you’re off.

Looong SQL Agent Job List
Looong SQL Agent Job List

 Then punch in the filtering you’d like to have

SQL Agent Jobs Filter
SQL Agent Jobs Filter

And voila, your list of jobs are filtere to show only the ones you want:

SQL Agent Jobs Filtered
SQL Agent Jobs Filtered

Loading

Microsoft Visual Studio is unable to load this document

This issue has actually been on my TODO list for quite some time. Now I had to go back to an old SSIS package and do some changes, I got reminded of how annoying this bug is.

The scenario is SQL Server Integration Services 2008 R2 combined with loading an Excel file. This should in anyones mind be the perfect match (if any such exists). I mean, both Microsoft products, one tailored to accommodate for the other – what could go wrong?

I turns out, that Business Intelligence Development Studio (BIDS) is unable to open the file, when you have configured a file in a remote localtion. Or so at least is my theory. Below is what fixes the flaw:

Opening the package fails:

SSIS Error Page

View Code to fix error:

SSIS View Code
SSIS View Code

Remove the inner text of the <DTS:PropertyDTS:Name=“ConnectionString”></DTS:Property> in question:

SSIS Code Behind
SSIS Code Behind

This Works, until the next time you save the file. :/

Filed a Microsoft Connect Item: https://connect.microsoft.com/SQLServer/feedback/details/894728/visual-studio-bids-unable-to-load-file-dtsx

Loading

SSIS Script Task and Proxy Auto Configuration (PAC) Script

Actually this post could have been divided into several topics, but currently time does not permit me to write that often. So this will be the whole shabang at once.

We needed to import data from an external source, using Java Script Object Notation [JSON], through Microsoft SQL Server Integration Services [SSIS]. Using a Script Task to set up the connection , retrieve the HttpWebRequest and passing the data along to SQL Server was, in broad terms, the way to solve the task. Now, along the way a Proxy Auto Configuration [PAC] Script was introduced, which complicated things a bit.

A PAC is a script that let’s for example Internet Explorer, or any other agent, to choose the appropriate proxy to use, for the current context. An example of a PAC script could look like this:

function FindProxyForURL(url, host) {
 // our local URLs from the domains below example.com don't need a proxy:
 if (shExpMatch(host, "*.example.com"))
 {
 return "DIRECT";
 }

 // URLs within this network are accessed through
 // port 8080 on fastproxy.example.com:
 if (isInNet(host, "10.0.0.0", "255.255.248.0"))
 {
 return "PROXY fastproxy.example.com:8080";
 }

 // All other requests go through port 8080 of proxy.example.com.
 // should that fail to respond, go directly to the WWW:
 return "PROXY proxy.example.com:8080; DIRECT";
}

The quick reader, will already have spotted, that such a PAC Script can indeed return several proxy URLs, separated by semicolon ‘;’. Ours did, in our case. I was ignorantly expecting a single URL to be returned, and spent some ½-hour trying to fix things here and there, without actually getting to the cause. Thanks MessageBox.Show(…) 🙂

So, how to integrate this into the Script Task of SSIS?

A piece of code on Code-Project managed to get me up and running in a jiff, so please have a look at: http://www.codeproject.com/Articles/12168/Using-PAC-files-proxy

The article explains how to set a proxy using PAC files. The solution presented in the article uses the WinHttp.dll for obtaining a proxy URL.
When this code is embedded into the Script Task, it’s a stroll in the park to get your data flowing.

HttpWebRequest request = null;
HttpWebResponse response = null;

string listOfServers = GetProxyForUrlUsingPac(Variables.SourceURL, "http://www.yourdomain.com/pac/pacscriptname.pac");
WebProxy p = new WebProxy("http://" + listOfServers.Split(new char[] { ';' })[0]);
request = (HttpWebRequest)WebRequest.Create(Variables.SourceURL);
request.Proxy = p;
response = (HttpWebResponse)request.GetResponse();

Stream s = response.GetResponseStream();
StreamReader sr = new StreamReader(s);
string json = sr.ReadToEnd();
//DO Whatever you want with the JSON stream

Now, this has every mark of a quick ‘n dirty, so you might want to make it a lot more robust. The list of proxy servers I receive could be put to a better use, rather tahn just picking the first one… But I hope this helps shed some light onto how to connect SSIS Script Task through a proxy, using a PAC Script.

Loading