Getting SSIS running on Web Server

I need to be able to run SSIS packages form an asp.net (win 2k3) web server. Wrox has a book out "Professional SQL Server 2005 Integration Services" where they call the dtsx package directly using the following vb.net snipette:

Imports Microsoft.SqlServer.Dts.DtsClient

Dim ssisConn As New DtsConnection
ssisConn.ConnectionString = String.Format("-f ""{0}""", strMyFilePath)
ssisConn.Open()

As you would expect this works great on a workstation that has BIDS installed on it but does not work on a web server where sql client tools are not installed. Without install sql tools on the server what needs to be done to get this functioning as coded? How about calling packages that are installed on the server?

If anyone knows of any sites or books that cover this in detail I would appreciate the info. I only seem to be able to find bits and pieces.

thanks in advance

You can't. To my knowledge, you'll need the SSIS client tools installed. The packages are executed using dtexec (or a variant, I suppose).|||Searching turned this up.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=172501&SiteID=1|||

I figured it wouldn't be possible to run packages from a web server w/out having ssis installed. I wanted to make sure that some combonation of dll's copied to the bin directory wouldn't get me what I needed. I opted to expose a web service on the sql server and consume the service from the web box. The following kb article describes how to do it via a job and a web service.

http://msdn2.microsoft.com/de-de/library/ms403355.aspx

Getting SSIS running on Web Server

I need to be able to run SSIS packages form an asp.net (win 2k3) web server. Wrox has a book out "Professional SQL Server 2005 Integration Services" where they call the dtsx package directly using the following vb.net snipette:

Imports Microsoft.SqlServer.Dts.DtsClient

Dim ssisConn As New DtsConnection
ssisConn.ConnectionString = String.Format("-f ""{0}""", strMyFilePath)
ssisConn.Open()

As you would expect this works great on a workstation that has BIDS installed on it but does not work on a web server where sql client tools are not installed. Without install sql tools on the server what needs to be done to get this functioning as coded? How about calling packages that are installed on the server?

If anyone knows of any sites or books that cover this in detail I would appreciate the info. I only seem to be able to find bits and pieces.

thanks in advance

You can't. To my knowledge, you'll need the SSIS client tools installed. The packages are executed using dtexec (or a variant, I suppose).|||Searching turned this up.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=172501&SiteID=1|||

I figured it wouldn't be possible to run packages from a web server w/out having ssis installed. I wanted to make sure that some combonation of dll's copied to the bin directory wouldn't get me what I needed. I opted to expose a web service on the sql server and consume the service from the web box. The following kb article describes how to do it via a job and a web service.

http://msdn2.microsoft.com/de-de/library/ms403355.aspx

Getting OLE DB connection properties in script

I have SSIS packages that send success/failure email upon completion, and I'd like to add a note that identifies the server and database used. I can certainly add variables to the packages and use them when constructing the email, but I'd prefer to get the information directly from the OLE DB connection itself. Is there a way to access the connection string from within a control-flow VB script task? Furthermore, can I get the data source and initial catalog from that connection string, or do I need to parse it myself?

Thanks!

Phil

Yeah you can do that. See here: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

If you don't want to parse it yourself you could try using the Properties collection using something like the following:

Dts.Connections("Errors").Properties("Format").GetValue(o)

Unfortunately that doesn't actually work cos I don't know what you're supposed to supply to the GetValue() method and the docs are a bit thin. But you can havea go with it if you like.

|||

Thanks, Jamie. You're right - I couldn't make it work with GetValue. However I was able to skin this particular cat thusly:

Dim _dbCxcn As String
Dim _inx As Integer
Dim _dbServer As String
Dim _dbCatalog As String

_dbCxcn = Dts.Connections("My_OLEDB_Connection").ConnectionString
_inx = InStr(_dbCxcn, "Data Source=")
_dbServer = Mid(_dbCxcn, _inx + 12, InStr(_inx, _dbCxcn, ";") - (_inx + 12))
_inx = InStr(_dbCxcn, "Initial Catalog=")
_dbCatalog = Mid(_dbCxcn, _inx + 16, InStr(_inx, _dbCxcn, ";") - (_inx + 16))

Then I constructed the email message using the _dbServer and _dbCatalog variables.

Thanks again!

Phil