DTS package

Mapa3matuk

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

Phil Brammer

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.

Mapa3matuk

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

Phil Brammer

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)

Mapa3matuk

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

many thanks

Dmitry

Phil Brammer

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