Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Flat file extraction error Expand / Collapse
Author
Message
Posted Tuesday, October 22, 2013 12:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:19 PM
Points: 115, Visits: 546
I am using ssis 2008 and trying to extract data from a tab delimited file



In the file above, I am getting an error where Counter=1494059

I am using a derived column transformation and it has a formula '(DT_DBTIMESTAMP)Date'

Due to that I am getting following errors:



[Derived Column 1 [295]] Error: An error occurred while attempting to perform a type cast.
[Derived Column 1 [295]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column 1" (295)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "Date" (297)" 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.

I tried to redirect erranous rows..but it is also not working

Can anyone pls help to sort this one out..

p.s. all the previous files were processed successfully, we are getting errors with this one file only..though it has identical structure and encoding
Post #1507281
Posted Tuesday, October 22, 2013 12:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:02 PM
Points: 5,259, Visits: 12,193
Rather than looking at the source data in Excel, can you please open it in Notepad++ or similar and then have a close look at the offending data?

From your screen shot, everything looks fine. But Excel has a tendency to mask problems.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1507285
Posted Tuesday, October 22, 2013 12:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:19 PM
Points: 115, Visits: 546
Now instead of Date and time conversion..I tried following:

SUBSTRING(LTRIM(RTRIM([Date])),LEN(LTRIM(RTRIM([Date]))) - 3,LEN(LTRIM(RTRIM([Date])))) + "-" + SUBSTRING(LTRIM(RTRIM([Date])),1,FINDSTRING(LTRIM(RTRIM([Date])),"/",1) - 1) + "-" + SUBSTRING(LTRIM(RTRIM([Date])),FINDSTRING(LTRIM(RTRIM([Date])),"/",1) + 1,2)+" 00:00:00.000"
STILL NOT WORKING

I get the error 'An error occurred while evaluating the function. '


Post #1507286
Posted Tuesday, October 22, 2013 1:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:19 PM
Points: 115, Visits: 546
Now instead of Date and time conversion..I tried following:



SUBSTRING(LTRIM(RTRIM([Date])),LEN(LTRIM(RTRIM([Date]))) - 3,LEN(LTRIM(RTRIM([Date])))) + "-" + SUBSTRING(LTRIM(RTRIM([Date])),1,FINDSTRING(LTRIM(RTRIM([Date])),"/",1) - 1) + "-" + SUBSTRING(LTRIM(RTRIM([Date])),FINDSTRING(LTRIM(RTRIM([Date])),"/",1) + 1,2)+" 00:00:00.000"


STILL NOT WORKING
Post #1507307
Posted Tuesday, October 22, 2013 1:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 13,562, Visits: 11,372
How are you so sure it fails on that specific line?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1507310
Posted Tuesday, October 22, 2013 1:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:19 PM
Points: 115, Visits: 546
I checked it in the Data Viewer
Post #1507312
Posted Tuesday, October 22, 2013 1:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 13,562, Visits: 11,372
rockstar283 (10/22/2013)
I checked it in the Data Viewer


I thought you said redirecting error rows didn't work?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1507314
Posted Tuesday, October 22, 2013 1:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:19 PM
Points: 115, Visits: 546
Yes, so there are no rows getting redirected on error output i.e. RED pipeline..and also the data viewer on Green pipeline is telling me where the error is
Post #1507318
Posted Tuesday, October 22, 2013 1:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 13,562, Visits: 11,372
rockstar283 (10/22/2013)
and also the data viewer on Green pipeline is telling me where the error is


And why is that, if I may ask?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1507332
Posted Tuesday, October 22, 2013 1:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 3:19 PM
Points: 115, Visits: 546
Data viewer on the Green pipeline is showing me all the records before that particular erroneous record
Post #1507343
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse