DTS failed to export result in CSV but mark succes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS failed to export result in CSV but mark succes

Author

Topic

mkmkmk2
Starting Member

3 Posts

Posted - 2010-07-08 : 03:46:00

Hi,

I have a DTS package which will be called from an application server remotely. It will output the query result into a text file(around 40MB per file). It works fine for a long time to output 2 files at a time. However when I added additional 2 more files in the package, it casue some problems. It sometime output the empty file with header only. I checked with the log and the system will mark success with no exception even it gave me a header only empty file.
And the step execution time will be smaller than in normal case
Total Step Execution Time: 0.266 seconds
Normally should be
Total Step Execution Time: 26.656 seconds

Anybody have an idea what's going on?
I tried to duplicate the original package for the new additional output files, it works fine. But I have no idea why it won't work for 4 but 2 only. Is that the server performance issue? But it didn't flow error for timeout and similars message. Please help.

====================
Log with empty output file
====================
The execution of the following DTS Package succeeded:

Package Name: DTS_CSV_OUTPUT
Package Description: (null)
Package ID: {04FBAE6D-1FB5-4872-A106-9249DC82EB26}
Package Version: {ADA79B46-422D-47AC-8086-88C5CCD669E9}
Package Execution Lineage: {3E1202E9-D79C-40EE-93FC-77C63B80206C}
Executed On: APPSERVER
Executed By: USER
Execution Started: 6/30/2010 2:31:19 PM
Execution Completed: 6/30/2010 2:32:34 PM
Total Execution Time: 75.891 seconds

Package Steps execution information:

Step 'DTSStep_DTSActiveScriptTask_8' succeeded
Step Execution Started: 6/30/2010 2:31:19 PM
Step Execution Completed: 6/30/2010 2:31:19 PM
Total Step Execution Time: 0.172 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDataPumpTask_1' succeeded
Step Execution Started: 6/30/2010 2:31:19 PM
Step Execution Completed: 6/30/2010 2:32:22 PM
Total Step Execution Time: 62.86 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDataPumpTask_3' succeeded
Step Execution Started: 6/30/2010 2:32:22 PM
Step Execution Completed: 6/30/2010 2:32:34 PM
Total Step Execution Time: 12.828 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDataPumpTask_2' succeeded
Step Execution Started: 6/30/2010 2:31:19 PM
Step Execution Completed: 6/30/2010 2:31:19 PM
[color=#c3c3c3]Total Step Execution Time: 0.266 seconds[/color]
Progress count in Step: 0

Step 'DTSStep_DTSDataPumpTask_4' succeeded
Step Execution Started: 6/30/2010 2:31:19 PM
Step Execution Completed: 6/30/2010 2:31:31 PM
Total Step Execution Time: 11.969 seconds
Progress count in Step: 0
****************************************************************************************************

====================
Log with output file
====================
The execution of the following DTS Package succeeded:

Package Name: DTS_CSV_OUTPUT
Package Description: (null)
Package ID: {04FBAE6D-1FB5-4872-A106-9249DC82EB26}
Package Version: {ADA79B46-422D-47AC-8086-88C5CCD669E9}
Package Execution Lineage: {B824F3A0-55E9-4070-9DDF-814B39F40458}
Executed On: APPSERVER
Executed By: USER
Execution Started: 6/30/2010 2:27:19 PM
Execution Completed: 6/30/2010 2:29:08 PM
Total Execution Time: 108.578 seconds

Package Steps execution information:

Step 'DTSStep_DTSActiveScriptTask_8' succeeded
Step Execution Started: 6/30/2010 2:27:19 PM
Step Execution Completed: 6/30/2010 2:27:19 PM
Total Step Execution Time: 0.094 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDataPumpTask_1' succeeded
Step Execution Started: 6/30/2010 2:27:19 PM
Step Execution Completed: 6/30/2010 2:28:52 PM
Total Step Execution Time: 92.594 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDataPumpTask_3' succeeded
Step Execution Started: 6/30/2010 2:28:52 PM
Step Execution Completed: 6/30/2010 2:29:08 PM
Total Step Execution Time: 15.844 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDataPumpTask_2' succeeded
Step Execution Started: 6/30/2010 2:27:19 PM
Step Execution Completed: 6/30/2010 2:27:46 PM
Total Step Execution Time: 26.656 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDataPumpTask_4' succeeded
Step Execution Started: 6/30/2010 2:27:19 PM
Step Execution Completed: 6/30/2010 2:27:54 PM
Total Step Execution Time: 34.562 seconds
Progress count in Step: 0
****************************************************************************************************

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-08 : 04:39:45

If there is no error then there is no data found to be exported.
Do you have a SELECT as input for the datapump task?
Then please execute the select statement in a query window so you can see the result.

No, you're never too old to Yak'n'Roll if you're too young to die.

mkmkmk2
Starting Member

3 Posts

Posted - 2010-07-09 : 03:59:18

Hi webfred,

Thanks for your reply. Actually both results are executed from the same source, but it just behavor differently. I run it for a few times, it sometime runs ok, but sometime it only output the header with no result.

The way I implement it by providing some parameters to the DTS package, and it take the parameters and pass it to all 4 datapump sql stmt for the result CSV files. However, the original 2 datapump files works fine always, but the new 2 datapump I duplicated behavor abonormal as state.

I have no idea why it act like this as 4 sql stmt are almost the same with the same inputted parameter, it just output the file in different formats. If it was the performance issue, it should flow error instead of mark success. Accroding to the short execution time, I am thinking if it somehow passed the empty value to the new 2 sql stmt, such that the sql result is empty and finish in a shorter time.

quote:
Originally posted by webfred

If there is no error then there is no data found to be exported.
Do you have a SELECT as input for the datapump task?
Then please execute the select statement in a query window so you can see the result.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 04:24:21

Maybe you have to correct the workflow?
As we can see datapump_3 is running after datapump_1 has finished but
datapump_2 and datapump_4 are starting the same time as datapump_1

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 04:25:27

What is the ScriptTask doing and will it be finished before the datapumps are starting?

No, you're never too old to Yak'n'Roll if you're too young to die.

mkmkmk2
Starting Member

3 Posts

Posted - 2010-07-09 : 06:15:13

Hi webfred,

ScriptTask is a common task to collect the inputted parameters and forward to those 4 datapump task.
As you said I think I should make it run in seq instead of parallel.

quote:
Originally posted by webfred

What is the ScriptTask doing and will it be finished before the datapumps are starting?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 07:58:34

yes - good luck

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources