February 9, 2009 at 6:01 pm
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" in the substring Command?
Thank you For your time and attention to my inquiry.
February 9, 2009 at 10:29 pm
Please post in the correct forum. Moved to SSIS.
The 1252 should be the length of data selected.
You need to look through your data. It's possible that you have some rows that work fine and some that error out because of the data. It's a process of narrowing down which data might be causing you issues.
February 10, 2009 at 7:22 am
Thank you for your reply.
Please note information I came upon regarding the 1252 question.
According to Microsoft SQL Server Developer Center:
When the Data Type is DT_STR the parameters Character count "charcount" and codepage are expected as shown in the example:
Data Type DT_STR Parameter charcount codepage
(DT_STR,30,1252) casts 30 bytes, or 30 single characters, to the DT_STR data type using the 1252 code page.
I have looked at the date fields in the flat file and they are all consistent with respect to values and content.
I must say that the following comment on the microsoft SQL Server Development Central is not very helpful either.
If the code page is a multibyte character code page, the number of bytes and characters may differ. Casting from a DT_WSTR to a DT_STR with the same charcount value may cause truncation of the final characters in the converted string. If sufficient storage is available in the column of the destination table, set the value of the charcount parameter to reflect the number of bytes that the multibyte code page requires. For example, if you cast character data to a DT_STR data type using the 936 code page, you should set charcount to a value up to two times greater than the number of characters that you expect the data to contain; if you cast character data using the UTF-8 code page, you should set charcount to a value up to four times greater.
Any other ideas please.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply