How can I run an SSIS package on a remote web hosting SQL SERVER 2005

[idea4]

Goal: transform and move data from an MS Access database to a shared SQLServer 2005 database located on a web hosting site whenever the MS Access database is updated.

background: I have a shared SQLServer 2000 and SQLServer2005 database on a web hosting server. I have been running DTS from Access to transform and upload the data to the SQLServer 2000 database by adding a reference to DTS Package object library and running it from Access. Everything works fine.....

Now I'm trying to upgrade from DTS to SSIS. I rewrote all my DTS packages to SSIS packages using the developer edition of SQL Server 2005. Now the Hosting company is telling me I can only run the SSIS on their job agent at certain preset times. I thought maybe I could run them from a stored procedure, but I don't have permission to use the xp_cmdshell. I asked if there was a way to trigger the job agent and they said I didn't have permission.

It's very clear that the web hosting company is not familiar with SSIS or DTS. Does anybody have any suggestions? It took me a long time to get use to SSIS, now I really like it, and would like to keep using it, but not when my hosting cost is going to triple because I would need a dedicated server.

Thanks in advance.

 

[cmartin75]

With the command-line utility "dtexec" of SQL Server 2005. You can execute packages saved as "stored packages" on the SSIS server.

Another way would be execute the package from a local machine, and connect the SSIS data sources to the server of the hosting company

 

[idea4]

My understanding is that to use dtexec, you need SSIS installed on the client computer. My users don't have SSIS installed.

I was previously running DTS from the local machine and connecting to the server of the hosting company. I just can't figure out how to do it with SSIS.

here's the VB code I used to run DTS from local machines with no SQL Server installed:

Dim dtsp As New DTS.package
' uses dtsobject package library reference
dtsp.LoadFromSQLServer _
ServerName:="servername", _
ServerUserName:="username", _
ServerPassword:="password", _
PackageName:="DTSpackagename"

dtsp.Connections("Connection 1").DataSource = "C:\accessdb.mdb"
dtsp.Execute

I think I just need to use different references. Any ideas?

 

[MDXer]

Now I am all for upgrading out of DTS but in an enviroment where you are so limited I would follow the "if it isn't broke don't fix it" philosophy.

However if what you are trying to do is load something from your local machine to the hosted server then it may be possible if they expose the connection to outside sources. you would just need to configure your ole db connection property.

If everything is on the hosted server then you are limited by what their policies allow.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[idea4]

The problem with staying with DTS is that DTS came with SQL Server 2000. I believe they are not going to be supporting it much longer with SQLServer 2008 comming out. Also, I don't know if you can update DTS packages with SSIS, which basically means, I'd need to stay with all the old SQLServer 2000 components (enterprise manager) when most of my customers already have SQL Server 2005.

I'm still trying to see if I can run it with similar code I used for the DTS but I'm having trouble finding somebody at the hosting company that is familiar with SSIS. I've also been checking other hosting companies, but I'm running into the same problem. It seems not much is known about SSIS.