execute DTS from asp.net 2.0 - package does not exist error

I am trying to execute a dts from asp.net, but getting an error

“The specified DTS Package ('Name = 'DTS_USERS_LIST'; ID.VersionID = {[not specified]}.{[not specified]}') does not exist.”

I know I have saved the dts package on the sqlserver, and have run it from the server successfully.

Here is my code:

Package2Class package = new Package2Class();

object pVarPersistStgOfHost = null;

package.LoadFromSQLServer(

sServer,

sUID,

sPWD,

//DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,

DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,

null,

null,

null,

sPkgName,

ref pVarPersistStgOfHost);

package.Execute();

package.UnInitialize();

// force Release() on COM object

//

System.Runtime.InteropServices.Marshal.ReleaseComObject(package);

package = null;

any inputs would be aprpeciated.

I see this is a DTS 2000 issue. This forum only covers SSIS in SQL Server 2005.

Have a look at this article

http://www.c-sharpcorner.com/Code/2002/Sept/DTSnCS.asp

- see if it has anything useful for you. Otherwise, you may want to look at www.sqldts.com and the forums there.

Donald Farmer

|||Hello Donald:
This is indeed an SSIS package issue. The SSIS package was saved in mssql 2005 server. The code above is in asp.net 2.0. Using the word DTS is I guess an old habit.

Your response for be appreciated.

thx
|||

But it looks like you are using DTS code here. The interfaces you are listing - and the resultant error message are DTS interfaces and erros, not SSIS.

For SSIS execution, have a look at http://msdn2.microsoft.com/zh-cn/library/ms136090.aspx

hth

Donald Farmer

|||

I had my machine switched to a Chinese locale for some (much needed) practice and pasted a link to Chinese books-online.

Here is the US English link: http://msdn2.microsoft.com/en-us/library/ms136090.aspx

对不起

Donald Farmer

|||thank you for the help. I now have been able to run SSIS stored in MSDB by using stored procedure by following the sql agent method. Here is the sp that i used

exec master..xp_cmdshell 'DTEXEC /U sa /P xxx /Ser servername /SQ "DTS_USERS_LIST"/de ""'

I now want to add an input parameter to the sp and pass that parameter onto the SSIS. I know how to pass the parameter from asp.net to the sp, but cannot figure out how to pass the parameter to the SSIS on the sp. I figure i need to append it somehow to the dtexec code above. Can you point me to the right direction?

A second question, how do I modify the SSIS that is stored in MSDB?

thx again,

|||

There is a much better way of executing a package using SQL Agent. See http://msdn2.microsoft.com/en-us/library/ms139805(SQL.90).aspx

xp_cmdshell is off by default for good reasons - we do not recommend using it, as it opens up a significant attack vector for security.

SSIS packages in MSDB are deployed packages: you should still have a design-time version available in BIDS.

See http://msdn2.microsoft.com/en-us/library/ms137633.aspx and http://msdn2.microsoft.com/en-us/library/ms141772.aspx

In 2005, there is a clear distinction between the development environment (Business Intelligence Development Studio) and the deployed / admin environment (SQL Server Management Studio). We support a true "develop and test -> deploy to production -> administer" methodology.

In other words, rather than editing the version in MSDB, you would edit the version you have in your development environment, and redeploy the new version to your server (after thorough testing, of course.)

Donald