Protean

Citi Group Building

I’ve been working at Canary Wharf for 6 months now. Its a fantastic looking place, and it lends itself well to photography. I thought I’d post a couple of shots while I think up some useful database article to write about. I feel its been so long since I’ve written, that I forgotten how!


(more…)

I know as SQL Server / IT pros we tend to focus our interview efforts on our technical proficiency. Having been on many interview panels, I can say this - by the time you’ve sat through a phone screen, a SQL Server test, and finally you’ve been brought in for interview, we already know that you have the technical skills to do the job and what we’re looking for is the right fit for the team.

I may ask you some harder questions on my chosen subject, I may challenge you about some of the answers you have given for the technical test, I may interrogate you about why you left a perfectly good job. The aim here is not to prove that I’m right and you’re wrong, it’s to see how you handle the situation. Can you argue your case persuasively and confidently, can you think on the spot, how do you react when your views are challenged?

I use the dialog that we have to get some idea of how you might be to work with and what your attitudes are. Exactly what the right attitudes are can be hard to tell, but I can tell you what they aren’t! Here are some of the reason’s why I might have questions about a person’s suitability for my team. What often surprises me is that candidates fail on some of these before they’ve even met me…

(more…)

The script task in SSIS was a fantastic improvement on DTS Active X script. The reasons for this in my view are 2 fold:-

1. Firstly, by allowing the user to code in Visual Studio it is far easier to develop and debug. Because the developer can make use of intellisense & object explorer, it means even the casual programmer can read & write code.
2. Secondly, because we can use it to access other external .net assemblies, it provides functionality for logic that can’t be achieved using the standard SSIS components.

These are both reasons in themselves that make it a compelling choice when deciding how to develop your package logic.

However, I have noticed at both client sites and on the forums, that there are those (the script task junkies!) that use the script task in almost any scenario when it is just not necessary. It makes maintenance and support tricky at best and sometimes impossible…

(more…)

I don’t know about you, but one of the things that drives me to distraction when writing SSIS packages is being forced to use excel as a data source. I’d like to qualify that statement by adding that it’s the use of excel in the face of all reason, to do the job that other software would be better for. Unfortunately though, corporations and people being what they are, they are a fact of life for an ETL developer.

(more…)

Here’s the scenario… You have a RAW file which contains data from many files.

In the subsequent dataflow you need to perform a lookup against a large reference table, however you want just a subset that reflects the period contained within your RAW file.

Question 1, how do you find out the earliest date used within your RAW file data? And question 2, how do you write it to a variable so that you can use it in the subsequent data flow?

(more…)

Many of you will have heard the mantra that loops are bad, set based is good. But how do you get around them?

The fact of the matter is that there are very few circumstances where a loop is the only way to achieve your objective. A look through the SQL forums will show you many a technique to turn iterative row by row processing into single transaction set based processing. I’m refering to hierarchical query techniques (see nested set theory) and of course the subject of this post, the numbers table.

(more…)

In an earlier post, I demonstrated how to send HTML formatted mail using the script task. Another frequently requested use of the mail task is to be able to send query results within the email message body. Here’s how…

(more…)

I talked in a previous post about the possibility of using the format command and bulk insert task inside of a foreach loop in order to load all your tables using a single package… I don’t much like the method as it means you have to run each table load in series, and you’re not taking advantage of SSIS (high speed dataflow task, parallelism and eliminating staging with a single pass transformation).

(more…)

A very frequent requirement in a database, especially in an OLTP system is to be able to capture the identity and values of the last inserted record in a table so that we can use it to populate the foreign key in a child table and any other tables that require it.

It is easy enough to capture the last inserted identity if we are only dealing one record at a time using @@IDENTITY, but what happens when we need to do batch inserts? Well, the old way of doing things would mean you had the following options:

• Execute a loop (either a cursor or a WHILE statement), and for each iteration we’d write a record in each of the child tables
• You’d have to select the same data twice, once to insert the audit records and once to update the data
• You’d rely on a trigger to write to the audit table

Well that should now not be necessary as using the OUTPUT clause we can capture all the data we’ve inserted, updated or deleted in a temporary area for use later on, or say in an auditing scenario, we can output the data directly to the auditing table. More importantly we can use that data in a set based transaction instead of row based. The advantages here are obvious. 

(more…)

Some of the companies I’ve worked for have had reporting contracts with smaller 3rd party providers. Campaign or website analysis provided by them would have to be loaded to the datawarehouse

Security policy dissallowed downloading the files over FTP, because credentials are passed in clear text, and our 3rd party report providers didn’t have a sFTP facility, so in this scenario what other options are available?

(more…)