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

SSIS: Not able to transfer a FLAT FILE Data to Database. Please help Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 3:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 12:29 PM
Points: 54, Visits: 101
Hi team
My flat file content is
emp_no emp_fname emp_lname dept_no,
25348 Matthew Smith d3 ,
10102 Ann Jones d3 ,
18316 John Barrimore d1 ,
29346 James James d2 ,
9031 Elsa Bertoni d2 ,
2581 Elke Hansel d2 ,
28559 Sybill Moser d1


ROW DELIMITER is COMMA & COLUMN DELIMITER IS COLUMN.

& my Table in SQL SERVER IS

EMP_DATA
(
EMP_ID int,
F_NAME varchar(20),
L_NAME varchar(20),
DEPT_ID varchar(10)
)

But when i carry on the PACKAGE Execution, the below error are stopping me
[Data Conversion 0 - 0 [47]] Error: The "output column "Column 3" (59)" failed because truncation occurred, and the truncation row disposition on "output column "Column 3" (59)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[Data Conversion 0 - 0 [47]] Error: Data conversion failed while converting column "DEPT_ID" (22) to column "Column 3" (59). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion 0 - 0" (47) failed with error code 0xC020902A while processing input "Data Conversion Input" (48). 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.


The mapping is done Correct.
The Length of column variables match in CONTROL FLOW ALSO DATA FLOW.

Could anyone please help me understanding the above errors with possible scenarios?
Post #1467049
Posted Tuesday, June 25, 2013 4:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 9, 2014 12:42 AM
Points: 68, Visits: 413
I THINK
your flat file content is not arranged as matching to database table, add commas(,) correctly between as shown below .use below edited data as flat file source and try....
*Its considering a whole line as one column and trying to insert which is more than the size of the columns defined in the source and you will enter all the values to single column that's wrong.
*like it will insert emp_no emp_fname emp_lname dept_no,this whole line into emp_no ..which is wrong.

correct flat file source can be:
emp_no, emp_fname, emp_lname, dept_no
25348,Matthew, Smith ,d3
10102 ,Ann ,Jones ,d3
18316 ,John ,Barrimore,d1
29346 ,James ,James, d2
9031, Elsa, Bertoni,d2
2581 , Elke, Hansel ,d2
28559 ,Sybill ,Moser ,d1
then
use row delimiter = cr-lf
column delimeter= ,(comma)
Post #1467067
Posted Tuesday, June 25, 2013 4:45 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 13, 2014 2:00 PM
Points: 712, Visits: 790
Think its the column delimiter
When on the columns tab of the source connector you will see an option for column delimiter - its a drop down. But you can click in there and press the spoace bar. This will then work for your data.

However seperating columns with a space is very likely to cause problems later on. Its much better to use | or # or something else taht will not occur in the data set.

E
Post #1467072
Posted Tuesday, June 25, 2013 9:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:11 AM
Points: 309, Visits: 299
Also, if you are importing free form text, I find it quite helpful to have a text delimiter, such as a double quote, in addition to a column delimiter. Even though the design of the database wasn't meant to include data that has commas, single quotes, or other special characters in it, doesn't mean that a user won't try to shove them in there.

-G
Post #1467246
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse