data not getting Loaded from csv file to sql server 2008 r2 table (Enterprise edition)

Question

data not getting Loaded from csv file to sql server 2008 r2 table (Enterprise edition)

sql2k8

Hall of Fame

Points: 3455
More actions
February 13, 2012 at 8:14 pm

#268219

Hi,
I am trying to load data from csv file into a table in sql server 2008 r2 (enterprise edition) on windows server 2008 r2 enterprise edition sp1.
I am getting the below error.Can you help me in resolving the issue.
Data conversion failed. The data conversion for column "Column 76" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Flat File Source [1]: The "output column "Column 76" (331)" failed because truncation occurred, and the truncation row disposition on "output column "Column 76" (331)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task, Flat File Source [1]: An error occurred while processing file "C:\Users\john\Desktop\reports\yedytr.txt" on data row 1.
Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. 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.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C:\Users\john\Desktop\reports\yedytr.txt" has ended.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "OLE DB Destination" (9)" wrote 0 rows.
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
The table structure is like the below one
CREATE TABLE [dbo].[test](
[Owner] [nvarchar](255) NULL,
[Suffix] [nvarchar](255) NULL,
[First_Name] [nvarchar](255) NULL,
[Last_Name] [nvarchar](255) NULL,
[Gender] [nvarchar](255) NULL,
[Companion] [nvarchar](255) NULL,
[Birthdate] [nvarchar](255) NULL,
[person_Type] [nvarchar](255) NULL,
[person_Status] [nvarchar](255) NULL,
[Credit_Type] [nvarchar](255) NULL,
[Credit_Line] [nvarchar](255) NULL,
[UYT] [nvarchar](255) NULL,
[Game_Type] [nvarchar](255) NULL,
[Company_Name] [nvarchar](255) NULL,
[Title] [nvarchar](255) NULL,
[Phone] [nvarchar](255) NULL,
[Extension] [nvarchar](255) NULL,
[Mobile] [nvarchar](255) NULL,
[Mail_to] [nvarchar](255) NULL,
[Address_1] [nvarchar](255) NULL,
[Address_2] [nvarchar](255) NULL,
[City1] [nvarchar](255) NULL,
[City2] [nvarchar](255) NULL,
[State] [nvarchar](255) NULL,
[Zip_Code] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[Email] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[Other_Address1] [nvarchar](255) NULL,
[Other_Address2] [nvarchar](255) NULL,
[Other_City] [nvarchar](255) NULL,
[Other_State] [nvarchar](255) NULL,
[Other_Zip] [nvarchar](255) NULL,
[Other_Country] [nvarchar](255) NULL,
[ADA] [nvarchar](255) NULL,
[Fax] [nvarchar](255) NULL,
[NewMoveType] [nvarchar](255) NULL,
[cNName] [nvarchar](255) NULL,
[NewMoveEffect] [nvarchar](255) NULL,
[RecordMatchCode] [nvarchar](255) NULL,
[DFTA] [nvarchar](255) NULL,
[DFTB] [nvarchar](255) NULL,
[DFTC] [nvarchar](255) NULL,
[Code] [nvarchar](255) NULL,
[RecTypeCode] [nvarchar](255) NULL,
[PriNumber] [nvarchar](255) NULL,
[PreDir] [nvarchar](255) NULL,
[PriName] [nvarchar](255) NULL,
[Suffix] [nvarchar](255) NULL,
[PostDir] [nvarchar](255) NULL,
[SecUnitDes] [nvarchar](255) NULL,
[SecNumber] [nvarchar](255) NULL,
[CityName] [nvarchar](255) NULL,
[StateUYL] [nvarchar](255) NULL,
[ZipCode] [nvarchar](255) NULL,
[AddonCode] [nvarchar](255) NULL,
[CarrierID] [nvarchar](255) NULL,
[Number] [nvarchar](255) NULL,
[PriAddress] [nvarchar](255) NULL,
[SecAddress] [nvarchar](255) NULL,
[AltAddress] [nvarchar](255) NULL,
[Urban] [nvarchar](255) NULL,
[KHNCode] [nvarchar](255) NULL,
[RetCode] [nvarchar](255) NULL,
[AAFootnotes] [nvarchar](255) NULL,
[DelCode] [nvarchar](255) NULL,
[AccuracyInd] [nvarchar](255) NULL,
[Deliverability] [nvarchar](255) NULL,
[DelType] [nvarchar](255) NULL,
[Vacancy] [nvarchar](255) NULL,
[Seasonal] [nvarchar](255) NULL,
[Sort] [nvarchar](255) NULL,
[Lacs] [nvarchar](255) NULL,
[DropInd] [nvarchar](255) NULL,
[DropCount] [nvarchar](255) NULL,
[Footnotes] [nvarchar](255) NULL,
[AddressFlags] [nvarchar](255) NULL,
[AddressType] [nvarchar](255) NULL,
[AALa] [nvarchar](255) NULL,
[SuiteLink] [nvarchar](255) NULL,
[CZOFootnotes] [nvarchar](255) NULL,
[CZOReturnCode] [nvarchar](255) NULL,
[APOFPO] [nvarchar](255) NULL,
[Numeric_Business] [nvarchar](255) NULL,
[Ca_Postal_Code] [nvarchar](255) NULL,
[Blank_City] [nvarchar](255) NULL,
[Numeric_Name_City] [nvarchar](255) NULL,
[Blank_Ind] [nvarchar](255) NULL,
[Invalid_Zip] [nvarchar](255) NULL,
[Invalid_State] [nvarchar](255) NULL,
[Invalid_SCF] [nvarchar](255) NULL,
[Invalid_State_ZIP] [nvarchar](255) NULL,
[Blank_Address] [nvarchar](255) NULL,
[Invalid_City_ZIP] [nvarchar](255) NULL,
[Record] [nvarchar](255) NULL,
[KHQ_Group_ID] [nvarchar](255) NULL
) ON [PRIMARY]
GO
Thank You,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

The error is pretty much self explanatory. You have a "field" in the data (probably the 76th field) that's too big for the table. Temporarily change the 76th column in your table (or whatever maps to the 76th field in the data) to be a VARCHAR(MAX) or NVARCHAR(MAX) and then check for the max length of the field so you can make the column a more appropriate size.

As a side bar, it's a real waste to have columns for zip codes (for example) to be set to 255 characters in width. You might want to do a little optimation there while your checking the max length of field 76. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Jeff Moden (2/13/2012)
As a side bar, it's a real waste to have columns for zip codes (for example) to be set to 255 characters in width. You might want to do a little optimation there while your checking the max length of field 76. 😉

I understand the motivation. The source is a .csv file, so there is no actual garantuee that the zip code will always have 4 characters (or how many characters they use in that country). So if you import everything AS-IS without doing any validation in SSIS, it is best to have bigger column sizes.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP