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...
banjara15Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
banjara15Author Commented:

Thanks for your help, this is the solution I was looking for... :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.