Error while running DTS package

Question

Error while running DTS package

Ansari Asif

Grasshopper

Points: 17
More actions
October 11, 2004 at 12:38 am

#60325

Hi All,
I have created a DTS package which copies the data from Ingres table to SQL Server 2000 table using System ODBC.
When I try to execute the package, it gives the error,
"[CA][Ingres ODBC Driver]Restricted data type attribute violation".
Any help will be appreciated.
Thnx.

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

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

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 1

Is is possible that your Ingres table has a column of a custom datatype that SQL Server has no known equivalent for? Or a size that SQL Server can't handle?

You might try pulling one column, and if it works, add a second then a third, until you determine what the problem column is. Once you know what the problem column is, you might be able to convert it to a datatype or size that is acceptable.

Lots of luck,

Metra

Ansari Asif Grasshopper Points: 17 More actions · Answer 2

Thanks Metra,

I removed all the transformations and mapped one column at a time, but for each and every single transformation it gives the same error as it was before.

Is it related to ODBC configuration?

Thnx.

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 3

It could be -- You may have the wrong driver for your version of Ingres -- apparently there's three main types of Ingres databases -- the original, Ingres II, and OpenIngres. Check out the last two links at the bottom -- they are Ingres links, and may help you the most.

You may have the wrong datatypes -- try converting the receiving table's fields to varchar(255) or text for all char fields, to numeric(13,3) (or more) for all int, integer1, integer2, smallint, money fields, etc. In otherwords, try bringing the data into the largest probably similar field. Even try bringing numeric data in as varchar... just to be sure you can get it, and THEN convert it to the datatype of your choice...

Can you pull the Ingres data into any other application, such as any version of Access you happen to have handy? If you can link in the ingres tables to an mdb and view the data, and if you can run a pass-through select on the Ingres tables, then the machine you're using has the right drivers, but you would also need to verify that the SQL Server box has the same ones.

I had a whole bunch of links, I'll try to recreate them:

How to determine what datatypes the source has: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcexplicit_data_type_conversion.asp

Native SQL used at source:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlnativesql.asp

Special columns:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlspecialcolumns.asp

Somebody with a similar problem:

http://www.dbforums.com/printthread.php?threadid=352365

pdf of Oracle datatype conversions which may give you some help, but may not:

http://www.oracle.com/technology/software/tech/migration/toolkits/ingres/ingresds.pdf

Ingres documentation: check out the Connectivity Guide -- it may be the best help.

http://opensource.ca.com/projects/ingres/documents

Link to discussion where you could perhaps find the best driver:

http://opensource.ca.com/projects/ingres/forum/20/745837393272?b_start:int=0#654554212404