Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

issues with running SSIS packages programmatically

Posted on 2010-09-02
10
Medium Priority
?
1,007 Views
Last Modified: 2013-11-10
Hi, I have spent days trying to solve this problem and still stuck with this and I have posted some questions in various forums, but didn't get satisfactory answers. I have gone through this article already http://blogs.msdn.com/b/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx and here are my issues (I need to run the SSIS package from ASP.NET)

1.option 1 is not suitable for me, because it may recycle worker process if it consumes memory

2.option 2 is also not suitable because of security issues in creating a new process and passing the context to new process looks very complicated for me (according to the support article)

3.option 3 is not suitable because using SQL Server Agent to run SSIS package is not allowed by the company I am working for(I guesss it requires installation of db engine on application server, not sure). but SSIS is installed on the application server.

4.option 4&5 will have the same issues as options 1&2.

I guess the only option left now is to create a windows service and start the service from ASP.NET. but will this allow running multiple packages in parallel? OR is there a better alternate solution for this? please let me know. Thanks.
0
Comment
Question by:rkpavp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 16

Expert Comment

by:carsRST
ID: 33585688
In my opinion, given your limitations, option 2 is your easiest route.   See below.  You can have a VBS script call the command line tool and run your SSIS project.  Using windows scheduler you can set your package to run at any time by calling the VBS file.

To test this, put the code below in a file and save as *.vbs.  Obviously input the path to your package.  Then double click it.  It should run.




Path = "C:\...\mySSISPackage.dtsx"

Set WshShell = WScript.CreateObject("WScript.Shell")

ReturnCode = WshShell.Run("dtexec.exe /FILE """ & Path & """ /MAXCONCURRENT "" -1 "" /CHECKPOINTING OFF  /REPORTING EWCDI  ")

Open in new window

0
 

Author Comment

by:rkpavp
ID: 33585704
thanks for the reply. but the package name is dynamically passed from the web page and the package should be executed on demand from the user, not scheduled by the windows scheduler. how can I do that?
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33585756
Another option is calling a stored procedure and having it run the ssis script.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79508

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 16

Expert Comment

by:carsRST
ID: 33585814
Just a follow up to the last post.

With the SP route, you can pass in your SSIS package name as a parameter.  

Using the xp_cmdshell within the SP, you can execute the DTEXEC.exe command tool and run your package.

0
 

Author Comment

by:rkpavp
ID: 33585821
thanks for the reply. in my case the packages are stored on the application server file system, not on the database, will it still work? (also I am not sure if xp_cmdshell option is allowed by the DBAs where I work). I also need an option to kill it if the process hangs (i.e. runs for a long time unexpectedly). I mean kill the specific long running process which is executing a given package, not other dtexec processes which may be running fine.
0
 
LVL 16

Expert Comment

by:carsRST
ID: 33585853
>> in my case the packages are stored on the application server file system, not on the database, will it still work?

yes - but you have to have permissions to the directory where the package lives.  Just like calling a file that resides on another server.

In any case, I don't see how you get away from using the command line tool.

I guess you could also take my first approach and just remotely kick off the VBS script residing on the app server and have it run the package.  

You have a lot of restrictions.  
0
 

Author Comment

by:rkpavp
ID: 33585896
that's fine. but regarding my second question, lets say I want to kill that process if it is taking a long time (due to some deadlocks or any other reason). how can I do that (with command line or xp_cmdshell option)? I need to kill only the specific process which is taking long time for a given package, not the other processes for other packages which are running fine.
0
 
LVL 16

Accepted Solution

by:
carsRST earned 1500 total points
ID: 33585940
I would put in checks within your package, but below is some c# code that will kill processes running remotely.

http://alperguc.blogspot.com/2008/11/c-process-processgetprocessesbyname.html
0
 

Author Comment

by:rkpavp
ID: 33586482
this will help. but as I said earlier, there could be multiple instances of that process running (if multiple packages are running) and I need to kill the right one. anyway thanks for your comments. I will try to figure that out.
0
 

Author Closing Comment

by:rkpavp
ID: 33598373
a possible solution is suggested which I need to improve lot more to get the desired results. thanks for the replies
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

721 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