December 21, 2001 at 9:10 am
i created a package in DTS and tried to transfer data from excel spreadsheet to SQl but it gave me an error message that not null fields must have a related column. well that not null field in SQL table is an auto generated ID field(primary key) how can i have column mapping for that ID field.
December 21, 2001 at 9:41 am
I've done this quite a bit. There should not be a mapping for the field on the transformations tab.
Also, a PK in SQL Server deos not necessarily mean an auto-generated field. That would be an identity field, and this has nothing to do with a PK.
Steve Jones
December 21, 2001 at 10:36 am
Well, I had a short chat session with nasir afridi and here is some followup:
The DDL for the table:
if exists (select * from sysobjects where id = object_id(N'[dbo].[ProductOwnerInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ProductOwnerInfo]
GO
CREATE TABLE [dbo].[ProductOwnerInfo] (
[custNumber] [int] NOT NULL ,
[fname] [varchar] (50) NULL ,
[lname] [varchar] (50) NULL ,
[address1] [varchar] (100) NULL ,
[address2] [varchar] (100) NULL ,
[pobox] [varchar] (50) NULL ,
[city] [varchar] (50) NULL ,
[state] [varchar] (50) NULL
, [zipcode] [varchar] (50) NULL ,
[country] [varchar] (50) NULL ,
[countryD] [varchar] (50) NULL ,
[homephone] [varchar] (50) NULL ,
[otherphone] [varchar] (50) NULL ,
[fax] [varchar] (50) NULL ,
[varchar] (50) NULL ,
[company] [varchar] (50) NULL ,
[region] [varchar] (50) NULL ,
[regionD] [varchar] (50) NULL ,
[club] [varchar] (20) NULL ,
[mail] [varchar] (20) NULL ,
[warranty] [varchar] (20) NULL ,
[warrantyCardRestricted] [varchar] (20) NULL ,
[websiteOrder] [varchar] (20) NULL ,
[websiteOrderNumber] [varchar] (25) NULL ,
[DMSSubscriber] [varchar] (25) NULL ,
[DMSSubcriptionDate] [smalldatetime] NULL ,
[Status] [varchar] (10) NULL ,
[dateCreated] [smalldatetime] NULL ,
[dateEdited] [smalldatetime] NULL ,
[editorName] [nvarchar] (50) NULL
) ON [PRIMARY]
Notice, there is no identity value, which would be the auto generate. I generated this script:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_ProductOwnerInfo
(
custNumber int NOT NULL IDENTITY (1000, 1),
fname varchar(50) NULL,
lname varchar(50) NULL,
address1 varchar(100) NULL,
address2 varchar(100) NULL,
pobox varchar(50) NULL,
city varchar(50) NULL,
state varchar(50) NULL,
zipcode varchar(50) NULL,
country varchar(50) NULL,
countryD varchar(50) NULL,
homephone varchar(50) NULL,
otherphone varchar(50) NULL,
fax varchar(50) NULL,
email varchar(50) NULL,
company varchar(50) NULL,
region varchar(50) NULL,
regionD varchar(50) NULL,
club varchar(20) NULL,
mail varchar(20) NULL,
warranty varchar(20) NULL,
warrantyCardRestricted varchar(20) NULL,
websiteOrder varchar(20) NULL,
websiteOrderNumber varchar(25) NULL,
DMSSubscriber varchar(25) NULL,
DMSSubcriptionDate smalldatetime NULL,
Status varchar(10) NULL,
dateCreated smalldatetime NULL,
dateEdited smalldatetime NULL,
editorName nvarchar(50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_ProductOwnerInfo ON
GO
IF EXISTS(SELECT * FROM dbo.ProductOwnerInfo)
EXEC('INSERT INTO dbo.Tmp_ProductOwnerInfo (custNumber, fname, lname, address1, address2, pobox, city, state, zipcode, country, countryD, homephone, otherphone, fax, email, company, region, regionD, club, mail, warranty, warrantyCardRestricted, websiteOrder, websiteOrderNumber, DMSSubscriber, DMSSubcriptionDate, Status, dateCreated, dateEdited, editorName)
SELECT custNumber, fname, lname, address1, address2, pobox, city, state, zipcode, country, countryD, homephone, otherphone, fax, email, company, region, regionD, club, mail, warranty, warrantyCardRestricted, websiteOrder, websiteOrderNumber, DMSSubscriber, DMSSubcriptionDate, Status, dateCreated, dateEdited, editorName FROM dbo.ProductOwnerInfo TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_ProductOwnerInfo OFF
GO
DROP TABLE dbo.ProductOwnerInfo
GO
EXECUTE sp_rename N'dbo.Tmp_ProductOwnerInfo', N'ProductOwnerInfo', 'OBJECT'
GO
COMMIT
and this should fix the issue. The first value after the IDENTITY keyword in this script should be set to the highest value in the table + 1.
Steve Jones
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply