Error in date conversion using DT_DATE in Derived

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Error in date conversion using DT_DATE in Derived

Author

Topic

SeekingWisdom
Starting Member

24 Posts

Posted - 2009-02-10 : 09:40:25

Hello!

I am presently working on SSIS package and attempting to convert a date in the format of "YYYYMMDD" using the following expressions in the Derived Column Transformation.

(DT_DATE)(SUBSTRING((DT_STR,8,1252)DATE_WRKD,1,4) + SUBSTRING((DT_STR,8,1252)DATE_WRKD,5,2) + SUBSTRING((DT_STR,8,1252)DATE_WRKD,7,2))

When I run the package the following exception is generated.

Error: 0xC0049064 at Data Flow Task, Derived Column [12720]: An error occurred while attempting to perform a type cast.
Error: 0xC0209029 at Data Flow Task, Derived Column [12720]: The "component "Derived Column" (12720)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "New_DATE_WRKD" (14860)" specifies failure on error. An error occurred on the specified object of the specified component.

I also attempted to perform the following as was suggested through another forum with the results shown below.

(SUBSTRING((DT_STR,8,1252)DATE_WRKD,1,4) + "/" + SUBSTRING((DT_STR,8,1252)DATE_WRKD,5,2) + "/" + SUBSTRING((DT_STR,8,1252)DATE_WRKD,7,2))

exception for the derived column transformation attempted above is:

Error: 0xC0049062 at Data Flow Task, Derived Column [12720]: An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type.
Error: 0xC0209029 at Data Flow Task, Derived Column [12720]: The "component "Derived Column" (12720)" failed because error code 0xC0049062 occurred, and the error row disposition on "output column "New_DATE_WRKD" (14860)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Derived Column" (12720) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Data Type in the Derived Column transformation is defined as date(DT_DATE)

I am processing the Flat File, followed by a Derived Column Transformation with the hope of eventually storing the resulting date into a column with the following definition.

RTN_PRCS_DT] [smalldatetime] NOT NULL

I am not familiar with SSIS and I am learning as I go.

Can anyone please assist me and share what they know about this exception? Can you also please tell me what is the meaning of "1252" code page in the substring Command? How do you go about deciding or assigning the "code page".

Thank you For your time and attention to my inquiry.

[/blue]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 09:51:58

is the source column of datetime type? if not what will format in which date values are appearing?

SeekingWisdom
Starting Member

24 Posts

Posted - 2009-02-10 : 10:09:09

Thank you for your reply!

Please note that the field is defined as DT_STR for a length of 8. The contents will be formatted as YYYYMMDD or 20090126.

Thanks..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 10:35:04

try (DT_WSTR,30)(DT_DBDATE) yourcolumn

SeekingWisdom
Starting Member

24 Posts

Posted - 2009-02-10 : 12:28:27

Please note the result of the latest attempt in accordance to your suggestion and the expression used below.

(DT_WSTR,8)(DT_DBDATE)DATE_WRKD

Error: 0xC0049064 at Data Flow Task, Derived Column [12720]: An error occurred while attempting to perform a type cast.
Error: 0xC0209029 at Data Flow Task, Derived Column [12720]: The "component "Derived Column" (12720)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "New_DATE_WRKD" (14860)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Derived Column" (12720) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Any ideas please?

Thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources