Sunday, April 20, 2014
   
Text Size
Login

SSIS Script: convert UPPERCASE to Mixed-Case using TitleCase

Applies to:
  • Microsoft Business Intelligence Development Studio (BIDS) Visual Studio 2008
  • Microsoft Windows 7
  • Microsoft .NET Framework 3.5
  • Microsoft Visual C# 2008

What?
So like lots of people on the net, I've been given a datasource with names and addresses all in UPPERCASE. Initially, no one seemed bothered but now the request came through asking if this could be restored to a normal case, a mixture of mostly lowercase and some UPPERCASE.

Why?
I'm writing this article because other solutions on the net were either incomplete, did not work for me or simply did not match the requirements (do they ever?).
  1. The script must be able to be copied into other SSIS packages.
  2. Need the option to merely tick input column names rather than hardcode these.
  3. "It " should be an acronym for IT, so "IT Department" and "Director of IT".
  4. "O'REILLY" should become "O'Reilly" and not "O'reilly".
  5. "MCDONALDS" should become "McDonalds" but do not apply for "MacDonalds" because of names like "MACK" and "MACHURA".
  6. " and ", "la", "le", " van ", " de ", " of " and similar should remain lowercase.

How?

Trim in T-SQL and SSIS

What?
Some methods of removing trailing spaces, tabs, carriage returns and line feeds (new lines).

How?
First in Transact-SQL:

SSIS Skip Blank Rows in Flat File Source

What?
This is a quick article to remind me on how to skip blank rows when using a Flat file as a data source. I would receive another Microsoft error as clear as mud:

  1.  Error: The conditional operation failed. 
  2.   
  3.  Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component  
  4.  "MyDerivedColumns" (4228)" failed because error code 0xC0049063 occurred, and the error 
  5.  row disposition on "output column "DC_MyDate" (7349)" specifies failure on error. An  
  6.  error occurred on the specified object of the specified component.  There may be error  
  7.  messages posted before this with more information about the failure. 

Why?
So the solution must be obvious to you by now. At least that's what Microsoft people think to themselves every time they see the error they programmed in.

Apparently this problem also happens when you have a Data File of varying column numbers. My solution below will also fix this.

How?

SSIS Skip Rows in Excel Source file

What?
A quick article on how to skip rows or even specify a range to extract from the Excel file when using as the DataSource.

Why?
If you are simply using a text file as your data source, then the options in the connection manager will let you skip rows and specify column datatypes. You don't get that with Excel but you can still control the data range.

How?
My example is that I have an excel sheet where the first 10 rows are descriptions to the column, and then I have 38 columns ("A" to "AL"):

SSIS Convert a string into a date

What?
Please note: the following article is not a solution but a page of various methods and date conversions which I try and use depending on the situation.

This is a quick article on how I got time conversion working in an SSIS package which read from a text file. Note that the example below converts a string in European Date Format (ie. "ddmmyyyy" to "dd/mm/yyyy"). Also, my data flow imports two dates one which was imported as a string (DT_WSTR) and another as a integer (DT_R8).

Why?
It took me a long time to figure this and it was only by trawling through columns that someone mentioned that maybe all the data in that column is not consistent? This made sense as I need to parse the string to extrapolate the date, month and year.

How?

Latest Posts

  • 301 Redirect using htaccess file

    • Tue 15-Apr-14
      Further suggestion:
      RewriteRule ^(.*/)?assets/s 2dmain.html\?/( .*/)? $2 [R=301,L]
      Webmaster  
    • Thu 10-Apr-14
      Playing with some RegEx testers
      RewriteRule ^(.*\?/)?(?:$|( .+?)(?:(\.[^.]$ )|$)) $2 [R=301,L] ...
      Webmaster  
    • Wed 09-Apr-14
      I tried to redirect links from my old site to my new site, based on what I read in this thread. The old ...
      pelle
  • Joes Revolver Map (JRM)

    • Sun 20-Apr-14
      Hello, is it possible to use this module also in joomla 3.2? Thanks Manfred
      Manfred V.
  • K2 Items disappear

    • Thu 03-Apr-14
      The fix works great, but the problem is occurring a couple of times a day. Any idea how to fix ...
      Larry C.