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.

Posted in Programming | Tagged , , | Leave a comment

How to generate the alphabet from ASCII codes in T-SQL

Just now I had the requirement to create a loop in SSIS, and have that loop the alphabet.
In order to facilitate that, I generated a T-SQL statement, that provides the alphabet including the Danish Æ, Ø and Å. If you don’t need these, you can leave out the latter part of the last where clause (197, 198, 216).

   1:  SELECT CHAR(number) FROM master..spt_values
   2:   WHERE
   3:      Type = 'P'
   4:      AND
   5:      ( number between 65 and 90
   6:          OR
   7:        number IN (197, 198, 216)
   8:      )

Link to an ASCII table with possible values ranging from 0 to 255.

Posted in Programming | Tagged , , | Leave a comment

Subtle differences in Teradata and SQL Server, Part 2

p5rn7vb

Implementing Business logic in views is something seen quite often. Actually a view in my experience often contains some sort of data enrichment, -enhancement or -extension. So I am surely not the first one to come across the thought of adding some nifty tricks to a view. Well, tonight it seems I got too creative.

Imagine you have a table T1 with a column C1 that contains the definition of a certain type. And then we have another table T2 with a column C2 that cotains values of thses types. Now, since human interaction has been involved in populating the values in T2, there is a good change the values are missspelled, renamed or otherwise differ from the original true value. In my case there was a guaranteed match on the first six (6) characters, the rest could differ. So, I though I’d join the two tables on the columns, using only the first six characters as join condition.

The Query:

SELECT * FROM T1 JOIN T2 ON SUBSTRING(T1.C1 from 1, 6) = SUBSTRING(T2.C2 from 1, 20)

Works pretty well, until you try to create a view containing that type of join. Then you’ll get a syntax error.
Apparently the Teradata engine is parsing the statement in different ways depending on the context of use.

Needles to say, this Works in SQL Server, and is documented as well (see last note): http://technet.microsoft.com/en-us/library/aa213233(v=SQL.80).aspx

Posted in Programming | Tagged , | Leave a comment

Why it takes a DIY-guy a week to replace a faucet

This is from back in 2011, but still valid I think

For just about any DIY-guy comes the time where the faucet has held back its last drop, and must be replaced. One might initially think that this would be a relatively easy task. Of the possible combinations no outcome would be disastrous, but there could of course have been reversed hot and cold Water – or no Water at all .

All this was well thought through when I took off to Bauhaus to buy new faucet . One of my final questions were “do I have everything I need? ” to which the expert replied ” Yeah, it should be square .” Just let me reveal now, that it was far from it.

When I first tried to install the pipes without mounting the battery itself , I discovered that the supplied thread did not fit on the extension tube . Hmpf! Shop was closed, so I had to re- install the old faucet .

Status Day 1 : no change

Back to Bauhaus where an employee cocksure announced that “it can only be those that fit ” so I rolled good-natured Home, finally to install the new faucet . Sure enough, they fit, the new gizmos, amazing! But it was only a partial goal. The mixer proved to be for the kitchen, so the piece under the table, filled more space than there was room for. In connection with this discovery, which of course meant the dismantling of the existing faucet was one of the hoses that connected pipe with faucet leaking due to damaged packaging.

Status Day 2 : still the old faucet mounted, now just only with hot water which, incidentally, was the dripping .

On the third day I unfortunately has to spend 3.5 hours to a fantastic important extra – extraordinary general meeting (important neighborhood stuff), so there was no progress in the project.

Status Day 3 : same same

The first mixer was chosen partly by design and partly by long-term use in mind. Now we had to choose again, and the priority now became price and feature the highest. We would now just need a mixer that could do the job for a short period . Somewhere on the horizon there is a new bathroom waiting, if it is while we live here is not known . Anyway, it was Silvan who was able to deliver a faucet that just met price + functionality, but they were so not able to deliver that much more. It was too much for the dear service representative when I explained how it was installed . When the original tube from balofixen was extended by a snake, it was too confusing for the employee. ” I need a coupling with external thread ” I explained, then Silvan boy quite upset proclaimed that ” that’s a strange system you have. I’ve never experienced it before.” Without seeming too defeatist and indulgent, I tried to point to one of the many variants that hung on the wall, and it did indeed finally get the his bulb glow a little. Now I had the feeling of being closer to the target than before. And the Silvan employee also seemed as if he had said the right things. Tugging in the kids and other domestic obligations, meant no more progress on the project that day.

Status Day 4 : same same, now just with the proper fittings, and the right faucet .

Friday I was to have the kids on my own, but I would just try to mount the bugger while they were in the bathtub. It all went fine, right up until the cold connection should be installed . The tube on the new faucet was of inscrutable reasons, two inches shorter than the original. So pipe was missing. # % } { % } ^ ^ # ^ { % | $ | | % {

The old faucet I had not dealt with very elegantly, so I could not mount it again. I therefore had to mount the warm support for the new faucet, which was now sitting in the sink. The cold could not connect, so I mounted it on the discarded faucet and put it under the sink. You should just not turn on the old faucet, the it would pour out on the floor.

Status Day 5 : new faucet, but only with hot water.

Saturday morning I took off to Silvan to buy a longer pipe to connect the cold end . With Rosa in the Triobike  we were quickly on the road. There was good tailwind and therefore high speed. It was fatal in an S-bend at the café Ae. Here a wheel suddenly lost contact with the pavement, and I lost control of the bike. I tried to ease the impact with my leg, which was perhaps silly, but maybe the reason why nothing happened to Rosa. Luckily nothing happened to her, she looked a little glum for me when I rocked the bike back on all three wheels. But soon won her good mood again. Now we were almos there, so we hurried in Silvan bought the hose and limped home bound, very slowly.

ER couldn’t tell me anything, because of the swelling in the knee. Now, hit hard on mobility, lying with wrenches and other tools under a sink was not exactly top of the to-do list.

Status Day 6: all purchases made, now we just wait for mobility. So close to the target now :)

Sunday I could half sitting, half lying attach the hose so we could get cold water. Bingo! It worked! And you don’t notice the slight trickle when you are with your leg up high, here on the sofa in the living room.

Status Day 7 : Alles klar Herr Kommisar , well almost…

Posted in Personal | Tagged , | Leave a comment