Hi,
i m facing this error when running DTS on IDENTITY(1,1) Field.
how can this field increment automatically ???
ERROR:
---------
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: 8004206A
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
