convert datatype DT_STRING to DT

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » convert datatype DT_STRING to DT_DBDATE in...

13 posts, Page 1 of 2

1 2 »»

convert datatype DT_STRING to DT_DBDATE in ssis

Rate Topic

Display Mode

Topic Options

Author

Message

muralikrishna37

Posted Monday, March 16, 2009 1:41 PM

SSC 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

peterk1961

Posted Monday, March 16, 2009 3:01 PM

Grasshopper

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

muralikrishna37

Posted Wednesday, March 18, 2009 10:05 AM

SSC 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

volkorna-780558

Posted Wednesday, March 18, 2009 12:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 05, 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

peterk1961

Posted Wednesday, March 18, 2009 1:39 PM

Grasshopper

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

volkorna-780558

Posted Wednesday, March 18, 2009 2:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 05, 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

peterk1961

Posted Wednesday, March 18, 2009 3:08 PM

Grasshopper

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

volkorna-780558

Posted Wednesday, March 18, 2009 3:38 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 05, 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

muralikrishna37

Posted Thursday, March 19, 2009 12:15 PM

SSC 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

peterk1961

Posted Thursday, March 19, 2009 1:19 PM

Grasshopper

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 »

13 posts, Page 1 of 2

1 2 »»

Permissions