SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


convert datatype DT_STRING to DT_DBDATE in ssis


convert datatype DT_STRING to DT_DBDATE in ssis

Author
Message
muralikrishna37
muralikrishna37
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 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
peterk1961
peterk1961
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 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
muralikrishna37
muralikrishna37
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 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
volkorna-780558
volkorna-780558
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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
peterk1961
peterk1961
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 46
What is your source and destination, ex. file or table?
Peter
volkorna-780558
volkorna-780558
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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?
peterk1961
peterk1961
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 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
volkorna-780558
volkorna-780558
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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.
muralikrishna37
muralikrishna37
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 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
peterk1961
peterk1961
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search