DTS Fail when IDENTITY(1,1)

Hi,

i m facing this error when running DTS on IDENTITY(1,1) Field.
how can this field increment automatically ?

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (80040e21): Insert error, column 14 ('s_no', DBTYPE_I4), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.) (Microsoft OLE DB Provider for SQL Server (80040e21): Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.)
Step Error code: 8004206ADon't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.

-PatP|||there is a option button (check box) in your transformation task that says
"enable identity insert"
change that.|||Does the table have existing data?

Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?

Does this import need to update as well as add data (or delete)?|||Hi,
Actually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.

e.g

Table1
mfg_no varchar(255)
Product_Name varchar(255)
Description varchar(255)

Table2
s_no int IDENTITY(1,1) Not Null {constraint for identity seed 1)
mfgno varchar(255)
ProductName varchar(255)

i just have to transfer data column (mfgno and product name) from Table1 to Table 2. the problem is that when i run dts its not inserting "s_no" automatically in Table 2 (i hope SQL server should handle this but its not working). if i remove constraint of identity (s_no)and make it allow Null then data transfer successfully.

i m using DTSDataPump in package to transform data.

waiting for ur reply|||Does the table have existing data?
NO

Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?
NO there is no existing keys in SourceTable

Does this import need to update as well as add data (or delete)?
No it just Add data|||ctually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.That is what I thought... Just view the transform code, and remove any transformation for the IDENTITY column. It ought to be pretty simple.

-PatP|||Don't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.

-PatP

yes i have't include any transformation for the identity column and i m wonder why db engine not handle this :mad:|||I don't get it...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE Table1(mfg_no varchar(255), Product_Name varchar(255), [Description] varchar(255))
CREATE TABLE Table2(s_no int IDENTITY(1,1) Not Null, mfgno varchar(255), ProductName varchar(255))
GO

INSERT INTO Table1 (mfg_no, Product_Name, [Description])
SELECT 'x002548','Gas Powered Blenders', 'The Ultimate is finally here' UNION ALL
SELECT 'x002548','Shake, rattle and Roll Mixers', 'Get the Party started' UNION ALL
SELECT 'x002548','1.75 litre sampler packages', '6 Bottles of unconciousness' UNION ALL
SELECT 'x002548','Margarita Glasses', 'Glasses, Hell you wont be able to see, son' UNION ALL
SELECT 'x002548','Hide away Car Key Chain', 'inebriation Dector - will hide your keys on you'
GO

SELECT * FROM Table1
GO

INSERT INTO Table2(mfgno, ProductName)
SELECT mfg_no, Product_Name FROM Table1
GO

SELECT * FROM Table2
GO

SET NOCOUNT ON
DROP TABLE table1
DROP TABLE table2
GO|||I don't get it...
[/code]

u run direct quries its working fine :( but if i run dts package then it fails :(|||That's what I don't get.

Why are you bothering with a DTS package at all?|||That's what I don't get.

Why are you bothering with a DTS package at all?

i want to tranfer data from one table to another on schedule basis so that i need a DTS package that tranfer only those column data that i want to tranfer.

whole dts working fine its transform the desired column to anothe table but when identiy column is removed cuz destination table has a unique column type int and identity seed 1.

:( this is sample code u can run it . if u remove constraint on s_no it will work fine. try this

CREATE TABLE [DTS_UE].[dbo].[NorthwindProducts] (
[s_no] [int] IDENTITY(1,1) Not NULL ,
[ProductName] [nvarchar] (40) NULL ,
[CategoryName] [nvarchar] (25) NULL ,
[CompanyName] [nvarchar] (40) NULL )
This is the Visual Basic code for the application:

Public Sub Main()
'Copy Northwind..Products names, categories, suppliers to DTS_UE..NorthwindProducts.
Dim objPackage As DTS.Package2
Dim objConnect As DTS.Connection2
Dim objStep As DTS.Step2
Dim objTask As DTS.Task
Dim objPumpTask As DTS.DataPumpTask2
Dim objTransform As DTS.Transformation2
Dim objLookUp As DTS.Lookup
Dim objTranScript As DTSPump.DTSTransformScriptProperties2
Dim sVBS As String 'VBScript text

Set objPackage = New DTS.Package
objPackage.FailOnError = True
objPackage.LogFileName = "C:\Temp\TestConcurrent.Log"

'Establish connections to data source and destination.
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 1
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 2
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect

'Create copy step and task, link step to task.
Set objStep = objPackage.Steps.New
objStep.Name = "NorthwindProductsStep"
Set objTask = objPackage.Tasks.New("DTSDataPumpTask")
Set objPumpTask = objTask.CustomTask
objPumpTask.Name = "NorthwindProductsTask"
objStep.TaskName = objPumpTask.Name
objStep.ExecuteInMainThread = False
objPackage.Steps.Add objStep

'Link copy task to connections.
With objPumpTask
.SourceConnectionID = 1
.SourceSQLStatement = _
"SELECT ProductName, CategoryID, SupplierID " & _
"FROM Northwind..Products"
.DestinationConnectionID = 2
.DestinationObjectName = "[DTS_UE].[dbo].[NorthwindProducts]"
.UseFastLoad = False
.MaximumErrorCount = 99
End With

'Create lookups for supplier and category.
Set objLookUp = objPumpTask.Lookups.New("CategoryLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CategoryName FROM Northwind..Categories " & _
"WHERE CategoryID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp
Set objLookUp = objPumpTask.Lookups.New("SupplierLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CompanyName FROM Northwind..Suppliers " & _
"WHERE SupplierID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp

'Create and initialize rowcount and completion global variables.
objPackage.GlobalVariables.AddGlobalVariable "Copy Complete", False
objPackage.GlobalVariables.AddGlobalVariable "Rows Copied", 0
objPackage.ExplicitGlobalVariables = True

'Create transform to copy row, signal completion.
Set objTransform = objPumpTask.Transformations. _
New("DTSPump.DataPumpTransformScript")
With objTransform
.Name = "CopyNorthwindProducts"
.TransformPhases = DTSTransformPhase_Transform + _
DTSTransformPhase_OnPumpComplete
Set objTranScript = .TransformServer
End With
With objTranScript
.FunctionEntry = "CopyColumns"
.PumpCompleteFunctionEntry = "PumpComplete"
.Language = "VBScript"
sVBS = "Option Explicit" & vbCrLf
sVBS = sVBS & "Function CopyColumns()" & vbCrLf
sVBS = sVBS & " DTSDestination(""ProductName"") = DTSSource(""ProductName"") " & vbCrLf
sVBS = sVBS & " DTSDestination(""CategoryName"") = DTSLookups(""CategoryLU"").Execute(DTSSource(""CategoryID"")) " & vbCrLf
sVBS = sVBS & " DTSDestination(""CompanyName"") = DTSLookups(""SupplierLU"").Execute(DTSSource(""SupplierID"").Value) " & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Rows Copied"") = CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
sVBS = sVBS & " CopyColumns = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf

sVBS = sVBS & "Function PumpComplete()" & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Copy Complete"") = True" & vbCrLf
sVBS = sVBS & " PumpComplete = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf

.Text = sVBS
End With
objPumpTask.Transformations.Add objTransform
objPackage.Tasks.Add objTask

objPackage.Execute

End Sub