Trim in T-SQL and SSIS
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
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.
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?
MS Excel - Sort pivottable column headings by date
This is a quick note to myself so that I never use parentheses in the column headings again. Basically I have a pivot table in Microsoft Excel 2010 with the projects down the left (in the first column) and the days of the week along the top.
Why?
The excel report would hit a bug where it couldn't work out that 10 (Wednesday) happened after 8 (Monday).
How?
See the following screenshot and note the dates for Monday, Tuesday, Wednesday, and Thursday:
SSIS Skip Rows in Excel Source file
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
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?