DTS package to check if table exists

Hi all,

I have created a DTS package in SQL Server that takes Excel file and updates the database. The problem that i run into is - sometimes XLS file does not have any records in the table, and in this case i do not want DTS to execute and update the database.

The question is how to check ( i think usign Active X) if the exel table has any values in there or if it is blank?

a lot of thanks in advance,

Dmitry

yeah, and by the way,

when i execute the DTS i can see how many records are taken from XLS and copied into the database, if i can just somehow check that number, retreive that varible... that would solve the problem.

thanks again,

Dmitry

|||

It is often a good idea to import data into 'holding tables' (duplicate schema to the production tables), do whatever data cleanup/manipulation is required, and then move the data to the final tables.

With that process, it is easy to determine rowcount, duplications, etc, before running other code.

|||

ok...sounds ok, i have never done them before though... can you link an example or something..

many thanks

DTS package

Hi guyz,

I have created a DTS package that takes XLS file and updates the database. The problem that i run into is - sometimes XLS file does not have any records in the table, and in this case i do not want DTS to execute and update the database.

The question is i believe is how to check ( i think usign Active X) if the exel table has any values in there or if it is blank?

many thanks in advance,

Dmitry

Edit: Sorry, this is a response based on SSIS. I'm leaving it here as it might help someone using SSIS. If you are seeking DTS advice, please seek the DTS group: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg&hl=en

This isn't the most elegant solution, but it will work and can be done without programming.

Since the file exists, it just might not have any records in it, you'll be creating two data flows. The first data flow connects to the file and hooks to a Row Count Transformation. That's it. Store the results of the Row Count Transformation into a package-scoped variable.

The second data flow is what you've already got built, just use it as is.

Connect the two data flows with a precedence constraint and use the expression "@.Your_Variable > 0" to execute the second data flow if there were any records coming out of the Excel file.

|||

this is a very good idea, thank you very much...

but how would i count rows in the excel, can u maybe link some example please, or a code snippet?

many thanks in advance

|||If you are using SSIS, there is an Excel Source connection which you would hook up to a Row Count Transformation. Two pieces, very simple. No code or formula required. However, it sounds like you are using DTS (the old product that SSIS replaces)|||

Yes, im using DTS. any idea how to do it using DTS?

many thanks

Dmitry

|||

Again, this is an SSIS forum. For DTS questions, please see the DTS group:

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg&hl=en