DTS from Winform.

  • Thread starter Thread starter jdionne
  • Start date Start date
J

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
 
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.
 
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.
 
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.
 
Back
Top