Discussion :

[finarfin95]

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

[finarfin95]

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