MS Access to SQL 2005 conversion errors

Question

Post reply

MS Access to SQL 2005 conversion errors

Scott Thornton-407727

SSCrazy

Points: 2271
More actions
March 17, 2008 at 10:17 pm

#121070

Hi,

Getting back into SSIS again after some time away.....

Have to import from an Access Database table into a table on MS SQL 2005.

I receive a bunch of conversion errors and I am wondering about the best way of fixing the problem.

I am using a dataflow task, with OLE Db datasoruces.

An example error is:

[dbname [130]] Warning: Truncation may occur due to inserting data from data flow column "GENDER" with a length of 255 to database column "GENDER" with a length of 2.

The datatype on the Access side is Text and is retrived via a sql query. The datatype on SQL 2005 is nvarchar(2). I can change the datatype on the SQl 2005, but i feel i should be able to convert it somehow.

Ive just started looking into the Character Map\Derived Column transformations, but I think the solution may me something a bit simpler?

thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

You can change the behavior on truncation. Look under "configure error handling" in the SSIS data flow task (each of the various stages should have a config error handling). Assuming you know that the data in access in that field really is only 2 characters or less, then just tell it to ignore the truncation error

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?