March 16, 2009 at 1:41 pm
Hi ssis gurus,
i have a source file it contains some colums and in this file one column is string(20061026) format with date and i have to convert this column into date format(26-10-2006).for that i use derived column its working fine but another problem is in that same column some rows are contain null values.when i convert the values its navigate perform cast error.what is the solution to load data with null values.
ex:
source file(String datatype)
----------
1.20061026
2.20061115
3.20070712
4 0
5.20071204
6.20070922
7. 0
8.20080124
my target is
1.2006-10-26(Date Datatype)
2.2006-11-15
3.2007-07-12
4.Null
5.2007-12-04
6.2007-09-22
7.Null
8.2008-01-24
any body help appriciated
thanks
murali
March 16, 2009 at 3:01 pm
I am assuming you are using expression column to change the data to datetime format. As part of that, check for null or valid date and move a default value if it is null or invalid date.
Peter Kennedy
March 18, 2009 at 10:05 am
Hi peter thanks for u suggestion.
i did what u suggested and i implement expression in derived column transformation.
the expression is:
(ISNULL([date string])) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([date string], 7, 4) + "-" SUBSTRING([date string], 1, 2) + "-" + SUBSTRING([date string], 4, 2))
and my client asked me load data in target like this
1.2006-10-26(Date Datatype)
2.2006-11-15
3.2007-07-12
4.0
5.2007-12-04
6.2007-09-22
7.0
8.2008-01-24
what is the expression i have to write in derived column trasnformation
thanks
murali
March 18, 2009 at 12:27 pm
I'm using Script Component for converting from "yyyyMMdd" string (20090115) to Date type. It is more accurate and is independant from date settings.
Script example:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim provider As Globalization.CultureInfo = Globalization.CultureInfo.InvariantCulture
Dim format As String = "yyyyMMdd"
Dim errmessage As String = "place here error message/code"
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Try
If Row.InputDateString_IsNull = True Then
Row.OutputDateInDateType_IsNull = True
Exit Try
Else
Row.OutputDateInDateType = Date.ParseExact(Row.InputDateString, format, provider)
End If
Catch ex As Exception
Row.OutputDateInDateType_IsNull = True
Row.errmsg = Row.errmsg + errmessage
End Try
End Sub
End Class
March 18, 2009 at 1:39 pm
What is your source and destination, ex. file or table?
Peter
March 18, 2009 at 2:08 pm
Using Derived column and Substring in 3 parts makes package dependant from date format in regional settings. Have found a problem using this method after deploying on different machine, that in some cases the date will be converted incorrectly by derived column substrings + regional settings:
'20090105' should be '2009-01-05', but if regional setting are different, it can be converted in '2009-05-01'.
And script component removes this dependancy.
Is there any difference what is source and destination?
March 18, 2009 at 3:08 pm
If you look at the code attempted by Murali, he is trying to check for null and his description mentioned source file so depending on a flat file or table source how you are going to check for the value is going to change.
I am not sure if you are a script only programmer like one my colleague who will always insist on scripts even if multiple options are available.
I am not an expert to comment if script or derived column function is the best solution but for this purpose derived column will be lot simple and easy to code. That is just my opinion.
Peter
March 18, 2009 at 3:38 pm
Actually, I use scripts only where it is nessesary and standard option is not usefull.
Moving the package with string to date type conversion in Derived Column
from one machine to another may lead to incorrect data load. And I want to warn about it.
March 19, 2009 at 12:15 pm
Hi peter,
Source file is flatfile.
and i implement following expression in derived column.
([datestring ] == "0") ? (DT_DBTIMESTAMP)0 : (DT_DBTIMESTAMP)(SUBSTRING([datestring ],1,4) + "-" + SUBSTRING([datestring ],5,2) + "-" + SUBSTRING([datestring ],7,2))
its working fine but at destination in "0" place it display "12/30/1899 12:00:00" but i want display "0" only
Thanks
murali
March 19, 2009 at 1:19 pm
If you want just the date then you should use DT_DBDATE not DT_DBTIMESTAMP and also don't forget to change the data type.
I am assuming your output is a table. If so, how is the field defined. That will make a difference as to how the value is displayed even though you might just move 2009-10-23 for ex. If you want '0' to be stored in the table, it is not going to work if the field is defined as a date field. If you can't change the field definition to string on the output table, you may want to consider moving a default date.
Peter
March 19, 2009 at 1:38 pm
I guess, I didn't read your initial post correctly. If you want null when the date value is '0' use NULL(DT_DBDATE).
Peter
April 20, 2009 at 6:24 am
Hi,
Since the expression is cast to DB_TIMESTAMP, would it not fail for invalid date string?
eg. If the input string is 20090230 (which is an invalid date), how do you handle this in the expression.
April 20, 2009 at 8:03 am
This example shows how to check for valid date and replace default date for invalid date.
(DT_BOOL)(DT_DATE)IssueDate_C ? IssueDate_C : "01/01/1900"
June 12, 2013 at 12:06 pm
SELECT CONVERT(VARCHAR(10),GETDATE(),121);
Output: 2013-06-12
Example:-
SELECT CONVERT(VARCHAR(10),ContractEndDate,121)
From TestDataTable_Archive;
You can Play with the different format values like 111 etc.
SELECT CONVERT(VARCHAR(10),GETDATE(),111);
Output: 2013/09/30
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply