SSIS date conversion cheatsheet

I seriously can’t remember the number of times that I have had to lookup date conversion logic in SSIS. Decided to create a cheatsheet for it.

Date in Flat FileSQL Data TypeData Conversion TransformationDerived Column Transformation
yyyymmdddateDT_DBDATE (FastParse = True)
yyyy-mm-dddateDT_DBDATE (optional)
dd/mm/yyyydateDT_DBDATE (optional)
mm/dd/yyyydate(DT_DBDATE)(SUBSTRING([Col],7,4) + "-" + SUBSTRING([Col],1,2) + "-" + SUBSTRING([Col],4,2))
yyyy-mm-dd hh:mm:ssdatetimeDT_DBTIMESTAMP (optional)
yyyy-mm-dd hh:mm:ss.nnnnnnndatetime2DT_DBTIMESTAMP2 (scale = count(n)) (optional)

yyyymmdd

Most solutions on stackoverflow would suggest that you use a derived column transformation to insert dashes to form a yyyy-mm-dd string and then cast it to DT_DBDATE. A much simpler way is to turn on a custom property called FastParse. (Thanks to my co-worker Lynn for passing on this tip)

FastParse can be found in Show Advanced Editor -> Input and Output Properties -> Data Conversion Output -> Output Columns

yyyy-mm-dd

This is the simplest format to handle. A data conversion transformation will do. It can be implicitly loaded into a date column as is.

dd/mm/yyyy

This is locale-specific so a data conversion transformation can easily handle it it can be implicitly loaded given that we are in Australia.

mm/dd/yyyy

I have to say this is the most hated date format in the database world (this part of the world at least). Now that we are in Australia and it is a US date format, it cannot be converted directly using a data conversion transformation. Instead we need to rely on a derived column transformation to pull apart the date components and reconstruct into the yyyy-mm-dd format.

EDIT: Actually I later found that SSIS can handle both the dd/mm/yyyy and mm/dd/yyyy format as long as the days are larger than 12, even for dates with mixed format within the same column. Will write about this in a future post.

yyyy-mm-dd hh:mm:ss

This is a perfect date time format that can be converted using a data conversion transformation. Just need to remember to use the DT_DBTIMESTAMP data type. Or, simply load into a datetime column as is.

yyyy-mm-dd hh:mm:ss.nnnnnnn

This is the format for a SQL datetime2 data type. In order to do a direct data conversion, the scale needs to match the digits in the fractional seconds. Basically, no fractional seconds = no scale, 1 digit  = scale of 1, 2 digits = scale of 2, and so on up to 7. Again, simply load into a datetime2 column as is.

How to migrate a WordPress blog to Windows Azure

Just recently I have migrated this WordPress blog to Windows Azure. I am actually writing this post on the new platform. I have learnt a few lessons along the way and it is always a good idea to document them while everything is still fresh in my head.

Like doing anything for the first time, I started by searching for How To articles on the web. One frustrating thing I found was that there were a lot of out of date info around and after following a few step by step guides to no success, I eventually gave up and decided to go on my own.

After failing a few attempts, I stopped following other people’s instructions and started thinking. What is the most important content I care about? The answer was obvious, the blog posts. Do I care about themes, plugins and comments, etc? Yes but they are not critical. At a bare minimum, I would be content if only the blog posts were migrated across. In that case, I just needed to backup and restore the WordPress database then. Once I realised what I needed to achieve, the migration path became much clearer. Well that was the initial thinking but nonetheless, the path was more or less clear. What I found later was that a straight backup and restore would not have worked as the new environment was not empty, there was already data in the database.

Now I will cut to the chase and describe what I did step by step.

1. Install plugin to work with WordPress’s MySQL database

The plugin I used was called Adminer (formerly phpMinAdmin). I simply went to Plugins -> Add New -> Searched for Adminer -> Install -> Activate.

2. Decide on tables on export

Goto Tools -> Adminer -> Start Adminer inside

Once in Adminer, I was presented with 11 tables, the trick was to work out which tables I needed to export.

wp_options
I didn’t need to migrate this table because the new WordPress installation on Windows Azure contained all my options already and I could go back and match the settings manually anyway.

wp_users, wp_usermeta
Since this is my personal blog, I am the only user I care about, so no need to migrate this table either because I was already a user in the new environment. The ID of my users in both the old and the new databases were the same and this could be very handy during migrations.

wp_posts, wp_postmeta
These tables looked important, I definitely wanted to migrate them.

wp_terms, wp_term_relationships, wp_term_taxonomy
Yep, I also wanted to retain the categories.

wp_comments, wp_commentmeta
I decided not to bother with comments.

wp_links
The table was empty (later found out it was deprecated).

3. Export

Clicked on Export from the panel on the left and selected the following options

Output: save
Format: SQL
Database: <BLANK>
Tables: <BLANK>
Data: INSERT

Before exporting, made sure I ticked wp_postmeta, wp_posts, wp_terms, wp_term_relationships and wp_term_taxonomy tables.

4. Edit the exported SQL file

Once I had exported the tables, I started to inspect the SQL file. There were links to the old site littered across the file. I simply did a search and replace. (For example, from www.oldsite.com/blog to www.newsite.com)

5. Import into the new database

I then went into Adminer on the Windows Azure side and imported the SQL file. No errors. All good.

6. Check

Opened up the new site and it was a relief seeing all the old blog posts.

7. Publish a new blog post

If you are reading this, that means I have successfully migrated the blog from my old host to Azure. At least the main part.

8. What’s next?

There is still a bit of cleaning up to do as all those images and files from the old site also need to be moved to the new site. But that’s for another day. Stay tuned.

No information may be an important piece of information

A few years back, I have learnt a very valuable lesson in a data migration project for a health care organisation.
 
There was a table called patient and within the patient table there was a field called allergies.
 
The allergies field contains free text like “allergic to peanuts”, etc. As part of the cleansing rule, we’ve decided to filter out anything that was not an allergy. For example, “NIL KNOWN” will not make it to the destination system.

That turned out to be a mistake because the words “NIL KNOWN” might seem unimportant, but it contained a hidden logic that’s valuable to the business.

For example, the allergies field may be used to store answers from a patient survey form. Therefore there is an obvious  difference between NOT answering a question and answering NO to a question.

Let’s now look at the possible consequences of the data migration process:

1. Migrating “NIL KNOWN”
The nurse can safely carry out care activities without delay.

2. Not migrating “NIL KNOWN”
The nurse may take extra time to confirm that the patient has no food, medical or physical allergies before carrying out care activities.