Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65

    Unanswered: Not importing all records-using DTS

    Hi!

    I'm using DTS to import a flat ASCII file. I checked the primary key(telephone number) in the flat file to make sure it's unique. But for some reason when I import is using the DTS package, I'm missing 4 records. I checked one of the missing records and I don't see any problems with it. Is there a way I can debug the DTS package so it spits out a message/reason why it's not importing the record?

    Thanks.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Jigsatics
    Is there a way I can debug the DTS package so it spits out a message/reason why it's not importing the record?

    Thanks.
    Open the properties page for the Data Pump task (I'm assuming it's a data pump, but you'd do the same for a Data Driven Query Task). Click on the Options tab. Specify a file name and what options you want (for SQL 2000 packages, you can specify that the source row values be output.

    Run the package and look for your errors.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65
    Thanks.

    I was able to do your suggestions and got this log from the DTS execution. My primary key is the phone number. I checked the input file for duplicates in the phone number field and found out there's no dups so I don't know on which record it's getting that error.

    DATA TRANSFORMATION SERVICES: Data Pump Exception Log


    Package Name: Mailout DB: Import New Completions
    Package Description: Imports new records and appends it to Mailout DB
    Package ID: {35D1FB66-1C48-4F0D-A371-95CC3046BC42}
    Package Version: {272C083B-FF5A-4867-A97D-D8E1E4CA236B}
    Step Name: Copy Data from mailout_comps to [mailout].[dbo].[Mailout DB] Step

    @@ExecutionStarted

    Execution Started: 11/15/2005 4:20:44 PM

    @@LogSourceRows: D:\Inetpub\wwwroot\Mailout\ImportLogs\ImportError. txt.Source
    @@LogDestRows: D:\Inetpub\wwwroot\Mailout\ImportLogs\ImportError. txt.Dest

    @@ErrorRow: 737
    Error at Destination for Row number 737. Errors encountered so far in this task: 1.

    Error Source: Microsoft OLE DB Provider for SQL Server
    Error Description:The statement has been terminated.
    Error Help File:
    Error Help Context ID:0


    Error Source: Microsoft OLE DB Provider for SQL Server
    Error Description:Violation of PRIMARY KEY constraint 'PK_Mailout DB'. Cannot insert duplicate key in object 'Mailout DB'.
    Error Help File:
    Error Help Context ID:0
    @@SourceRow: Not Available

    @@ExecutionCompleted

    Execution Completed: 11/15/2005 4:20:44 PM

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65
    Hi!

    I think I know what's causing the problem. Now, I need help to solve the problem.

    Here's the scenario:

    I have a flat file database. Each record contains personal information such as address, city, etc. The last field in each record is the postal code. For some records, they don't have the postal code so they end up having only 221 characters in that record compared to 227 characters if they do have it.

    For some reason, when it encounters a record with no postal code, it skips the next record.

    How can I fix this problem in the DTS?

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Can you post the data that's in these files:

    @@LogSourceRows: D:\Inetpub\wwwroot\Mailout\ImportLogs\ImportError. txt.Source
    @@LogDestRows: D:\Inetpub\wwwroot\Mailout\ImportLogs\ImportError. txt.Dest

    Also, can you post the DDL for the destination table?

    How have you defined the source file in your DTS package? Is it fixed width or delimited?

    Brett K. will tell you to use BCP!

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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