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.
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.
Then punch in the filtering you’d like to have
And voila, your list of jobs are filtere to show only the ones you want:
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:
View Code to fix error:
Remove the inner text of the <DTS:PropertyDTS:Name=“ConnectionString”></DTS:Property> in question:
This Works, until the next time you save the file. :/
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?
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 = newWebProxy("http://" + listOfServers.Split(newchar[] { ';' })[0]);
request = (HttpWebRequest)WebRequest.Create(Variables.SourceURL);
request.Proxy = p;
response = (HttpWebResponse)request.GetResponse();
Stream s = response.GetResponseStream();
StreamReader sr = newStreamReader(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.