sql 2005 import error from flat file

[snshgrdn]

I am importing data from flat file source to destination tables that were created in SQL 2000 (imported to SQL 2005). I finally figured out that I had to match the column in source and destination file so I get past the validation errors. Below is the error I am getting now. The column in my text file where the error occurs is just a data field that is one character, I've changed it to match, then when back and set it to the default of 50, but keep getting the same error.

Executing (Error)
Messages
* Error 0xc02020c5: Data Flow Task: Data conversion failed while converting column "PIO" (22) to column "PIO" (342). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

* Error 0xc0209029: Data Flow Task: The "output column "PIO" (342)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "PIO" (342)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

* Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Data Conversion 1" (322) failed with error code 0xC0209029. 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.
(SQL Server Import and Export Wizard)

* Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.
(SQL Server Import and Export Wizard)

* Error 0xc02020c4: Data Flow Task: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)
* Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - GASTROSEVFIN0101TP053106" (1) returned error code 0xC02020C4. 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.
(SQL Server Import and Export Wizard)

* Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

 

[mrdenny]

Are you using the Import/Export wizzard?

Have you applied SP1 to your workstation?

Sounds like the engine isn't seeing the delimiter that you specified and it thinks that the column is longer than it should.

Try doing the import manually using the BIDS.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[snshgrdn]

Yes I'm using the Import/Export wizard.

What is SP1? What is BIDS?

Forgive my ignorance, I set up my stuff on 2000 two years ago and haven't had to use these tools before.

 

[mrdenny]

SP1 is Service Pack 1.
BIDS is the Business Inteligence Development Studio.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com