Conversion failed because the data value overflowe

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Conversion failed because the data value overflowe

Author

Topic

Finarfin
Starting Member

28 Posts

Posted - 2007-11-12 : 05:02:27

Hi all,

I have a problem while transforming data from an Access DB to an SQL 2005 DB.

Context:
- Migration of packages from SQL 2000 to SQL 2005
- DB SQL 2005 is a back up from SQL 2000
- The access DB is the same than the one used with SQL 2000

Error:
[OLE DB Source [1]] Error: There was an error with output column "ID" (32) on output "OLE DB Source Output" (11). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Access Source:
tblSource
ID DateID ConfigIDRequest FromTime ToTime
43221 01.01.2007 362 00.00 05.30
43233 01.01.2007 362 21.10 23.59
43234 01.02.2007 362 00.00 05.30
43244 01.02.2007 362 21.10 23.59
43247 01.03.2007 362 00.00 05.30
...

In the access table, the datatype of ID is "AutoNumber" :
- field size : Long Integer
- new values : increment
- indexed : yes.

SQL Destination:
tblDestination
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblDestination](
[ID] [int] NOT NULL,
[DateID] [nvarchar](10) NULL,
[ConfigIDRequest] [int] NULL,
[FromTime] [nvarchar](5) NULL,
[ToTime] [nvarchar](5) NULL
) ON [PRIMARY]

SSIS Package description:
- Control Flow:
* Data Flow Task
- Data Flow:
* OLE DB Source pointing to tblSource, using AccessCon
* OLE DB Destination pointing to tblDestination, using SQL2005Con
- Connections:
* AccessCon : Native OLE DB\Microsoft Jet 4.0 OLE DB Provider pointing to AccessSource.mdb
* SQL2005Con : Native OLE DB\Microsoft OLE DB Provider for SQL Server

NB: All those components are default configured

Previous tests executed:

1. OLE DB Source Preview : OK, same records.
2. Error redirection to flat file for ID column : here are the first records

ErrorOutput.txtErrorCode,ID,DateID,ConfigIDRequest,FromTime,ToTime, ErrorColumn
-1071607691,43221,01.01.2007,362,00.00,05.30,32
-1071607691,43222,01.01.2007,363,05.30,05.50,32
-1071607691,43223,01.01.2007,366,05.50,06.20,32
-1071607691,43224,01.01.2007,370,06.20,12.20,32
-1071607691,43225,01.01.2007,365,12.20,13.00,32

3. Execute the transformation on the SQL2000 server, for the same Access DB, to the initial SQL 2000 DB : OK, no error.

Questions:

- Do you have an idea of what differs between SQL2000 and SQL2005 in this kind of situation?
- Why is this working for 2000 and not 2005?
- Why the error message says "output column "ID" (32) on output "OLE DB Source Output" (11). ". Shouldn't it be something like "output column "ID" (32) on input "ID" (11). " (with the second ID column for the SQL DB).
- May be the error comes from my connections parameters, one parameter which doesn't exists in SQL2000?

Thanks,

Romain

nduggan23
Starting Member

42 Posts

Posted - 2007-11-12 : 06:06:56

At a glance, difficult i would see is within your datetime fields in access transferring them to 2005. im assuming the access table is storing your times as date time not varchar. if this is the case then when it converts to nvarchar it will truncate your data. or try to at least. therefore fail.. check your data types.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 06:14:52

If ID is Autonumber, you can't insert values into it.

E 12°55'05.25"
N 56°04'39.16"

Finarfin
Starting Member

28 Posts

Posted - 2007-11-12 : 06:46:32

Thx for the answers!

@nduggan23 : in access DB DateID, FromTime and ToTime are stored as "text". I guess this excudes the conversion to varchar assumption.

@Peso : ID column is Autonumber in Access DB (source), but is not identity specified in SQL 2005 (destination).

The weirdest thing is that this transformation works well for SQL2000, there is definitely something I missed... but what?

Romain

Thank you all,
Romain

nduggan23
Starting Member

42 Posts

Posted - 2007-11-12 : 06:54:14

Try BigInt, your converting from long to int within your current scheme.

Finarfin
Starting Member

28 Posts

Posted - 2007-11-12 : 08:13:32

I added a Dataconversion to convert ID from I2 to I8. It seems like the conversion task isn't reached. The error comes from the OLE DB Source.

Even if I create a msgbx directly connected to the source, with no data transfert, the OLE DB Source displays the error.

Thank you all,
Romain

Finarfin
Starting Member

28 Posts

Posted - 2007-11-12 : 09:46:25

Ok, I got it! ("I" is a bit strong).

I forgot to check advanced properties on my OLE DB Source, where I can see that the default parameters implicitely converts my external column (4 bytes int) to an output column (2 bytes int). I don't why the default component doesn't thake the same integer size...

Thank you for your help!

Thank you all,
Romain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources