Import data and identity field

[wallaceoc80]

I have created a table in SQL Server and I want to import data from a MS Access database.

The table I have created has the following columns:

AppID, AppName, AppMnem, GripsID, Decommissioned, Last Modified, Modified By

Decommissioned is a boolean value in the MS Access table and is a int value in the SQL Server table.

However, my problem is, is that I have AppID set as the primary key and it is also set as an identity column that is to increment by 1 every time a record is created.

However, when I run the query to import the data from the Access table I try to leave the AppID field blank so that a value is auto generated. However, I get the following error:


Error at destination for row number 1. Errors encountered so far in this task: 1.
Instert error, column 1 ('AppID', DBTYPE_I4), status 10: Integrity violation; attempt to insert null data or data which violates constraints.
Unspecified Error


Any ideas what the problem is?

Regards,

Wallace

 

[bartsimpson]

It looks like you may be trying to import a null value into that field. Go into the properties of the connection between the access database and the sql database in the DTS you set up, and make sure that there is no link between the two AppID fields.

 

[wallaceoc80]

This is the query I created to get the data from the Access db:

select `T>Application>General`.`Application Name`, `T>Application>General`.`Application Mnem`, `T>Application>General`.`GripsID`, `T>Application>General`.`Decommissioned`, `T>Application>General`.`Last Modified`, `T>Application>General`.`Modified By`
from `T>Application>General`
where `T>Application>General`.`Application Mnem`='TMS'
order by `T>Application>General`.`Application Mnem`

This is the transformation:

'**********************************************************************
'  Visual Basic Transformation Script
'  Copy each source column to the
'  destination column
'************************************************************************

Function Main()
	DTSDestination("AppName") = DTSSource("Application Name")
	DTSDestination("AppMnem") = DTSSource("Application Mnem")
	DTSDestination("GripsID") = DTSSource("GripsID")
	DTSDestination("Decommissioned") = DTSSource("Decommissioned")
	DTSDestination("Last Modified") = DTSSource("Last Modified")
	DTSDestination("Modified By") = DTSSource("Modified By")
	Main = DTSTransformStat_OK
End Function

In the mapping because I select the destination table to be APPLICATION and APPLICATION has an AppID field I have to put <ignore> in the source filed for the APPLICATION.AppID field.

Does this make it any clearer?

Thanks,

Wallace

 

[wallaceoc80]

To add to this,

I tried a simple SQL statement

insert into Application (AppName, AppMnem, GripsID)
values('Management System', 'TMS', 501205);

to enter some random data into the "Not Null" fields and it worked properly. The identity field ("AppID") incremented as it was supposed to!

 

[bartsimpson]

Right click the arrow between the data source and the data destination, and select properties. Click the "trasformation" tab, and then click "edit". This will then show you source & destination tabs. You should deselect the AppID field from both of then.

 

[wallaceoc80]

At what stage should I be seeing this arrow? I used the Data Transformation Services under Tools in Enterprise Manager of Microsoft SQL Clients Util 2000.