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 String DataType to Date datatype in DTS Expand / Collapse
Author
Message
Posted Friday, May 1, 2009 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:11 PM
Points: 7, Visits: 55
Hi,
I need help for conversion of datatypes in DTS pack. I am using a flat file which has date field as string. I am loading this file to database which contains columns with 'datetime' datatype. Any help will be appreciated!!!
Post #708515
Posted Friday, May 1, 2009 8:29 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
What is the format of the dates in the source file? Please post an example.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #708846
Posted Sunday, May 3, 2009 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 4, 2009 6:45 PM
Points: 1, Visits: 1
It's a csv file.
"123456432",0,0,0,0,"3214","21/04/2009"
The second value in the file is a date field. This file is automatically generated from prog with lots of other information including dates. Some fields have dates and some don't. Null string date type field is giving problem while loading data to datetime column
Post #709055
Posted Sunday, May 3, 2009 5:56 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
When you say the second value, you mean the one which is zero in your sample data? Zero is different from null and needs to be handled differently.

If the value is zero, do you want to import it as null?

Or did you mean 'seventh' rather than 'second'?

Phil



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #709129
Posted Monday, May 4, 2009 7:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:11 PM
Points: 7, Visits: 55
Yes it is 0 in sample data. Sometimes it is an empty field e.g 123456,,0,12/04/09etc
Post #709363
Posted Monday, May 4, 2009 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:11 PM
Points: 7, Visits: 55
I just confirmed thius file is automatically generated from prog and will proper date field or nothing which is blank field
Post #709372
Posted Monday, May 4, 2009 11:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:11 PM
Points: 7, Visits: 55
I am getting following error:
The number of failing rows exceeds the maximum specified:
Transformcopy'DTSTransformation_95'conversion error:Conversion invalid for datatypes on column pair1(source column'Col095'(DBTYPE_STR), destination column'createDate'(DBTYPE_DBTIMESTAMP)).
Post #709567
Posted Monday, May 4, 2009 2:01 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:06 PM
Points: 1,478, Visits: 1,025
I assume your already using a transform task to cast it into a date.
If not add a derived column transform between your source and your destination.
for Col095 Replace Col095, and put this in your expression.
ISNULL(Col095) || TRIM(Col095) == "" ? NULL(DT_DATE) : (DT_DATE)Col095

If Col095 is null, OR "" then NULL(DT_Date) else Cast(Col095) As Date.
If your column does not allow nulls, then replace NULL(DT_Date) with (DT_Date)'01/01/1900' or whatever default date you want
Post #709688
Posted Monday, May 4, 2009 2:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:11 PM
Points: 7, Visits: 55
Thanks for your reply. But i am new to this DTS programming. Also I ahve little knowledge about VB script

' Copy each source column to the destination column
Function Main()


Main = DTSTransformStat_OK

End Function

Function TransformFailureMain()

DTSDestination("CreateDate") = CDate(DTSSource("Col095"))


TransformFailureMain = DTSTransformStat_OK

End Function

Function InsertSuccessMain()

DTSDestination("RNS") = CDate(DTSSource("Col066"))

InsertSuccessMain = DTSTransformStat_OK

End Function

This is the scrpit I am using to load this file. This script is working and loading data. But when I query to date field is returns nothing
Post #709714
Posted Monday, May 4, 2009 4:59 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
It seems that you need to modify the script, which looks curiously like a VBScript DTS script.

Try replacing this line
DTSDestination("CreateDate") = CDate(DTSSource("Col095"))

With this

If IsDate(DTSSource("Col095")) Then
DTSDestination("CreateDate") = CDate(DTSSource("Col095"))
End If




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #709814
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse