Solved

Need help transforming a string to a date using SSIS expression

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

911 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

25 Experts available now in Live!

Get 1:1 Help Now