Board index » delphi » D7 vs DTS III programming TASK

D7 vs DTS III programming TASK

Any one have everperiance programing the DTS !!!!!
not getting any replies and could really use some help
I am stuck on configuring the tast

MY D7 Code  VB code created by DTS follows

procedure DTSTask(dts : TPackage);
var
 dtsTask  : Task;
 dtsLookup : Lookup;
 dtsCustomTask1 : DataPumpTask; // CustomTask;
 qText : widestring;
begin

  dtsTask := dts.Tasks.New('DTSDataPumpTask');
  dtsTask.Name := 'Copy Data from SQL to dBase Task';
  dtsCustomTask1 := dtsCustomTask1 := dtsTask.CustomTask;   // Compile error
type mismatch,

// if I change the type to CustomTask

// can not set SrourcConnectionID

   tsCustomTask1.Name := 'Copy Data from SQL to dBase Task';
 dtsCustomTask1.Description := 'Copy Data from SQL to dBase Task';
 dtsCustomTask1.SourceConnectionID := 1 ;
  qText := 'SELECT ''****ECRLOT**'' + v.CR2 + '' AS ECR, v.*" ' + Chr(13);
  qText :=  qText +  '  FROM  vwMailHouseHolds  v ' + Chr(13);
  qText :=  qText +  '  JOIN ##MIKE2LABEL lst ' + Chr(13);
  qText :=  qText +  '    ON v.voter_id = lst.voter_id ' + Chr(13);
  qText :=  qText +  '  LEFT JOIN ##MIKE2CR_LIST cr ' + Chr(13);
  qText :=  qText +  '    ON v.zip = cr.zip ' + Chr(13);
  qText :=  qText +  '    AND v.cr = cr.cr ' + Chr(13);
  qText :=  qText +  '  WHERE v.COUNTY = ''LA''' + Chr(13);
  qText :=  qText +  '    AND dbo.Valid_Carrier_Route (v.lot_seq, cr.cnt) =
1 ' + Chr(13);
  qText :=  qText +  '  ORDER BY v.Lot_Sort ASC'
 dtsCustomTask1.SourceSQLStatement := qTest ;
 dtsCustomTask1.DestinationConnectionID := 2
 dtsCustomTask1.DestinationObjectName := "Test"
 dtsCustomTask1.ProgressRowCount := 1000
 dtsCustomTask1.MaximumErrorCount := 0
 dtsCustomTask1.FetchBufferSize := 1
 dtsCustomTask1.UseFastLoad := True
 dtsCustomTask1.InsertCommitSize := 0
 dtsCustomTask1.ExceptionFileColumnDelimiter := "|"
 dtsCustomTask1.ExceptionFileRowDelimiter := vbCrLf
 dtsCustomTask1.AllowIdentityInserts := False
 dtsCustomTask1.FirstRow := 0
 dtsCustomTask1.LastRow := 0
 dtsCustomTask1.FastLoadOptions := 2
 dtsCustomTask1.ExceptionFileOptions := 1
 dtsCustomTask1.DataPumpOptions := 0

//Call oCustomTask1_Trans_Sub1( oCustomTask1 )

//goPackage.Tasks.Add oTask
//Set oCustomTask1 = Nothing
//Set oTask = Nothing

end;

The DTS created this VB code
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask.Name = "Copy Data from Results to Test Task"
Set oCustomTask1 = oTask.CustomTask

 oCustomTask1.Name = "Copy Data from Results to Test Task"
 oCustomTask1.Description = "Copy Data from Results to Test Task"
 oCustomTask1.SourceConnectionID = 1
 oCustomTask1.SourceSQLStatement = "SELECT '****ECRLOT**' + v.CR2 + '' AS
ECR, v.*" & vbCrLf
 oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & " FROM
vwMailHouseHolds  v " & vbCrLf
 oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "  JOIN
##MIKE2LABEL lst " & vbCrLf
 oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "    ON
v.voter_id = lst.voter_id " & vbCrLf
 oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & " LEFT
JOIN ##MIKE2CR_LIST cr " & vbCrLf
 oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "   ON
v.zip = cr.zip " & vbCrLf
 oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "  AND
v.cr = cr.cr " & vbCrLf
 oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
WHERE v.COUNTY = 'LA'" & vbCrLf
 oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
AND dbo.Valid_Carrier_Route (v.lot_seq, cr.cnt) = 1 " & vbCrLf
 oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
ORDER BY v.Lot_Sort ASC"
 oCustomTask1.DestinationConnectionID = 2
 oCustomTask1.DestinationObjectName = "Test"
 oCustomTask1.ProgressRowCount = 1000
 oCustomTask1.MaximumErrorCount = 0
 oCustomTask1.FetchBufferSize = 1
 oCustomTask1.UseFastLoad = True
 oCustomTask1.InsertCommitSize = 0
 oCustomTask1.ExceptionFileColumnDelimiter = "|"
 oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
 oCustomTask1.AllowIdentityInserts = False
 oCustomTask1.FirstRow = 0
 oCustomTask1.LastRow = 0
 oCustomTask1.FastLoadOptions = 2
 oCustomTask1.ExceptionFileOptions = 1
 oCustomTask1.DataPumpOptions = 0

