Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help transforming a string to a date using SSIS expression

Posted on 2013-06-05
2
Medium Priority
?
305 Views
Last Modified: 2016-02-11
Hi,

    I've been banging my head against the wall with this.  My flat file import has a dozen dates as strings in the format "YYYYMMDD", i.e.,  "20120517".  When I run the Data Flow with this Derived column (which is added) using expression :

(DT_DBDATE)(SUBSTRING(LRDDTES_01,5,2) + "-" + SUBSTRING(LRDDTES_01,7,2) + "-" + SUBSTRING(LRDDTES_01,1,4))

I get the error message(s) in the execution results at the end of this post. Not sure what else I can do.  There are no nulls or bad strings in any of the records. I also created a string column using (SUBSTRING(LRDDTES_01,5,2) + "-" + SUBSTRING(LRDDTES_01,7,2) + "-" + SUBSTRING(LRDDTES_01,1,4)) and the column yields "05-17-2012".

Scratching my head............................

BTW: I'm using Visual Studio 2012 Pro and SQL Server 2012 Standard

Error messages below:

[FES-DERIVED-CUSTOMERINFO [2]] Error: An error occurred while attempting to perform a type cast.

[FES-DERIVED-CUSTOMERINFO [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "FES-DERIVED-CUSTOMERINFO" failed because error code 0xC0049064 occurred, and the error row disposition on "FES-DERIVED-CUSTOMERINFO.Outputs[Derived Column Output].Columns[T_DATE_TEST_DB]" 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.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "FES-DERIVED-CUSTOMERINFO" (2) failed with error code 0xC0209029 while processing input "Derived Column Input" (3). 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.  There may be error messages posted before this with more information about the failure.

[FES-SOURCE-CUSTOMERINFO [274]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on FES-SOURCE-CUSTOMERINFO returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
0
Comment
Question by:ITMikeK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1000 total points
ID: 39223616
As far as I know <<DT_DBDATE  is a date structure that consists of year, month, and day. >> so did you tried just

(DT_DBDATE)(LRDDTES_01)


as your string is already in that format or like below for YYYY+MM+DD

(DT_DBDATE)(SUBSTRING(LRDDTES_01,1,4) + "-" + SUBSTRING(LRDDTES_01,5,2) + "-" + SUBSTRING(LRDDTES_01,7,2))
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1000 total points
ID: 39224001
Definitely need to add dashes in there if doing conversion in SSIS...

(DT_DATE)(SUBSTRING([LRDDTES],1,4) + "-" + SUBSTRING([LRDDTES],5,2) + "-" + SUBSTRING([LRDDTES],7,2))

Or, if absolutely confident with structure and YYYYMMDD compliance, leave it as a string in SSIS and allow SQL Server to sort it out on the actual insert / update.
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question