Hi,

I am doing a DTS transformation from SQL Server table to Access table
in my ASP page. When the DTS Package runs from ASP Page, I get the
following error:

Microsoft Data Transformation Services (DTS) Data Pump error '80042025'

DTSTransformCopy: Schema validation failed; see Extended Error
information.

The code I am doing to do transformation is below:


ExceptionFileName = Server.MapPath("\temp\Exceptions.mdb")

'--- Declare the DTS objects
Dim objPackage, objTask, objStep, objDataPump, objTransform,
objConnectDest, objConnectSrc

'--- Create the DTS Package
Set objPackage = Server.CreateObject("DTS.Package2")

--- Create our connection to the database
Set objConnectSrc = objPackage.Connections.New("SQLOLEDB.1")
With objConnectSrc
.ID = 1
.DataSource = "servername"
.Password = "password"
.UserID = "userid"
End With
objPackage.Connections.Add objConnectSrc

'--- Create our connection to source database/file
Set objConnectDest =
objPackage.Connections.New("Microsoft.Jet.OLEDB.4. 0")
With objConnectDest
.ID = 2
.DataSource = ExceptionFileName
End With
objPackage.Connections.Add objConnectDest


'--- Add our datapump step and task to the package
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSDataPumpTask")
Set objDataPump = objTask.CustomTask
objDataPump.Name = "DataPumpTask"
With objStep
.Name = "DataPumpStep"
.TaskName = objDataPump.Name
.ExecuteInMainThread = True
End With
objPackage.Steps.Add objStep

'--- Set our datapumptask parameters up
With objDataPump
.SourceConnectionID = 1
.DestinationConnectionID = 2
.SourceObjectName = "[webdb][dbo].[Exceptions]"
.DestinationObjectName = "Exceptions"
End With
objPackage.Tasks.Add objTask

'Create and add the transformation.
Set objTransform =
objDataPump.Transformations.New("DTS.DataPumpTrans formCopy")
objTransform.Name = "Transform"
objTransform.TransformFlags = DTSTransformFlag_AllowLosslessConversion
objDataPump.Transformations.Add objTransform
objPackage.FailOnError = True

strRes = ""

objPackage.Execute
bDTSresult = "True"


Now, when I go through designer, and creates a DTS Package with simple
one on one transformation and run it, the package runs fine. It is just
not running through my code.

Any help would be really appreciated. What is the problem here and how
can I fix it? How do I see the extended error details?

Thanks,
Boris

Why do you need to builid the package each time? Can you not build a pckage
in designer and call that? You can change properties in your package no
problems.

Where are your SourceColumns and DestinationColums?

--



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


"Boris" <supermanreloaded (AT) gmail (DOT) com> wrote

Allan,

I need to build a new package every time because I do not know what the
source table structure looks like. The source table may have different
number of columns each time. Based on what source table is, I create a
destination table dynamically. That is why I can not have a package in
the designer because I do not know what columns should be mapped.

Any suggestions?

Thanks,
Boris

Allan Mitchell wrote:

OK so you need to do this dynamically. You can of course let the package do
this for you. Granted it is "code heavy".

Send us a mail and I'll send you an example of doing this

Take a Query from a DP task
Build the Excel Table
Reconstruct the DataPump task

I would post here but wrapping and size would destroy the meaning.



--



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


"Boris" <supermanreloaded (AT) gmail (DOT) com> wrote

Allan,

Thanks again. My email address is supermanreloaded at gmail dot com.

I would appreciate for your help,

Thanks,
Boris

Allan Mitchell wrote:

on its way

--



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


"Boris" <supermanreloaded (AT) gmail (DOT) com> wrote