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.


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, "", ""))
 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.


Microsoft Connect Bug Reports

Just posted two bug reports to Microsoft Connect

We don’t take kindly to sensitivity ’round here…
First, I perceive it as a bug, when a foreach loop fails to list variables when a project parameter is set as sensitive. When a project parameter is set as sensitive, I get this error message when setting up the foreach loop:

MsgBox Following is the failure to populate the list of variables:


Vote here: https://connect.microsoft.com/SQLServer/feedback/details/786696/foreach-from-variable-enumerator-fails-when-one-or-more-parameters-are-marked-as-sensitive

Flexibility? MEH!
The other bug is about the file connection manager.
When adding a file connection manager as a project connection manager, I want to be able to se the connection string as an expression in a given package. But none of the project parameters are present when I open the expression builder dialog box. Huh?


Vote here: https://connect.microsoft.com/SQLServer/feedback/details/787145/project-parameters-not-present-in-file-connection-manager-expression


Run out of disk you say, eh?

In the rare occasion, that when you are executing a project from the Information Services Catalogs, and the SysAdmin didn’t foresee some, unforeseeable event causing the disk to run out of space. And the execution doesn’t fail, just waits for more disk to become available, you will maybe be tempted to stop the operation through the ‘Active Operations’ pane in SSISDB.

This cannot be done, simply because, catalog.stop_operation which is the stored procedure called, also needs to write to disk. As the following screen shots will verify, you need to restart the instance.

Active OperationsIn SQL Server Management Studio (SSMS), select the Integration Services Catalogs, and right click the SSISDB. Click Active Operations.

Active OperationsWhen you select the operation, the option to Stop the operation becomes available.

Active OperationsYou will the be prompted, if you really want to Stop the operation.

And if you select Yes, you will be met by the following error.

Active OperationsSo, in order to get the operation, which is running, to stop, you need to restart the whole instance.


Microsoft Connect Suggestions

Just posted two new suggestions to the Microsoft Connect site.

First of is a suggestion, that when using a sensitive parameter, you get a warning when trying to use it in an expression. On the other hand, if you create the expression first, you don’t get the same warning when changing the sensitivity of the parameter.

Vote here: https://connect.microsoft.com/SQLServer/feedback/details/750997/changing-a-parameter-to-sensitive-should-trigger-alert-in-ssis

Secondly I miss the ability to reorder the items in the SSIS Toolbox:

Vote here: https://connect.microsoft.com/SQLServer/feedback/details/751003/reorder-the-elements-in-the-ssis-toolbox