DTS execution problem - shows successful but results in only 1/2 of the text files being created.

More information needed.

Check the event viewer on the server to see if it is showing any errors when the package is run.

The DTS package I created takes 8 different tables and outputs them to a text file with a CSV format.

When I run it manually, from inside the designer or externally in the DTS local packages window, it gives me the indication that all eight tasks have completed (the status column shows completed along witht he number of rows/records each task came up with).

However when I look int eh folder the csv files are supposed to be written to only 4 files are getting created.  (I have even deleted them and reran it to ensure my eyes were seeing the results I have described - and again it only outputs 4 of the 8 files.

Which 4 files are you getting?  Is it the first 4, the last 4, every second file or is it random?  If there is a pattern, the new files may be overwriting files that are created previously.  

Also, try stepping through each taks in your workflow one at a time and see what happens as each task runs.

Okay,  here is the results on the event viewer:

Event Type:      Information
Event Source:      DataTransformationServices
Event Category:      None
Event ID:      80
Date:            10/1/2007
Time:            11:15:35 AM
User:            N/A
Computer:      VCK1
Description:
The execution of the following DTS Package succeeded:

Package Name: PM_Feed_Export
Package Description: (null)
Package ID: {1B32D408-2F97-4501-A967-762AEE4164F3}
Package Version: {AD36C87E-1B17-4E89-9F1C-AC75ED03F5EF}
Package Execution Lineage: {83541287-38D3-43BF-A2CD-CA7480C9A39C}
Executed On: VCK1
Executed By: Administrator
Execution Started: 10/1/2007 11:15:32 AM
Execution Completed: 10/1/2007 11:15:35 AM
Total Execution Time: 3.625 seconds

Package Steps execution information:


Step 'DTSStep_DTSDataPumpTask_1' succeeded
Step Execution Started: 10/1/2007 11:15:32 AM
Step Execution Completed: 10/1/2007 11:15:32 AM
Total Step Execution Time: 0.093 seconds
Progress count in Step: 1

Step 'DTSStep_DTSDataPumpTask_2' succeeded
Step Execution Started: 10/1/2007 11:15:32 AM
Step Execution Completed: 10/1/2007 11:15:32 AM
Total Step Execution Time: 0.078 seconds
Progress count in Step: 32

Step 'DTSStep_DTSDataPumpTask_3' succeeded
Step Execution Started: 10/1/2007 11:15:32 AM
Step Execution Completed: 10/1/2007 11:15:32 AM
Total Step Execution Time: 0.079 seconds
Progress count in Step: 175

Step 'DTSStep_DTSDataPumpTask_4' succeeded
Step Execution Started: 10/1/2007 11:15:32 AM
Step Execution Completed: 10/1/2007 11:15:32 AM
Total Step Execution Time: 0.062 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDataPumpTask_5' succeeded
Step Execution Started: 10/1/2007 11:15:32 AM
Step Execution Completed: 10/1/2007 11:15:33 AM
Total Step Execution Time: 1.171 seconds
Progress count in Step: 412

Step 'DTSStep_DTSDataPumpTask_6' succeeded
Step Execution Started: 10/1/2007 11:15:32 AM
Step Execution Completed: 10/1/2007 11:15:32 AM
Total Step Execution Time: 0.125 seconds
Progress count in Step: 786

Step 'DTSStep_DTSDataPumpTask_7' succeeded
Step Execution Started: 10/1/2007 11:15:32 AM
Step Execution Completed: 10/1/2007 11:15:35 AM
Total Step Execution Time: 3.14 seconds
Progress count in Step: 412

Step 'DTSStep_DTSDataPumpTask_8' succeeded
Step Execution Started: 10/1/2007 11:15:32 AM
Step Execution Completed: 10/1/2007 11:15:34 AM
Total Step Execution Time: 2.234 seconds
Progress count in Step: 1735  

It is giving me the files generated in the 1st, 2nd, 3rd, and 5th step(s).

That is interesting.  Have you tried executing one step at a time and checking the files after each step?

I opened the DTS package and tried each one.  All of the files that run fine on the job run fine individually.
All of the steps that fail to produce a file say they they completed successfully but still do not produce a file.

I looked at the DTS log and there are no errors at all.  All eight rows show a successful completion.

How are you creating your files in the DTS?

I have not done much in DTS so my descriptions may be a bit simple, but here goes:

I identify the source database and table and then the destination name/location/type.  Then I create the tasks that joins the two together.  (The source database is the same for all of the steps - only the tables are different.

The source tables are populated with a different DTS package - that works 100% of the time.

OK, so what are you using as a destination (text file destination, excel file, other)?

text file.

Correct me if I am wrong (it wouldn't be the first time), but don't you have to have a text file (empty) matching the filename provided in the datasource in order for this to work?

It seems I am wrong in this instance, it worked for me....

So the task that joins the 2 together is a Data Transform Task?

Right.

And are you using the same database, but a different source connection for each?

That way you would use an On Success workflow from the destination of one connection to the source of the next?

I am using the sane source to pull from for all of the tasks.

If I do one at a time inside of the designer and it tells me successful wouldn't that show that it is completed.

I'll create another DTS package just for one of the files it is not producing and see what it does then.  It won't explain how it was able to work last week when I created it originally.

I guess I am wondering how you are setting the on completion workflow if you are pointing it back to the same source?

I would try removing the workflow or setting up several connection objects all connecting to the same database so you can use workflow.  That way you can connect from the first destination to the 2nd datasource, then from the second destination to the 3rd datasource... etc.

It could be that the workflow is getting confused because it is continually connecting to the same datasource and does not know where to go from there.  The workflow needs to be linear.

To be honest, although I can see what the workflow control enable one to do, I did not use it prior to having problems with this first thing this morning.

I see what you are saying, now that I looked back at the DTS package and see that the left column points to the source - not the completion of a particular destination (which is what I misread it as).

So, I removed all of the workflow controls and saved it and --- it still is not producing (outputting) the other four (4) files.

I tried having just one source and one destimation (one of the ones it is not creating) and it told me it was successful but it did not produce the file.

The other thing I would check is the destination.  Make sure the folder you are saving to exists.  One issue I have run into on occasion is trying to run a DTS package remotely when all of the folders are on the server.

I have to use remote desktop in this case to get the files to come out correct.

Even if there is nothing in the tables, an empty file should be created unless there is something wrong with the transform or the destination does not exist.

I am using a shared drive that is external from both my pc and the server.  I use the same drive letter to point to the folder.  Four of them are getting created there.

One question I have as I examine each of the pieces of the DTS:  When looking at the Transform Data Task List I preview and test both the source and destination.  I get all of the columns on the preview of the source, but I only get the first column on the Test Results in the Transformation.  Note that I get this same single-column result on the files that do and those that don't make it to the folder.

Alright, yes my face is red...

As I went back in to look at all of the details, the four files not being created were missing the drive letter and path to the correct folder.

I do not know what caused it to lose those - since I know I ran it a dozen times on Friday - but, that is what the problem was.

Thanks alot for your help.  Sometimes the simplest things get overlooked.  :-)

No problem.