Call oCustomTask1_Trans_Sub1( oCustomTask1 )

goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

 

Re:D7 vs DTS III programming TASK


Wouldn't it be easier to use a dbase component (which should be your fastest
option)?  Or the BDE? (which is free and works with Delphi, even if it one
day won't be available)

Some dbase components can be found at:
    www.hallogram.com/topazdelphi

There is a free dbase project on sourceforge:

http://sourceforge.net/projects/tdbf/

For DTS help, I suggest the oleautomation group.

Oliver

Quote
"Vanessa Amaral" <[email protected]> wrote in message

news:[email protected]
Quote
> Any one have everperiance programing the DTS !!!!!
> not getting any replies and could really use some help
> I am stuck on configuring the tast

Re:D7 vs DTS III programming TASK


If the BDE is going away why shoule I use it
and
DTS is much faster then BDE, especially on append anything over 10,000
records, the BDE is a dog.

I will try the OLEautomation group
Thanks

Quote
"Oliver Townshend" <[email protected]> wrote in message

news:[email protected]
Quote
> Wouldn't it be easier to use a dbase component (which should be your
fastest
> option)?  Or the BDE? (which is free and works with Delphi, even if it one
> day won't be available)

> Some dbase components can be found at:
>     www.hallogram.com/topazdelphi

> There is a free dbase project on sourceforge:

> http://sourceforge.net/projects/tdbf/

> For DTS help, I suggest the oleautomation group.

> Oliver

> "Vanessa Amaral" <[email protected]> wrote in message
> news:[email protected]
> > Any one have everperiance programing the DTS !!!!!
> > not getting any replies and could really use some help
> > I am stuck on configuring the tast

Re:D7 vs DTS III programming TASK


Thanks Oliver
The tDBF works and is fast for this part of the app.

However I still need to program the DTS for moving text files of various
formats into SQL.
I want to have the user inter the format then Delphi programming the DTS,

Quote
"Oliver Townshend" <[email protected]> wrote in message

news:[email protected]
Quote
> Wouldn't it be easier to use a dbase component (which should be your
fastest
> option)?  Or the BDE? (which is free and works with Delphi, even if it one
> day won't be available)

> Some dbase components can be found at:
>     www.hallogram.com/topazdelphi

> There is a free dbase project on sourceforge:

> http://sourceforge.net/projects/tdbf/

> For DTS help, I suggest the oleautomation group.

> Oliver

> "Vanessa Amaral" <[email protected]> wrote in message
> news:[email protected]
> > Any one have everperiance programing the DTS !!!!!
> > not getting any replies and could really use some help
> > I am stuck on configuring the tast

Re:D7 vs DTS III programming TASK


Actually DTS will be MUCH faster than BDE as long as fastload is used. BDE
does not support SQL7+ types either.

-Euan

Quote
"Oliver Townshend" <[email protected]> wrote in message

news:[email protected]
Quote
> Wouldn't it be easier to use a dbase component (which should be your
fastest
> option)?  Or the BDE? (which is free and works with Delphi, even if it one
> day won't be available)

> Some dbase components can be found at:
>     www.hallogram.com/topazdelphi

> There is a free dbase project on sourceforge:

> http://sourceforge.net/projects/tdbf/

> For DTS help, I suggest the oleautomation group.

> Oliver

> "Vanessa Amaral" <[email protected]> wrote in message
> news:[email protected]
> > Any one have everperiance programing the DTS !!!!!
> > not getting any replies and could really use some help
> > I am stuck on configuring the tast

Re:D7 vs DTS III programming TASK


I posted a sample in the attachements newsgroup, you need to access the task
as a generic task object and then QI for the customer task interface you
need.

-Euan

Quote
"Vanessa Amaral" <[email protected]> wrote in message

news:[email protected]
Quote
> Any one have everperiance programing the DTS !!!!!
> not getting any replies and could really use some help
> I am stuck on configuring the tast

> MY D7 Code  VB code created by DTS follows

> procedure DTSTask(dts : TPackage);
> var
>  dtsTask  : Task;
>  dtsLookup : Lookup;
>  dtsCustomTask1 : DataPumpTask; // CustomTask;
>  qText : widestring;
> begin

>   dtsTask := dts.Tasks.New('DTSDataPumpTask');
>   dtsTask.Name := 'Copy Data from SQL to dBase Task';
>   dtsCustomTask1 := dtsCustomTask1 := dtsTask.CustomTask;   // Compile
error
> type mismatch,

> // if I change the type to CustomTask

> // can not set SrourcConnectionID

>    tsCustomTask1.Name := 'Copy Data from SQL to dBase Task';
>  dtsCustomTask1.Description := 'Copy Data from SQL to dBase Task';
>  dtsCustomTask1.SourceConnectionID := 1 ;
>   qText := 'SELECT ''****ECRLOT**'' + v.CR2 + '' AS ECR, v.*" ' + Chr(13);
>   qText :=  qText +  '  FROM  vwMailHouseHolds  v ' + Chr(13);
>   qText :=  qText +  '  JOIN ##MIKE2LABEL lst ' + Chr(13);
>   qText :=  qText +  '    ON v.voter_id = lst.voter_id ' + Chr(13);
>   qText :=  qText +  '  LEFT JOIN ##MIKE2CR_LIST cr ' + Chr(13);
>   qText :=  qText +  '    ON v.zip = cr.zip ' + Chr(13);
>   qText :=  qText +  '    AND v.cr = cr.cr ' + Chr(13);
>   qText :=  qText +  '  WHERE v.COUNTY = ''LA''' + Chr(13);
>   qText :=  qText +  '    AND dbo.Valid_Carrier_Route (v.lot_seq, cr.cnt)
=
> 1 ' + Chr(13);
>   qText :=  qText +  '  ORDER BY v.Lot_Sort ASC'
>  dtsCustomTask1.SourceSQLStatement := qTest ;
>  dtsCustomTask1.DestinationConnectionID := 2
>  dtsCustomTask1.DestinationObjectName := "Test"
>  dtsCustomTask1.ProgressRowCount := 1000
>  dtsCustomTask1.MaximumErrorCount := 0
>  dtsCustomTask1.FetchBufferSize := 1
>  dtsCustomTask1.UseFastLoad := True
>  dtsCustomTask1.InsertCommitSize := 0
>  dtsCustomTask1.ExceptionFileColumnDelimiter := "|"
>  dtsCustomTask1.ExceptionFileRowDelimiter := vbCrLf
>  dtsCustomTask1.AllowIdentityInserts := False
>  dtsCustomTask1.FirstRow := 0
>  dtsCustomTask1.LastRow := 0
>  dtsCustomTask1.FastLoadOptions := 2
>  dtsCustomTask1.ExceptionFileOptions := 1
>  dtsCustomTask1.DataPumpOptions := 0

> //Call oCustomTask1_Trans_Sub1( oCustomTask1 )

> //goPackage.Tasks.Add oTask
> //Set oCustomTask1 = Nothing
> //Set oTask = Nothing

> end;

> The DTS created this VB code
> Public Sub Task_Sub1(ByVal goPackage As Object)

> Dim oTask As DTS.Task
> Dim oLookup As DTS.Lookup

> Dim oCustomTask1 As DTS.DataPumpTask2
> Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
> oTask.Name = "Copy Data from Results to Test Task"
> Set oCustomTask1 = oTask.CustomTask

>  oCustomTask1.Name = "Copy Data from Results to Test Task"
>  oCustomTask1.Description = "Copy Data from Results to Test Task"
>  oCustomTask1.SourceConnectionID = 1
>  oCustomTask1.SourceSQLStatement = "SELECT '****ECRLOT**' + v.CR2 + '' AS
> ECR, v.*" & vbCrLf
>  oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
FROM
> vwMailHouseHolds  v " & vbCrLf
>  oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
JOIN
> ##MIKE2LABEL lst " & vbCrLf
>  oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
ON
> v.voter_id = lst.voter_id " & vbCrLf
>  oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
LEFT
> JOIN ##MIKE2CR_LIST cr " & vbCrLf
>  oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
ON
> v.zip = cr.zip " & vbCrLf
>  oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
AND
> v.cr = cr.cr " & vbCrLf
>  oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
> WHERE v.COUNTY = 'LA'" & vbCrLf
>  oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
> AND dbo.Valid_Carrier_Route (v.lot_seq, cr.cnt) = 1 " & vbCrLf
>  oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement & "
> ORDER BY v.Lot_Sort ASC"
>  oCustomTask1.DestinationConnectionID = 2
>  oCustomTask1.DestinationObjectName = "Test"
>  oCustomTask1.ProgressRowCount = 1000
>  oCustomTask1.MaximumErrorCount = 0
>  oCustomTask1.FetchBufferSize = 1
>  oCustomTask1.UseFastLoad = True
>  oCustomTask1.InsertCommitSize = 0
>  oCustomTask1.ExceptionFileColumnDelimiter = "|"
>  oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
>  oCustomTask1.AllowIdentityInserts = False
>  oCustomTask1.FirstRow = 0
>  oCustomTask1.LastRow = 0
>  oCustomTask1.FastLoadOptions = 2
>  oCustomTask1.ExceptionFileOptions = 1
>  oCustomTask1.DataPumpOptions = 0

> Call oCustomTask1_Trans_Sub1( oCustomTask1 )

> goPackage.Tasks.Add oTask
> Set oCustomTask1 = Nothing
> Set oTask = Nothing

> End Sub

Other Threads