Suggestions on package

[unclerico]

I have an Access database that our corporate headquarters kicks out every night. Currently I have a DTS package in SQL 2000 that has about 40 different transform tasks that takes all of the data from the individual tables in the Access database and copies it into the SQL box. My dilema is, recently the Access database has been having some errors in it where some of the tables will not have any data in them and when the DTS Package executes it truncates the current table in SQL Server and then proceeds to import nothing leaving the SQL database in an inconsistent state. What I need is to check each table in the Access database to make sure that it has data in it and if it does, only then should the transform task for that specific table kick off. I know that I can create 40 different tasks to check the tables but this seems to be inefficient; does anyone think that there is an easier way?? I'm all ears. thanks.

 

[Crowley16]

you could use a cursor on the system table to step through all the tables and do a count on each one, and execute your individual statements from the cursor depending on the count

although if your source is an access database, the hidden table is MSysObjects, which also includes other things, so you'll have to find out which category are tables...

--------------------
Procrastinate Now!

 

[123FakeSt]

This would be much easier if you had one dynamic data pump task for the tables rathe than a task for each.. Then:

1. Store the first (or later current) table as a global variable(start with min(ID) using 'SELECT MIN(ID) WHERE ID > 'global variable logic

2. Loop through all of the tables in the msysobjects. Look up making a DTS package loop in this forum for how to do that.

3. Query rowcount

4. If rowcount > 0 then create SQL for and run transform data task

5. Repeat until your SELECT statement runs out of tables to process

6. End package



The early bird gets the worm, but the second mouse gets the cheese.