Class Library Templates Visual Studio 2008 Where are you

Having spent far too long in hindsight trying to find these templates for some work on functions and regular expressions I gave up and installed the express version of Visual Studio 2010.

FYI, using the command line to populate the default templates doesn't work as described here.

So why am I doing this, a new role where SQL and .net are the sole available languages got me thinking of how to implement data cleansing routines from perl in SQL.

The appendix in this book Inside Microsoft SQL Server 2008: T-SQL Programming gives you the VB code you need which is a great place to start to learn intermediate techniques such as user defined functions and CLR assemblies.

CLR Assemblies allow you to incorporate Regular Expressions into SQL which can be used to verify email address structures and are the basis of data cleaning.

Your first API call with Perl

I find learning more interesting when I have practical example to try, ebay have kindly provided an example of how to make an API call.  Click here for the full tutorial which lists the perl modules required.

If your using a Mac or Linux platform check out these resources first;

- Perlbew is an excellent version manager similar to RVM (Ruby Version Manager).

- Cpanminus is an excellent module to ensure the root perl libraries remain separate to the Perlbrew libraries.  A very helpful blog post describes how to install and setup cpanminus to handle the local perl installation and libraries you will setup using perlbew.

- Another blog post describing the modules require for https api calls and a very helpful test script to ensure your setup is working.

If this has kickstarted your interest, these excellent resources are a most to learn more about XML and perl packages and libraries.  If you spend some time surfing the web you will find an online resource for more chapters from Perl and XML by Erik T. Ray and Jason McIntosh.  You can also pick up a used copy on ebay for a few pounds. 

Data Modelling with differing levels of data granularity

I came across this in a discussion on Linkedin's Microsoft Business Intelligence group, the initial post asked

 "Join Fact and Dimension Tables that are at Two Different Grains

I have a fact table that is based off the source system at a order level. My dimension is at a order line detail level. Would creating a bridge table work to join the fact and dimension or should I create another order dimension table at the order level?"

Click here for a Kimball Group cheat sheet with examples suggested by Assaf Mentzer which answers this question. 

SSIS load text file name into table for ETL auditing Unicode error

The old saying still rings true, you get what you pay for, pay nothing, expect nothing, this excellent post details how to read the file name but this post will cause a unicode error because the its missing details at this part

"Be sure to change the DATA TYPE from UNICODE to STRING.  Select OK to confirm changes."  This is impossible unless you do this

Click on Show Advanced Editor

to change the data type to String (see screen shot below)

Then your data type will be string (see below)

And then you will not have unicode errors.

Integrate Perl scripts into SSIS

Perl is such a powerful open source language with many modules for handling XML requests, whois lookups and database interfaces for all major open and closed commercial databases.

Being able to determine if a script has successfully run in SSIS is very important.

Using Perls exit value command allows the value of a successful execution to be passed to the SSIS process task.

Then set the value in SSIS;

Useful sources:

SSIS & SQL how to articles

Perl how to

To pass a variable from the perl script use the print command, it will be captured as a string in the SSIS 'StandardOutputVariable.  A useful how to guide here.