DTS Transform Data Task to Excel Problem

DTS Transform Data Task to Excel Problem

Post by Brian K » Sat, 23 Feb 2002 07:25:25



I have a DTS Package that exports records to Excel using a query.  The
problem comes into play when the query needs to be dynamic.  I have placed
this SQL Script into a Stored Procedure which accepts a single parm.

I am trying to execute this sp in a transform data task as exec
usp_MontlyPhoneRecords ?   .  I have supposedly mapped the parmeter by
clicking on the parmeters button and chose my global variable that I wish to
use.

Any help would be appreciated.

Thanks

Brian

 
 
 

DTS Transform Data Task to Excel Problem

Post by Jun Chen[M » Sat, 23 Feb 2002 12:25:57


Hi,

A workaround would be saving the parameters into a table, then retreive the
parameters from the table in DTS package.

Thanks,
Jun Chen

This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

DTS Transform Data Task to Excel Problem

Post by Brian K » Sat, 23 Feb 2002 23:17:19


Jun,  The only problem is that the stored procedure that I am using is
running a dynamic script.  The parameter that I am passing in is a table
name.  Would that still work if stored in a table?  Do you know of a ActiveX
Script that would perform a data transformation task?

Brian

Brian

Quote:> Hi,

> A workaround would be saving the parameters into a table, then retreive
the
> parameters from the table in DTS package.

> Thanks,
> Jun Chen

> This posting is provided "AS IS" with no warranties, and confers no
rights.

 
 
 

DTS Transform Data Task to Excel Problem

Post by Jun Chen[M » Wed, 27 Feb 2002 15:51:48


Hi,

I don't understand the "dynamic script" clearly. Do you mean the schema of
the query resultset are dynamically changed? If so, the Transform Data Task
may not be able to fulfil your requirement.

If the schema of the query resultset does not change, you can try the
following idea:
1. Add an Execute SQL Task. The task will execute the dynamic query and
save the result into a table. You can use Global variables as the the
parameter for the SQL dynamic script. (For example: 'insert into tablename
exec spname ?'')
2. Add a Transform Data Task to transfer the table into the Excel
conneciton.

Thanks,
Jun Chen

This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

DTS Transform Data Task to Excel Problem

Post by Brian K » Sat, 02 Mar 2002 03:43:34


I should have explained a little better.  The dynamic script that I was
refering to is a dynamic sql statment.  I am passing in the MonthYear (ex.
Jan02) into the stored procdure.  The stored procedure uses this variable to
create some tables etc...

Thanks for your help

Brian

Quote:> Hi,

> I don't understand the "dynamic script" clearly. Do you mean the schema of
> the query resultset are dynamically changed? If so, the Transform Data
Task
> may not be able to fulfil your requirement.

> If the schema of the query resultset does not change, you can try the
> following idea:
> 1. Add an Execute SQL Task. The task will execute the dynamic query and
> save the result into a table. You can use Global variables as the the
> parameter for the SQL dynamic script. (For example: 'insert into tablename
> exec spname ?'')
> 2. Add a Transform Data Task to transfer the table into the Excel
> conneciton.

> Thanks,
> Jun Chen

> This posting is provided "AS IS" with no warranties, and confers no
rights.

 
 
 

DTS Transform Data Task to Excel Problem

Post by A W » Tue, 12 Mar 2002 16:48:15


Hi Brian and Jun,

I also encounter similar problem and I'm still searching for a solution.

Firstly, I will have the following connection

ActiveXScript(To get the 1st parameter.i.e date)--> On success-> SQL
database -->Transform Date (in the source tab, enter the sp to be
execute -note i) --> Excel or whatever format.

I've complete the 1st part in Active X script to generate the date in
the format that I want. However, I cannot dynamically changing the sql
exec statment as stated in note i in the active x script. Could anyone
know how to change the sql statement in the source tab of the data
transformation properties page? If so, we can execute the sp
dynamically.

Regrads,
AW

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

DTS Transform Data Task to Excel Problem

Post by Jun Chen[M » Thu, 04 Apr 2002 16:40:03


Hi,

To change the SQL sattement in the source tab of the data transformation
properties page, you can use an ActiveXScript task. For example:

   Function Main()
      dim oPackage
      dim oPump

           'Get a handle to the Package object.
        set oPackage = DTSGlobalVariables.Parent
        'Get a handle to the desired Datapump Task.
        set oPump = oPackage.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask  
        'Alter the datapump SQL statement.
        oPump.SourceSQLStatement = "select * from pubs..titles WHERE type _
     = ''" + DTSGlobalVariables("booktype") + "''"
        Main = DTSTaskExecResult_Success
   End Function

Set this task to run before the transform data task.  

Thanks,
Jun Chen

This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

DTS Transform Data Task to Excel Problem

Post by Brian Krame » Fri, 05 Apr 2002 00:07:11


Thanks Jun,  I think this will be just what I am looking for.

Brian Kramer

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. DTS Transform Data Task fails in job where DTS Execute SQL Task does not

I have experienced the following issue in several DTS packages.  I use
Transform Data Task to transfer data from a table on one SQL Server to a
table on another SQL Server.  The package executes successfully when run
manually and fails with the error shown below when run from a job.

However, if I use an insert statement in a Execute SQL Task to accomplish
the same task,  the package executes successfully when run manually and when
run from a job.

Can anyone explain why jobs running with Transform Data Task sometimes fail
as shown below?

 DTSRun:  Executing...   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnStart:
DTSStep_DTSDataPumpTask_1   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1;
1000 Rows have been transformed or copied.; PercentComplete = 0;
ProgressCount = 1000   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 2000
Rows have been transformed or copied.; PercentComplete = 0; ProgressCount =
2000   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 3000 Rows have been
transformed or copied.; PercentComplete = 0; ProgressCount = 3000   DTSRun
OnProgress:  DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 4000   DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 5000   DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 6000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 6000   ...  Process Exit Code 1.  The
step failed.

2. aio vps confusion

3. Passing parameters in Transform Data Task (DTS)

4. DATABASE HELP

5. Bug in DTS Transform Data Task window

6. OmniSQL Gateway/VLDB Horizontal Segmentation

7. Gripe: DTS Designer Transform Data Task

8. Access to Oracle and Informix DB

9. DTS Transform Data Task?

10. DTS variable to a distination column in a data transform task

11. DTS Package and Transform Data Task Object

12. Execute SQL Task from within a Transform Data Task - hangs

13. Multiple Transform Data Task Problem