July 3, 2012 at 1:43 am
Hi,
We have ssis package which we are uploading the flatfile data to Sql table.And we are getting the belwo error while executing the job.
Below the JOb HIstory.
"
Started: 7:11:39 AM Error: 2012-07-03 07:11:39.71 Code: 0xC0202009 Source: Data Flow Task 1 Destination - DATA_S [26]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server
Native Client 10.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification". End Error Error: 2012-07-03 07:11:39.71 Code: 0xC020901C
Source: Data Flow Task 1 Destination - DATA_S [26] Description: There was an error with input column "Column 0" (49) on input "Destination Input" (39).
The column status returned was: "The value could not be converted because of a potential loss of data.". End Error Error: 2012-07-03 07:11:39.71 Code: 0xC0209029
Source: Data Flow Task 1 Destination - DATA_S [26] Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (39)"
failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (39)" 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. End Error Error: 2012-07-03 07:11:39.71
ode: 0xC0047022 Source: Data Flow Task 1 SSIS.Pipeline
Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - DATA_S" (26) failed with error code 0xC0209029 while processing
input "Destination Input" (39). 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. End Error DTExec:
The package execution returned DTSER_FAILURE (1). Started: 7:11:39 AM Finished: 7:11:39 AM Elapsed: 0.438
"
ISSUE :
The problem is there is a datatpype difference between flat file and table.
We are inserting char column to int cloumn in the table,
i have used the convertion for the table coulumn for flatfile in PAckage but still getting error.
Can any one tell is it possible to insert the insert the char to int ?if we can plz let me know how to do...
Many thanks..
July 3, 2012 at 1:51 am
This was removed by the editor as SPAM
July 3, 2012 at 2:29 am
You need to have Data viewer to find the exact row causing the problem and take it forward by avoiding / correcting the row according to your business. Thanks.
July 3, 2012 at 11:18 pm
Many Thanks for reply,
I have tried using a Derived Column transformation between Flatfie and destination components?
IN derived column i hvae added the a row with a command similar to: (DT_I4)[Column 0]
and mapped that column to the destination comp
But still getting the below error.
[Derived Column [54]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [54]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (54)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "Derived Column 1" (85)" 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 "Derived Column" (54) failed with error code 0xC0209029 while processing input "Derived Column Input" (55). 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.
July 4, 2012 at 12:09 am
You need to add error handling to your derived column.
Add a flat file destination, and connect the derived column to this flat file destination with the red arrow. Configure the derived column to redirect error rows. Put a dataviewer on the red arrow and look at the data that can't be casted.
Your problem basically is that you have some values in your column that can be converted to integers. You need to get rid of those before you attempt to write anything to your destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 4, 2012 at 12:51 am
Your problem basically is that you have some values in your column that can be converted to integers. You need to get rid of those before you attempt to write anything to your destination.
I have added the flatfile destination and connect the derived column to this flatfile destionation and also added data viwer between flatfile source , derived column and derived column ,flat file destination.
when i executed the package i could see from the Data Viwer that all the data loaded correctly between source flat file , derived column.
But between derived column ,flatfile destination...i could see from the Data viwer that one of the column as " NULL ",it is not loading the data to that column.
THis column contains data like "DD04500" which is a charater data in Source .
and i am inserting the data into INt type in destination(sql table).
IS It possible to insert the char data tpye (data like "DD04500") into an intiger column in destintion table.
Please adivise .
July 4, 2012 at 12:54 am
No, it's not possible.
Last time I checked DD04500 is not a number.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 4, 2012 at 1:06 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply