Solved

Need help transforming a string to a date using SSIS expression

Posted on 2013-06-05
2
294 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
2 Comments
 
LVL 39

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now