Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need help transforming a string to a date using SSIS expression

Posted on 2013-06-05
2
Medium Priority
?
307 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how the fundamental information of how to create a table.
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…

971 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