Fail package if lookup table not complete 1

[Glowworm27]

I have a DTS package that imports several text files. This has been working fine, but we found out that if one of our lookup tables does not contain the rows needed for one of the inner joins that data never gets imported into the tables.

I want to be able to run a query and return some indicator can be True or False, or a recordset. It doesnt matter, just something that I can use to determine if the lookup table is complete or not. If the lookup table is missing rows, I want to fail the package.

If the lookup table is correct then process the package normaly.

Here is a query that will return rows if the lookup table is missing the rows.

Select T.Storenumber from TmpInvoices T Where Storenumber Not in (Select S.Storenumber from Stores)

running this query will tell me if I am missing stores in the lookup table "Stores" by returning records. It will return no records if the Stores table has all the storenumber records.

If I return any rows I want the package to fail. If No rows (records) are returned then the stores table is up to date, so I can process the files with the package.

Thanks
in advance
G

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[JohnDTampaBay]

Are you using the DTS lookup functionality through a data pump or importing raw data to Tmp tables then JOINing to the lookup table using an ExecuteSQL task?

Assuming you run the ExecuteSQL task method, you could insert new ExecuteSQL task between the import task and the JOIN task using Success constraints.

IF EXISTS(SELECT T.Storenumber
   FROM TmpInvoices T
   WHERE Storenumber NOT IN
      (SELECT S.Storenumber FROM Stores)
  ) RAISERROR('Lookup table error',16,1)

This is untested, I'm thinking the RAISERROR would cause the task to fail, thereby causing the package to fail.

Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Glowworm27]

Thanks JohnD

It works perfectly!

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net