[RESOLVED] DTS package

Hi peepz,

Is it applicable to use Stored Procedure to run DTS packages
or
Hardcode DTS execution in vb6
For a scenario like, executing DTS package accross the network?

Thank You:wave:

Is this MS SQL 2005?

Please give me details about what you are trying to accomplish.

Usually a DTS job is run on the SERVER itself - scheduled in the server to run at certain times.

Have you considered using VB to call DTRun.exe from the command prompt.

For running via a stored procedure i would use the xp_cmdshell to run dtsrun.exe from the command promt also. You can incorporate xp_cmdshell into your own stored procedure

Guys, Thanks for the questions..
I am using MS SQL 2005
I downloaded DTS Designer to import DTS packages from SQL 2000

Here's the Scenario:
From Application(client PC), I want to execute DTS from the Server (SQL 2k5)

My System is running smoothly by calling a stored proc in my vb code and that stored proc calls the xp_cmdshell to run my DTS packages from server.

But I still want to know if there's a way for the DTS package to work if I code it in my VB. (something like Set oStep as new oStep.steps)

because I've tried it already but it fails in the client-server scenario. Only works on standalone.

Thank You Gurus.

What does this DTS package actually do? That's what I was curious about.

I believe that you can start DTS jobs with SQLDMO from VB6 - I googled for "sqldmo start a dts job" and got lots of links back.

If your using vb.net these links might be useful

HOW TO: Use DTS Package Events in Visual Basic .NET
http://support.microsoft.com/default...b;en-us;321525

How to create a DTS custom task by using Visual Basic .NET
http://support.microsoft.com/default...b;en-us;328587

I wrote a nice little DTS package runner in VB.NET 2003 a while back in my old company but forgot to take the code with me!

Doh!

Quote:

---

Originally Posted by Krizaaa

...
or
Hardcode DTS execution in vb6

---

Unfortunately the OP stated they are in VB6

My DTS package uploads data from .txt file to a table.
content of the .txt is like this:

0123 | US Bonds | 8.75 | 02/12/2008| mnscs

:wave:

There is no reason to use DTS to load a text file.

You can load a text file from a SPROC with BULK INSERT called from any client side app.

Do you need to see an example of this?

Thanks szlamany,

I saw this BULK INSERT when I run profiler during DTS execution from client. But I don't have any idea with it. The text file I upload has | delimeter..

This SPROC we have takes a text file and BULK INSERT's it into a temp table and then starts processing that data - cleaning it up (checking for valid id numbers) and such.

Code:

---

Create Procedure LearnCertLic_P
as
Begin Tran

Set NoCount On

Declare @TD datetime
Set @TD=GetDate()

Create Table #LicCom (Col1 varchar(1000),Col2 varchar(1000),Col3 varchar(1000),Col4 varchar(1000)
,Col5 varchar(1000),Col6 varchar(1000),Col7 varchar(1000),Col8 varchar(1000))

Bulk Insert #LicCom From '\\10.0.0.11\h$\Learn_FTP\Certifications_Licenses\Certifications.txt'

Update #LicCom Set Col7=Left(Col7,CharIndex(' ',Col7)-1)
        Where CharIndex(' ',Col7)>0

Insert into LearnError_T Select @TD,'No MASTER ID found'
                ,IsNull(Col1+' ','')+IsNull(Col2+' ','')+IsNull(Col3+' ','')+IsNull(Col4+' ','')
                +IsNull(Col5+' ','')+IsNull(Col6+' ','')+IsNull(Col7+' ','')+IsNull(Col8+' ','')
                From #LicCom
                Where IsNumeric(Col6)=0

Delete From #LicCom Where IsNumeric(Col6)=0

Update #LicCom Set Col4=Replace(Col4,'LICENSE - ','%L%')
Update #LicCom Set Col4=Replace(Col4,'CERTIFICATION - ','%C%')
.
.
.

---

Bulk Insert #LicCom From '\\10.0.0.11\h$\Learn_FTP\Certifications_Licenses\Certifications.txt'

This bulk insert statement is still "server-centric" - so the device and file must be located using a path as the server would see it.

Thanks Szalamany.. Now I have alternative solutions. Gonna rate you for the heck of it

You are very welcome. We use this technique extensively.

Remember to mark this thread resolved using the thread tools menu up top.