Load dd/mm/yy formated data into sqlserver

Question

Load dd/mm/yy formated data into sqlserver

sabyasm

SSCarpal Tunnel

Points: 4324
More actions
September 13, 2009 at 9:05 am

#214383

Hi All,
I am new to SSIS and stuck with a problem to convert date datatype. I have a flatfile containing date column in the format ddmmyy which needs to be transformed into a flatfile with some transformation and then loaded into a sqlserver DB.
While defining the flatfile connection mgr - I have defined the column as DT_STR and subsequently using a derived column component to convert into DT_DATE or DT_DBTIMESTAMP.
I used the hint from :
[/url]
the derived column transformation looks like this:
Derived Column Name: Derived LastLoginDate
Derived Column: add as a new column
Expression: LEN(TRIM(LastLoginDate)) == 0 ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING(LastLoginDate,4,2) + "-" + SUBSTRING(LastLoginDate,1,2) + "-" + SUBSTRING(LastLoginDate,6,2))
data type: Date [DT_DATE]
But getting the error :
Error: 0xC0049063 at DFT Convert RAW feed to standard format, Derived Column [934]: The conditional operation failed.
Error: 0xC0209029 at DFT Convert RAW feed to standard format, Derived Column [934]: The "component "Derived Column" (934)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "Derived LastLoginDate" (1322)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047022 at DFT Convert RAW feed to standard format, DTS.Pipeline: The ProcessInput method on component "Derived Column" (934) 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 DFT Convert RAW feed to standard format, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.
Any help will be highly appreciated ...
thanks ...
[font="Comic Sans MS"]--
Sabya[/font]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

forum member Ten Centuries Points: 1290 More actions · Answer 1

Hi,

I think you need to use Data Conversion component if you havent already before derived column and after flat file source component.

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 2

I would split the debugging (ps - not talking abt the conditional split). Just test the NULL(DT_DATE) without the other else.

Then try only using your custom function\transformation. I think there may be a problem converting from str to date, what is the destination datatype (string\varchar or datetime)?