Solved

Need help transforming a string to a date using SSIS expression

Posted on 2013-06-05
2
304 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 250 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 250 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

627 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