I'm having intermittent problems with a DTS package randomly generating empty
files. My package is actually split up into 4 packages, due to size, and
each package calls the next package in turn. The first 2 packages work fine
(no exports) gathering data from an external source and processing in SQL.
The 3rd and 4th packages contain all of the destination files, and that's
where things go awry. I have all the transforms and exports processing, for
each business data type I'm dealing with, in turn and sometimes no files are
empty, sometimes some are empty and sometimes others are empty. I've stepped
through each package and verified the workflow is processing properly and
that there are records to export prior to the transform occurring. No matter
what I try, I can't find a cause. I've even gone so far as to re-create
everything from scratch ( a good 2 days worth of work) on another server and
I still get these random empty files.

Any suggestions?

Is it possible that at these random times there are no rows to process?
Is it possible that maybe an error has been thrown and therefore the rows do
not move?

I hate random problems but if we can pin it down to something that is not
random.

Maybe you can log the amount of rows expected in each transform to a
location (SQL Server) and then if no rows get processed and you have numbers
in the reported location then there is a problem.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Eric" <Eric (AT) discussions (DOT) microsoft.com> wrote

Unfortunately, I'm not that lucky. The rows are there prior to the transform
to the .csv file occurring, but they aren't copied to the .csv file. I've
serialized the entire process to go record type (businsess data type I should
say, different sales recs) by record type and stage from the import, check
for duplicates, and post only new rows to the export table. Then the
transform task queries for those records which have not yet been exported
(which in all but a very few record types, there will always be records to
export on each run) , performs the transform in VBScript and exports nothing
to the .csv. I've also writtent a quick and dirty script that I've added to
the last package in the sequence which checks the file size on all of the
exported files and for any that are 0 queries the transform source table.
If it returns record count > 0 for today's exports, it emails me letting me
know that the export failed. Yesterday only 1 file was affected, today it
was 3, and the one from yesterday was not one of them.

Any other ideas?

"Allan Mitchell" wrote:

Sounds nasty and it also sounds as though it should work. If you are
getting it regularly just not consistently is there any chance you can run
it manually for a day or two (presuming your current process is to run it
scheduled). Maybe there are intermittent permissions issues?



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Eric" <Eric (AT) discussions (DOT) microsoft.com> wrote