SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS: Not able to transfer a FLAT FILE Data to Database. Please help


SSIS: Not able to transfer a FLAT FILE Data to Database. Please help

Author
Message
kiran.vaichalkar
kiran.vaichalkar
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 127
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?
super48
super48
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 517
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)
Ells
Ells
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1208 Visits: 931
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
:-)
Greg A Goss
Greg  A Goss
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 409
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search