DTS from Winform.

[jdionne]

I have a WInform app that runs a DTS. When I developed it on the SQL
box and executed it, it ran fine. I used the Trusted connection
parameter for the DTS. I don't want to use passwords. I packaged up
the project and put in on another box. Now I get SQL Server errors:
Description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not
exist or access denied.

Even when I change the connection type for the DTS back to default and
put in the sa password I get the same error.

I know I could make it an ASP.Net app and run it on the SQL Server and
simply use a trusted connection, but I need to use a WInform on a
terminal server. I don't understand why I get an error when I use a
default connection and the sa password, AND when switch it and use a
trusted connection. Are Winforms the same as webform apps as in you
have to do impersonation when they are on different boxes than the SQL
Server? I don't know if I can get the admins to set up an account for
us to cross domain authenticate.

I'm stuck.

Thanks in advance,
John

 

[Guest]

It might help if you try running the DTS package from a stored procedure
instead. The stored procedure can execute the DTS package by calling dtsrun
through xp_cmdshell.

HTH, Jakob.

 

[jdionne]

I don't want to. I want to run it from a winform on a remote machine.
I made a test winform program that used SQL Server auth AND Windows
auth and it worked fine:

'cstring = "server=SERVER;database=DYNAMICS;uid=id;pwd=pwd;"
OR
cstring =
"server=SERVER;database=MBSCUSTOM;Trusted_Connection=Yes;
con.ConnectionString = cstring
con.Open()
rtbMessage.Text = "Trusted Connection State : " &
con.State.ToString & vbCrLf

Both these statements returned Trusted Connection State : Open from the
remote client. Why won't either of these work in another program on
the same machine that tries to execute a DTS:

package.LoadFromSQLServer(server, id,pwd,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , , packageName)
OR
'package.LoadFromSQLServer(server, , ,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , ,
packageName)

I am getting killed here. I'm sure you can run a DTS via VB.Net
Winforms from a remote machine. I can't find anything to help me out.
I'm sure I'm not the only person who has ever tried this. I really
don't want to run it as a web app on the SQL Server box....but that is
my only alternative if I can't get this to work.

 

[Guest]

It is indeed possible to run a DTS package on a remote server. I have done
it using code similar to the following (C#):

Package2Class package = new Package2Class();
object pVarPersistStgOfHost = null;
DTSSQLServerStorageFlags storageFlags =
DTSSQLServerStorageFlags.DTSSQLStgFlag_Default;
package.LoadFromSQLServer(serverName, serverUserName, serverPassWord,
storageFlags, null, null, null, packageName, ref pVarPersistStgOfHost);
package.Execute();

It also works with a trusted connection (DTSSQLStgFlag_UseTrustedConnection
and null for serverUserName). I also tried it from VB.net and it works as
well.

I while ago I wrote a simple C# class for executing DTS packages which also
sets up connection points for receiving error and progress notifications from
the running package. If you want, I would be glad to e-mail the code to you.

Regards, Jakob.