Wednesday, February 13, 2008

SSIS - Execute a package from an ASP.NET page

Dear Friends,
Today I'm describing the way I executed a package from an ASP.NET page and passing a parameter.

The Problem
I need to enable the user of an ASP application to execute a package and passing a parameter to it when the user press a button.

Solution
The first thing you must know, is where you ssis packge will be stored. You or the DBA Team could stor the SSIS package inside database, in the filesystem or in DTS. Each one has advantages and disadvantages, and in my opinion the package inside database could be better when you must unsure more security.

So, in my SSIS package I have created a global variable "strCFG_TextPath" and I will update this value from ASP page. I have created a function fExecSSISPackage in a clean webservice to make the call.
(You can create the function in the ASP page, but in my case I made like this because I insert all the connections to the database inside a WebService)

WebService

In this example the SSIS package is stored in the filesystem, but the function is prepared to accept each of the 3 types that I described before.

You only need to write few lines: (SourceLocation is the string value I want to pass to the SSIS package)

Dim myPackage as Package
Dim integrationServices As New Application
myPackage = integrationServices.LoadPackage(packagePath, Nothing)
myPackage.Variables("strCFG_TextPath").Value = sourceLocation
myPackage.Execute()

ASP.NET Page
To call the function in the WebService is simple... see the image:

I hope this post could be helpfull for you.
Kind Regards,
Pedro

12 comments:

Amin said...
This comment has been removed by the author.
Pedro said...

Dear Amin,
You can catch the warning, information and error messages if you activate the log provider of your SSIS package. Doing that you can save these messages in a database, textfile or other.
Cheers friend!!!
Pedro

Amin said...
This comment has been removed by the author.
Pedro said...

Dear Amin,
Yes... I never tried, but yes.
When the user do the request from the ASP.NET page, the log provider of the SSIS Package can save several events like:
StartDateTimeRunPackage
EndDateTimeRunPackage
Error and other kind of messages like information, on attention...

If you want to know the rows inserted for the user, you must do some customization using the event handlers on the SSIS Package. Example: adding an sql task to the event handler postExecute to save the rows inserted in the task or in the container.

Always depend on what you need to do.
Regards!!! :-)

Subhash Subramanyam said...

Pedro,
Your post was very useful. I had this question in my mind but never tried. You already have that posted ! .. Great..

Thanks
Subhash Subramanyam

Amin said...
This comment has been removed by the author.
Pedro said...

Dear Amin,
it's a pleasure!
Do you know about SQL Bits conference next 1ºMarch on Birmingham (UK)? I'm trying to get my confirmation but will be dificult because there are about 500 people registered!!! Do you go?
Regards!

Amin said...
This comment has been removed by the author.
Pedro said...

Dear Amin,
But right now you are in UK or in Iran?!
Regards!!
:-)

Amin said...
This comment has been removed by the author.
Pedro said...

Dear Amin,
Because you told me some weeks ago that would like to go to england! Nothing special! :-)
I will be in UK next 1ºMarch for the SQLBits... You dont like to go?! See www.sqlbits.com!!!
Cheers!!!

Amin said...

Dear Pedro,
It was a good and useful post.
Is it possible to catch the warnings, and failure messages that are produced through SSIS Packages?
Thanks,
Amin

14 February, 2008 09:29
----------------------------
Dear Pedro,
It is OK, but I meant Is it possible to catch them within your ASP.net codes?
For example imagine that you are going to represent how many rows were affected by the action that user has submited.

many thanks,
Amin

15 February, 2008 19:46
----------------------------
Dear Pedro,
Thank you very much for this post and the answers.

Cheers friend,
Amin

16 February, 2008 15:39
----------------------------
Dear Pedro,
I read a little about it.
Unfortunately I cannor register for it because of lots of tasks that I have to do and the lack of time. :-(
But I hope you can participate in it and heard you alot about it.

Thanks,
Amin

17 February, 2008 13:05
----------------------------
Dear Pedro,
I am in Iran. Why do you think that I am in UK??
Regards,
Amin

18 February, 2008 04:14

LinkWithin

Related Posts Plugin for WordPress, Blogger...