Trying to create DTS package in ASP.NET

Question

Post reply

Trying to create DTS package in ASP.NET

Bonnie Whitfield

SSC Rookie

Points: 41
More actions
September 16, 2005 at 8:34 am

#168741

Note the word "trying". As in not succeeding very well.
Forgive me if anyone else has discussed this topic elsewhere, but I couldn't find it.
My task is to create DTS packages on the fly to download data from an AS/400 to our local SQL Server 2000 data warehouse. We are trying to automate the process, as it has proven difficult to manage manually.
I have succeeded in creating a DTS package with 2 connections, and deleting it if we are creating a new version -- but I can't seem to get past that to create a task or a step.
Here is the code I have so far (objPkg is my previously created package object with the two connections, connection 1 is the SQL Server):
Dim Task1 As DTS.Task
Dim Step1 As DTS.Step2
Dim Task1SQL As DTS.ExecuteSQLTask2
Step1 = objPkg.Steps.New
Task1 = objPkg.Tasks.New("DTSExecuteSQLTask")
With Step1
   .Name = "MakeBackupTable"
   .TaskName = "MakeBackupTable"
   .ExecuteInMainThread = True
End With
Task1SQL = Task1.CustomTask
With Task1SQL
   .Name = "MakeBackupTable"
   .Description = "Delete existing backup table, rename current table to *_BAK"
   .SQLStatement = "EXECUTE sp_BackUpTable '" & Tbl & "'"
   .ConnectionID = 1
   .CommandTimeout = 0
   .OutputAsRecordset = False
End With
objPkg.Tasks.Add(Task1)
Task1SQL = Nothing
Task1 = Nothing
When I run this, I get the following error message: "QueryInterface for interface DTS.CustomTask failed." on the line "Task1SQL = Task1.CustomTask" between the two With groups. I have tried several examples straight from Books Online, and they all get the same error. Using the theory "too much is better than too little", I have the following Imports lines at the top of my codebehind:
Imports DTS
Imports DTSCustTasks
Imports DTSPump
Imports SQLDMO
Imports SQLNS
Any help/suggestions/ideas would be greatly appreciated. I have really blown my timeline for this project trying to figure it out myself.
Thanks, Bonnie

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

When all else fails Cheat.

Create a dts package manually that does what you want.

Then do a package --> SaveAs --> Visual Basic File.

Open that file in visual studio, and it will show you all the steps, (In vb of course) use this as a guide on how to build in c# or VB whatever.

Bonnie Whitfield SSC Rookie Points: 41 More actions · Answer 2

Cheating is good.

I haven't actually done that with a DTS package that is exactly what I need, but I did do it with another package that has both a SQL task and a datapump task, and I've been using that for an example.

One of the problems I'm having with that example is that the steps are created before the tasks, and that just makes no sense to me, but that is why the code snippet I gave is in the order it is.

-- Bonnie

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Yeah, its all about the way the dts package binds the steps to the object, its kind of a pain.

Bonnie Whitfield SSC Rookie Points: 41 More actions · Answer 4

I found the answer finally. For those of you who are curious, the problem was my references. The local copies of the DLLs I had on my development PC were from the original release version of SQL Server 2000, and had no service packs applied. Once I copied the DLLs from our test environment SQL Server, which is running SP4, I was golden. The reference I found via Google said the bug that resulted in the error message I was getting was resolved in SP2. Specifically, the files I needed were (on the SQL Server) in

c:\Program Files\MS SQL Server\Tools\Binn

and are named:

custtask.dll

dtspkg.dll

dtspump.dll

As soon as I copied the versions on the server over to the same location on my PC, started up Visual Studio, removed the old references and added the new ones, it worked.

Whew!

-- Bonnie