Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2004
    Posts
    13

    Unanswered: 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: 8004206A

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    there is a option button (check box) in your transformation task that says
    "enable identity insert"
    change that.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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)?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2004
    Posts
    13

    DTS package

    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

  6. #6
    Join Date
    Sep 2004
    Posts
    13
    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

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by adeelnasim
    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

  8. #8
    Join Date
    Sep 2004
    Posts
    13
    Quote Originally Posted by Pat Phelan
    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

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't get it...

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Sep 2004
    Posts
    13
    Quote Originally Posted by Brett Kaiser
    I don't get it...
    [/code]
    u run direct quries its working fine but if i run dts package then it fails

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's what I don't get.

    Why are you bothering with a DTS package at all?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Sep 2004
    Posts
    13
    Quote Originally Posted by Brett Kaiser
    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(""Cat egoryID"")) " & vbCrLf
    sVBS = sVBS & " DTSDestination(""CompanyName"") = DTSLookups(""SupplierLU"").Execute(DTSSource(""Sup plierID"").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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •