Did someone restart the server over night?

Sometimes, when you are running lenghty integration jobs over night, you get in the office the next morning, and may be surprised that nothing has completed. And actually nothing is running anymore.

Today was such a Day for me. Last night I started the execution of a package via remote desktop and DTSExec Util. This morning, my job was gone from the session, and no where to be found in the Task Manager. So, did someone restart the server over night? (could even be a stupid auto Windows update setting rebooting the machine)

I found this piece of Powershell to tell, if the Event Log Service was stopped:

PS C:\get-eventlog system | where-object {$_.eventid -eq 6006} | select -first 10

At least it tells you quickly, if the suspicion has something to it.

Posted in Programming | Tagged , | Leave a comment

Run your cubes in Warp Speed – Really Really Fast!

So, finally Microsoft came up with a new feature for the MOLAP part of Analysis Services. The community has been hungering for updates ever since the new kid Tabular came around.

Now it apears that the SQL Server team has unlocked some underlying functionality in the Windows OS, to enable the MOLAP cubes to operate in Warp Speed.

Run the latest CU (get it here) and check out the newly added check box in the properties.

WarpSpeed for MOLAP Cubes

WarpSpeed for MOLAP Cubes

 

Posted in Programming | Tagged , , | 1 Comment

How to Process Multiple Dimensions via BIDS and SSMS

This is one of those tips I need to write down, before I forget…
Sometimes Analysis Services acts up and you need to do a process full on all the dimensions in the database. When that happens I usually turned to Visual Studio Business Intelligence Development Studio [BIDS] or SQL Server Data Tools [SSDT], which allows you to select more than one dimension at a time, and fire up the processing. See figures below:

Via BIDS

Open a connection to a “live” database

Open Connection

Open Connection

Choose which server/database to connect to:

Choose Server/Database

Choose Server/Database

Select all dimensions and right-click:

Select dimensions

Select dimensions and right-click

Finally, choose processing mode:

Processing Mode

Processing Mode

Via SSMS

Until today, I didn’t know you could actually do the same action through SQL Server Management Studio [SSMS]. You do as following:

Connect to the server:

Connect to Server

Connect to Server

Expand till you get to the Dimensions folder:

Find the Dimensions folder

Find the Dimensions folder

Hit F7 to open the Object Explorer Details:

Select All and Right-click

Select All and Right-click

Select Processing Mode:

Select Processing Mode

Select Processing Mode

 

Posted in Programming | Tagged , , | 1 Comment

Buy One, Get One Free

Packt Publishing releases the 2000th title. During this offer Packt is giving its readers a chance to dive into their comprehensive catalog and Buy One, Get One Free across their entire range of eBooks until 26th-Mar-2014.

Packt

Posted in Mixed | Tagged , | Leave a comment

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], though 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.

m4s0n501
Posted in Programming | Tagged , , | Leave a comment