Solved

executing DTS package from ASP.NET with VB.NET

Posted on 2004-04-28
13
2,679 Views
Last Modified: 2013-11-25
Hi,
I need to run a simple DTS package from the asp.net page.  i have button on the webform i need to run the dts package for that button click event.
I was trying to find examples on the internet, but i could not find a simple answer..

Using one of the articles i have created ref to dts dll file and was working with it. My dts execution process is taking to too long, in fact i never had it finished. when i run the package in enterprise manager it takes less than a min.

here is the sample code that i used.

Dim package As DTS.Package2Class
        package = New DTS.Package2Class
        Dim infos As DTS.SavedPackageInfos
        package.LoadFromSQLServer("(LOCAL)", "sa", "passwd", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "ProcessDB", "")
        package.Execute()
        package.UnInitialize()
        package = Nothing

I would be thankfull if some one could point me to a simple example or a sample code.
Thank you in advance
Ravi
0
Comment
Question by:ravi_net
  • 4
  • 4
  • 4
  • +1
13 Comments
 
LVL 5

Expert Comment

by:bwdowhan
ID: 10944391
Here is the link to the Microsoft Knowledge base on this:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q252987&ID=KB;EN-US;q252987

It actually describes the process very well and includes sample code..

~Brian
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 300 total points
ID: 10944472
Or, if you don't want to actually mess with the package object, just  execute with xp_cmdshell against a connection object:

conn.execute ("master..xp_cmdshell 'dtsrun /all your parms')

the user you login with must either be a member of th sysadmin group, or you must enable the proxy account to run xp_cmdshell.

If you need help with DTSRUN parms, type dtsrun at the command prompt or run DTSRUNUI and it will build the command line for you.

Brett
0
 
LVL 5

Expert Comment

by:bwdowhan
ID: 10944526
The DTSRUNUI utility isn't a bad way to go. In fact to make it easier to make changes, you could save the DTSRUN as a batch file by generating the execution string in the utility, saving it in a batch file and using xp_cmdshell to run your batch program.

I have an implementation of a WROX press com+ object that allows web driven requests to manage the execution of DTS packages. Of course you would have to write your own security around the COM object which gets more involved but it works very well, especially when you need users to execute jobs at non-specific times. At WROX Press, it is the Professional SQL Server 2000 DTS book.

~Brian
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:arbert
ID: 10944536
Why make it so complicated????
0
 

Author Comment

by:ravi_net
ID: 10944602
Hi
Thanks for your comments.
I was able to run a simple pacage using the above code.
But when i run this particular package it takes very long time (i don't know how long. coz it is still running). Why is taking so long when i could run the package in less than one min from enterprise manager??

Is there any thing wrong with the code i used above??

bwdowhan, i have been throw that article and it explains about asp. i used some part of that article and trying to work it with asp.net

albert, i think the params that should pass to the dtsrun are the same as the ones that i mentioned in my previous posting!!! dbname, username, password..etc....
So, i don't think that should make much of a difference.

Thank you in advance for any further pointers.
Ravi
0
 
LVL 5

Expert Comment

by:bwdowhan
ID: 10944691
arbert, if you are referring to the COM+ object, my asp page dynamically pulls in global variables and allows users to control the values to create custom processes, that's a different story though...

As for the processing taking so long, I would look at potential locks or resource issues. Does this DTS process pull data or push data to a different server or share drive or is everything done on the server?

~Brian
0
 

Author Comment

by:ravi_net
ID: 10944742
Brian,
I am doing evertying on the same machine. i have couple of table with around 600 record overall. my package process the entire cube.

I changed the cube such that it only process one dimention but still it taking long time.

I ran the dtsrun.exe from command line with the parameters and it got executed.

Could you give me more details how i could run this with in vb.net.

albert, as you said i was tring to run the execute method on conn object. i assume that you are reffering to the sqlconn object and there is no method as execute on connection class!!!

Thank you in advance
ravi
0
 
LVL 34

Expert Comment

by:arbert
ID: 10944786
I would look at permission problems--are you sure that it's actually running when you call it from xp_cmdshell?  Are you executing both tests with the same account?

Did you run sp_who from query analyzer to see if it's actually running or if there is any locking/blocking?
0
 

Author Comment

by:ravi_net
ID: 10944819
Albert,
I am running both from the same account.
Here the commands and output i got...

dtsrun /S(local) /NProcessDb /Usa /Ppassword

when i run this from command promt i got this result

DTSRun:  Loading...
DTSRun:  Executing...
DTSRun OnStart:  DTSStep_DTSOlapProcess.Task_1
DTSRun OnFinish:  DTSStep_DTSOlapProcess.Task_1
DTSRun:  Package execution complete.

i believe that the package is getting executed.
When i run the same command with different package (creates a folder), its working fine.

i think i need a way to work the above command from vb.net

Thanks in advane
Ravi
0
 
LVL 5

Accepted Solution

by:
bwdowhan earned 200 total points
ID: 10944933
If you are going to use dtsrun froma vb.net desktop application you could use:

SHELL ("C:\dtsrun /S(local) /NProcessDB /Usa /Ppassword")

Since you want to use asp.net you want to execute a sql command, as arbert described.  Try the following from query analyzer:

master..xp_cmdshell 'dtsrun /S(local) /NProcessDB /Usa /Ppassword'

If if works from query analyzer then you just need to store this in a string and execute it with conn.execute

~Brian
0
 
LVL 6

Expert Comment

by:jchopde
ID: 10945433
If you are calling the package from ASP.NET, the package is executing most likely under the ASPNET user's security context so you will need to make sure that the ASPNET user has access to all the resources used in your package (databases, files, DLLs,...whatever). Even though you have specified "sa" in LoadFromSQLServer, that is NOT the security context when you call Execute.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10945510
"the package is executing most likely under the ASPNET"

Depending on the security model that you defined for your .NEt application...
0
 

Author Comment

by:ravi_net
ID: 10989015
I had working using the storedprocedure. Storedprocedure runs the dtsrun utility on xp_cmdshell and provides the required params.
Thanks for the info guys...
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question