Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

convert datatype DT_STRING to DT_DBDATE in ssis Expand / Collapse
Author
Message
Posted Monday, March 16, 2009 1:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 16, 2009 6:05 AM
Points: 39, Visits: 99
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
Post #676881
Posted Monday, March 16, 2009 3:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 28, 2009 9:53 AM
Points: 23, Visits: 46
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
Post #676959
Posted Wednesday, March 18, 2009 10:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 16, 2009 6:05 AM
Points: 39, Visits: 99
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
Post #678669
Posted Wednesday, March 18, 2009 12:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 5, 2010 7:18 AM
Points: 8, Visits: 56
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
Post #678788
Posted Wednesday, March 18, 2009 1:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 28, 2009 9:53 AM
Points: 23, Visits: 46
What is your source and destination, ex. file or table?
Peter
Post #678858
Posted Wednesday, March 18, 2009 2:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 5, 2010 7:18 AM
Points: 8, Visits: 56
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?
Post #678891
Posted Wednesday, March 18, 2009 3:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 28, 2009 9:53 AM
Points: 23, Visits: 46
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
Post #678937
Posted Wednesday, March 18, 2009 3:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 5, 2010 7:18 AM
Points: 8, Visits: 56
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.
Post #678954
Posted Thursday, March 19, 2009 12:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 16, 2009 6:05 AM
Points: 39, Visits: 99
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

Post #679763
Posted Thursday, March 19, 2009 1:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 28, 2009 9:53 AM
Points: 23, Visits: 46
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
Post #679834
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse