- Microsoft Business Intelligence Development Studio (BIDS) Visual Studio 2008
- Microsoft Windows 7
- Microsoft .NET Framework 3.5
- Microsoft Visual C# 2008
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.
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?).
- The script must be able to be copied into other SSIS packages.
- Need the option to merely tick input column names rather than hardcode these.
- "It " should be an acronym for IT, so "IT Department" and "Director of IT".
- "O'REILLY" should become "O'Reilly" and not "O'reilly".
- "MCDONALDS" should become "McDonalds" but do not apply for "MacDonalds" because of names like "MACK" and "MACHURA".
- " and ", "la", "le", " van ", " de ", " of " and similar should remain lowercase.
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:
- Error: The conditional operation failed.
- Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component
- "MyDerivedColumns" (4228)" failed because error code 0xC0049063 occurred, and the error
- row disposition on "output column "DC_MyDate" (7349)" specifies failure on error. An
- error occurred on the specified object of the specified component. There may be error
- messages posted before this with more information about the failure.
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.
A quick article on how to skip rows or even specify a range to extract from the Excel file when using as the DataSource.
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.
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"):
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).
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.