Convert String DataType to Date datatype in DTS

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 String DataType to Date datatype in...

12 posts, Page 1 of 2

1 2 »»

Convert String DataType to Date datatype in DTS

Rate Topic

Display Mode

Topic Options

Author

Message

ajoshi-918641

Posted Friday, May 01, 2009 9:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, April 07, 2013 9:02 AM
Points: 7, Visits: 54

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

Phil Parkin

Posted Friday, May 01, 2009 8:29 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 4,247, Visits: 9,500

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #708846

AMrutajoshi1

Posted Sunday, May 03, 2009 8:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 04, 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

Phil Parkin

Posted Sunday, May 03, 2009 5:56 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 4,247, Visits: 9,500

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #709129

ajoshi-918641

Posted Monday, May 04, 2009 7:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, April 07, 2013 9:02 AM
Points: 7, Visits: 54

Yes it is 0 in sample data. Sometimes it is an empty field e.g 123456,,0,12/04/09etc

Post #709363

ajoshi-918641

Posted Monday, May 04, 2009 7:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, April 07, 2013 9:02 AM
Points: 7, Visits: 54

I just confirmed thius file is automatically generated from prog and will proper date field or nothing which is blank field

Post #709372

ajoshi-918641

Posted Monday, May 04, 2009 11:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, April 07, 2013 9:02 AM
Points: 7, Visits: 54

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

Ray M

Posted Monday, May 04, 2009 2:01 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 8:25 AM
Points: 1,467, Visits: 922

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

ajoshi-918641

Posted Monday, May 04, 2009 2:19 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, April 07, 2013 9:02 AM
Points: 7, Visits: 54

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

Phil Parkin

Posted Monday, May 04, 2009 4:59 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 4,247, Visits: 9,500

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #709814

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions