SQL DTS BulkInsertTask

forum.sourcecode (2000-2005) & forum.td.sourcecode (2005-2010)
Andy Wong

SQL DTS BulkInsertTask

Post by Andy Wong » 26 Feb 2007, 10:24

 Posted by:  Andy Wong 

Hi,

I'm new to CTD2005 and need to translate the following VB code which calls
SQL Server DTS ActiveX object to perform a bulk insert into the database.
Can anyone help?

Code: Select all

'initialize Payroll table in DTS_UE db with bulk data
Dim objPackage      As DTS.Package2
Dim objConnect      As DTS.Connection2
Dim objStep         As DTS.Step
Dim objTask         As DTS.Task
Dim objBulkCopy     As DTS.BulkInsertTask

Set objPackage = New DTS.Package

'create database connection
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
    .ID = 1
    .DataSource = "(local)"
    .UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
'create step and task, specify data file and format
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSBulkInsertTask")
Set objBulkCopy = objTask.CustomTask
With objBulkCopy
    .Name = "BulkInsTask"
    .DataFile = "D:\DTS_UE\BCPData\Payroll.txt"
    .ConnectionID = 1
    .DestinationTableName = "DTS_UE..Payroll"
    .FieldTerminator = "|"
    .RowTerminator = "\r\n"
End With

'link step to task to package, run package
objStep.TaskName = objBulkCopy.Name
objStep.Name = "BulkInsStep"
With objPackage
    .Steps.Add objStep
    .Tasks.Add objTask
    .FailOnError = True

    .Execute
End With

Krzysztof Dorko

Re: SQL DTS BulkInsertTask

Post by Krzysztof Dorko » 26 Feb 2007, 16:15

 Posted by:  Krzysztof Dorko 

Hi Andy
I do not know if my example realy works but this is at least way how you
should convert this code
Just play with it and let me know if you will have come problems
I used GTD 2005.1
To generate DTS lib just go to
tools -> activex explorer.. and generate it. Then Go to DTS_Package2 object
and comment derived from DTS__Package
HTH
Kris

You do not have the required permissions to view the files attached to this post.

Andy Wong

Re: SQL DTS BulkInsertTask

Post by Andy Wong » 27 Feb 2007, 02:54

 Posted by:  Andy Wong 

Thanks a lot!

But my damn Outlook Express blocks all files with the extension APT. So do
you mind sending the file again by compressing it first?

Kris

Re: SQL DTS BulkInsertTask

Post by Kris » 27 Feb 2007, 04:02

 Posted by:  Kris 

Ok no problem

Kris

You do not have the required permissions to view the files attached to this post.

Andy Wong

Re: SQL DTS BulkInsertTask

Post by Andy Wong » 27 Feb 2007, 04:03

 Posted by:  Andy Wong 

Just found I can actually turn off that security option and I can download
the attachment now. Thx!

Andy Wong

Re: SQL DTS BulkInsertTask

Post by Andy Wong » 27 Feb 2007, 10:21

 Posted by:  Andy Wong 

your code sort out most of my question but some errors are generated during
compilation.

it seems that Centura encapsulated all members of the class in DTS__Package2
instead of DTS_Package2. so I change to use DTS__Package2 but another
problem comes. DTS__Package2 don't have Create() function. without first
running Create(), all subsequent functions return false.

further help is much appreciated.

Krzysztof Dorko

Re: SQL DTS BulkInsertTask

Post by Krzysztof Dorko » 27 Feb 2007, 15:26

 Posted by:  Krzysztof Dorko 

Hi
Hmm I have compiled this and everything was ok
So, You must use DTS_Package2 because this is your Proxy and this class
derived from DTS__Package2. In this case you should have all functionality
of it.
But for some reason Gupta generates this proxy in wrong way and you have to
make a little change like I've written in my first post. After generation
your proxy derived from DTS__Package and DTS__Package2 and most functions
are duplicated so just comment DTS__Package and you should be able to
compile without errors.
The only one point where I am not sure in 100% is this Attach method. I use
similar solution in other case and everything is ok but .... So in case this
function crashes in this point I suggest replace in function
PropGetCustomTask argument type from DTS_CustomTask to DTS_BulkInsertTask.
It should be possible because both derived from the same class and in VB you
can use it directly.

HTH

Kris

Krzysztof Dorko

Re: SQL DTS BulkInsertTask

Post by Krzysztof Dorko » 27 Feb 2007, 17:40

 Posted by:  Krzysztof Dorko 

To be more precise
do this

Code: Select all

COM Proxy Class: DTS_Package2
 Description:
 Derived From
  ! Class: DTS__Package --Just comment this line
  Class: DTS__Package2
 Class Variables
 Instance Variables
 Functions
Kris

Return to “td.sourcecode”

Who is online

Users browsing this forum: [Ccbot] and 0 guests