DTS import Question

Question

DTS import Question

Connor Brannigan

SSC Rookie

Points: 29
More actions
December 29, 2004 at 4:28 am

#165611

Hi there,
I have created a DTS package to import files to a database on a monthly basis (one at a time). At the start of every month 10 files are imported to an IDF table. The data imported is:
Market ID
Warehosue ID
Brochure Code
Brochure QTY
There is a relationship formed between this IDF table and a Brochure table based on Brochure Code. If I try to import a file that has a brochure code in it that is not present in the brochure table, the file does not import (which is what i want).
Now the question.
Is there any way of determining which brochure code in the file i am importing is the one causing the problem. At the moment, each file contains over 100 records and so it is a bit of a chore to try and search through them all to locate the one (or more) errors with the brochrue codes. Any suggestion's would be much appreciated as I am a newbie at this and so not too sure of all the weird and wonderful thigns that DTS is capable of doing, yet.
Thanks for all your help,
Connor

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

One way of approaching this is to import the files into a new intermediate table (which has no constraints) and to use standard SQL on this table to perform validation checks prior to updating from it. You will easily be able to find the exceptions this way.

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Connor Brannigan SSC Rookie Points: 29 More actions · Answer 2

I have been thinking much the same myself all day, i'll give it a blast and post a reply with what happens.

Thanks again

Connor

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 3

Another option would be to use an ActiveX transform. In the transform use a Lookup query to validate the Brochure Code. Have an Error Function using an ADO connection to a Exception table in SQL. If the Brochure code is invalid drop to the Error Function, write the 4 source values to the Exception table and when it returns to the Main function use the SkipInsert status.

This would allow the package to complete every time and give you one table to look up any exceptions.

Kunal Jain Grasshopper Points: 10 More actions · Answer 4

You can have a Temp table in between without any constraints and then have it query against the brochure table and send you the missing code in mail. This will also allow you to make the package fail at any step.

Connor Brannigan SSC Rookie Points: 29 More actions · Answer 5

Hi there, been off work so only back in to get truly stuck into this. Managed to get the stuff sorted no bother. used a temp table to validate the information and output the invalid data to another table. Making everything so much easier.

Thanks for all your help

Connor (",)