[RESOLVED] DTS package

[Krizaaa]

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

[szlamany]

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.

[kevchadders]

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

[Krizaaa]

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.

[szlamany]

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.

[kevchadders]

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!

[szlamany]

...
or
Hardcode DTS execution in vb6

Unfortunately the OP stated they are in VB6

[Krizaaa]

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

[szlamany]

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?

[Krizaaa]

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..

[szlamany]

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.

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.

[Krizaaa]

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

[szlamany]

You are very welcome. We use this technique extensively.

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