• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 602
  • Last Modified:

SSIS Date


I am creating a SSIS package to extract data from a flat file. In the flat file there is a date column in the 'YYYYMM' format. I need to convert it into a datetime, using 01 as the DD part.

For exampe if the input value is '201001' in YYYYMM format. The converted value should be in the '01/01/2010 00:00:00.000' format. I am using the derived column expression to do that but had no success yet .... can you guys please help me..

Thanks a lot...
0
banjara15
Asked:
banjara15
  • 4
  • 3
1 Solution
 
Reza RadConsultant, TrainerCommented:
try this expression:
field is the column with YYYYMM value,


(DT_DBDATE)(SUBSTRING(field,1,4) + "-" + SUBSTRING(field,5,2) + "-01")


0
 
banjara15Author Commented:

Thanks a lot for the prompt reply, I think this will solve the problem... I'll do some more testing to make sure...
0
 
Reza RadConsultant, TrainerCommented:
let me know if you have any problem
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
banjara15Author Commented:

Hi Reza, It worked fine but it showed error for NULL values. To handle the null values, I used a derived column and used this expression, [Effective Date] is the column name. The derived column data type is "database date"

(LTRIM(RTRIM([Effective Date])) != "" ? (DT_DBDATE)(SUBSTRING(LTRIM(RTRIM([Effective Date])),1,4) + "-" + SUBSTRING(LTRIM(RTRIM([Effective Date])),5,2) + "-01") : (DT_DBDATE)[Effective Date])

But this also gives error for NULL values. The error is

* [DER_Trim_Data [274]] Error: The conditional operation failed.
* [DER_Trim_Data [274]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "DER_Trim_Data" (274)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "Formatted_Effective_Date" (5390)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

0
 
banjara15Author Commented:

Can you please help me, how should I fix the problem...
0
 
Reza RadConsultant, TrainerCommented:
use this as derived column, this will return null for null values,

ISNULL(EffectiveDate) ? NULL(DT_DATE) : (DT_DBDATE)(SUBSTRING(EffectiveDate,1,4) + "-" + SUBSTRING(EffectiveDate,5,2) + "-01")


ISNULL(EffectiveDate) ? NULL(DT_DATE) : (DT_DBDATE)(SUBSTRING(EffectiveDate,1,4) + "-" + SUBSTRING(EffectiveDate,5,2) + "-01")

Open in new window

0
 
banjara15Author Commented:

Thanks for your help, this is the solution I was looking for... :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now