ActiveX Script and Identity Insert.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

ActiveX Script and Identity Insert.

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-07 : 08:02:48

Hi,

I'm importing data from an Excel File to a temp table in my sql server. The temp table has 2 columns which are not there in the excel file.

The structure of excel file is something like this:
CustomerName
CustomerId
CustomerAddress

The Table is something like this:
RowId Int Identity Not Null
CustomerName Varchar(100) Not Null
CustomerId Int Not Null
CustomerAddress Varchar(300) Not Null
ValidRecord Bit Not Null

I need to set the values for valid record as 1, for all the records and RowId should be populated by identity insert. I tried two ways one is the transformation provided by dts, Where I got error for the ValidRecord column stating that I cannot have insert Null.

Next I tried the transformation where I write the activex script for the transformation which is this:
DTSDestination("RowId") = ignore
DTSDestination("CustomerName") = DTSSource("CustomerName")
DTSDestination("CustomerId") = DTSSource("CustomerId")
DTSDestination("CustomerAddress") = DTSSource("CustomerAddress")
DTSDestination("ValidRecord") = 1

With the above one I get the error for trying to insert null values, this time its for the Rowid column. I tried Checking the enable identity insert check box and vice versa. But of no use.

Infact I wanted to use Multiphase datapump so that I can check each record during transformation and mark it as either valid record or not. But couldnt figure out how to do it.

How to achieve what I'm trying to do?

Karunakaran

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-12 : 00:24:10

Can some one help me with this?

Karunakaran

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-04-12 : 05:36:07

Just remove the transformation with destination "RowId" altogether.

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-12 : 05:50:34

I tried that one too...Its not working...I get an error stating cannot insert null values.

Karunakaran

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-04-12 : 11:11:09

Hi
Are you sure you've got an identity on that column!?
Seriously though, make sure you've removed the column from the transformation's destination columns collection (not just from the ActiveX transformation script).
If this is the case, this approach will definitely work.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources