?
Solved

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

Posted on 2007-10-01
27
Medium Priority
?
224 Views
Last Modified: 2013-11-30
I have created a DTS package.  On Friday it worked well - creatingh a file for each of the destimations defined in it.

However, today I can only get it to create four (4) of the files.  It says it is successfully completing all 8 of the tasks.  Also, I defined a workflow where it would not even perform a task unless a specific task had completed successfully.

Thanks for your help on this issue.
0
Comment
Question by:itatahh
  • 14
  • 12
27 Comments
 
LVL 14

Expert Comment

by:twoboats
ID: 19991693
More information needed.
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19991730
Check the event viewer on the server to see if it is showing any errors when the package is run.
0
 

Author Comment

by:itatahh
ID: 19991734
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.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19991775
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.
0
 

Author Comment

by:itatahh
ID: 19991780
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  
0
 

Author Comment

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

0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19992139
That is interesting.  Have you tried executing one step at a time and checking the files after each step?
0
 

Author Comment

by:itatahh
ID: 19992223
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.
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19992235
How are you creating your files in the DTS?
0
 

Author Comment

by:itatahh
ID: 19992396
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.
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19993028
OK, so what are you using as a destination (text file destination, excel file, other)?
0
 

Author Comment

by:itatahh
ID: 19993042
text file.
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19993079
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?
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19993112
It seems I am wrong in this instance, it worked for me....
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19993130
So the task that joins the 2 together is a Data Transform Task?
0
 

Author Comment

by:itatahh
ID: 19993156
Right.
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19993187
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?
0
 

Author Comment

by:itatahh
ID: 19993216
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.
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19993269
I guess I am wondering how you are setting the on completion workflow if you are pointing it back to the same source?
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19993301
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.
0
 

Author Comment

by:itatahh
ID: 19993316
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.
0
 

Author Comment

by:itatahh
ID: 19993333
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.
0
 
LVL 14

Accepted Solution

by:
NBSO_ISS earned 500 total points
ID: 19993339
Have you checked all of the Data transform tasks to make sure that they are set up properly (check and preview the source, check the destination tab, check the transforms themselves to make sure nothing is wrong with the transform itself?
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19993393
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.
0
 

Author Comment

by:itatahh
ID: 19993439
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.
0
 

Author Comment

by:itatahh
ID: 19993494
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.  :-)
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 19994019
No problem.  
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question