SSIS Inserts 0 as a value in an identity column

views:

260

answers:

+2 Q:

SSIS Inserts 0 as a value in an identity column

Hi,

we have this problem : in sqlserver 2005/2008 we create some new tables with an integer identity column as the primary key : [Id] [int] IDENTITY(1,1) NOT NULL.
When loading data in this table via an SSIS dataflow task, SSIS should automatically fill the Id column. It does, BUT it starts at 0 in stead of 1. After that, when we delete all rows and do a checkident(reseed,0), we reload and it starts from 1 as it should. Any idea how we could get it right from the first time ?

+1 A:

Did you create the tables with the identity columns directly in SSMS? I've seen identity seeds start at 0 when using ERD tools to create the T-SQL DDL. Also, does it matter that the ID column starts at 1?

revelator 2010-03-17 15:40:34

Interesting question. I actually do not create any tables, I just "receive" the database "as is" and load it with our data. But as you asked this question I did a little test and created a table myself in SSMS. The when I load it with SSIS, all goes well and Id starts at 1. Interesting! So I'll ask how tables were created and let you know as soon as I find out.To answer your second question, it does matter since NHibernate has a problem with an Id of 0.

Hans Verriest 2010-03-18 07:37:03

Can you script out the create statement for the table to check the DDL?

revelator 2010-03-18 10:34:44

The tables are not created in SSMS directly but via a visual studio database project. I've created a table this way but now again ssis starts loading from 1, so no problem here.

Hans Verriest 2010-03-18 13:31:02

/****** Object: Table [dbo].[TestId] Script Date: 03/18/2010 14:26:56 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestId]') AND type in (N'U'))DROP TABLE [dbo].[TestId]GO/****** Object: Table [dbo].[TestId] Script Date: 03/18/2010 14:26:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestId]') AND type in (N'U'))BEGINCREATE TABLE [dbo].[TestId]( [Id] [int] IDENTITY(1,1) NOT NULL, [Text] [nchar](100) NOT NULL) ON [PRIMARY]ENDGO

Hans Verriest 2010-03-18 13:31:56

This is an example of a generated script I got for one of our "problem" tables (nothing special to my humble opinion) :-- ColumnsCREATE TABLE [FINOPV].[UitsplitsingWijze]([Id] [int] NOT NULL IDENTITY(1, 1),[Omschrijving] [nvarchar] (255) COLLATE Latin1_General_CI_AS NOT NULL,...) ON [PRIMARY] GO -- Constraints and indexesALTER TABLE [FINOPV].[UitsplitsingWijze] ADD CONSTRAINT [PK_UitsplitsingWijze] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]GO

Hans Verriest 2010-03-18 18:47:24

The DDL looks fine. Have you checked the Identity value prior to executing the package? Eg, dbcc checkident ('[FINOPV].[UitsplitsingWijze]'). It should be NULL if the table has not had any data inserted previously. Also, the following should ensure what the initial seed value is. SELECT IDENT_SEED('[FINOPV].[UitsplitsingWijze]')

revelator 2010-03-18 19:31:02

Is it possible that you have the option FastLoadKeepIdentity (oledb) or BulkInsertKeepIdentity (sql server) of the destination turned on?

If so, and you are mapping a source column to your destination id column, you could get the behavior you are describing.

santiiiii 2010-03-17 15:41:02

No, Keep Identity is off.

Hans Verriest 2010-03-18 07:10:12

ansaurus

tags:

views:

answers:

SSIS Inserts 0 as a value in an identity column

related questions