Web Services to run SSIS package

Thanks I add

<authentication mode="Windows"/>

<identity impersonate="true"/>

However, I can not use web services right? I still don't understand in what scenario we can use web services or does really this works.

I am still new to web services but if web services to run ssis package only can work within the same box with http://localhost:IP call then do we really need it.

It's very simple question to ask : machine "A" has web service can machine "B" use that services? in this case to run ssis package?

Thank you - Ashok

AshokOjha at 2007-10-2 >

I am going to try DTExec using web services but if you look into Michael Entin's WebLog http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

in number #2 ASP.NET specific he says same issues "Win32 CreateProcess function ignores the thread impersonation". Isn't this apply to web services?

--

I tried same result doesn't run ssis package from web services in hosted in web server. It runs from Visual Studio runs fine, then I right click project "Publish Web site" Target Location http://localhost/WebSer then open browser

http://localhost/WebSer/Service.asmx?op=HelloWorld doesn't run the package.

using System;

using System.Web;

using System.Web.Services;

using System.Web.Services.Protocols;

using System.Diagnostics;

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

public class Service : System.Web.Services.WebService

public Service () {

//Uncomment the following line if using designed components

//InitializeComponent();

[WebMethod]

public string HelloWorld() {

//Declare and instantiate a new process component.

System.Diagnostics.Process process1;

process1 = new System.Diagnostics.Process();

//Do not receive an event when the process exits.

process1.EnableRaisingEvents = false;

//The "/C" Tells Windows to Run The Command then Terminate

string strCmdLine;

strCmdLine = " /f C:\\SSIS\\TestPkg\\Package.dtsx";

System.Diagnostics.Process.Start("dtexec", strCmdLine);

return "Done";

AshokOjha at 2007-10-2 >

Thanks JayH. At last it worked even though little over engineering. I am going to look for other ways to run dtsx packages but it does works.

-

Before you try this code work with systems guys to change AdministrativeTools->Local SecurityPolicy->UserRightsAssignment->Increse quotas & Replace a process level token to add your NT login. For details use JayH's link.

using System;

using System.Web;

using System.Web.Services;

using System.Web.Services.Protocols;

using System.Diagnostics;

using System.Runtime.InteropServices;

using System.Security.Principal;

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

public class Service : System.Web.Services.WebService

[StructLayout(LayoutKind.Sequential)]

public struct STARTUPINFO

public int cb;

public String lpReserved;

public String lpDesktop;

public String lpTitle;

public uint dwX;

public uint dwY;

public uint dwXSize;

public uint dwYSize;

public uint dwXCountChars;

public uint dwYCountChars;

public uint dwFillAttribute;

public uint dwFlags;

public short wShowWindow;

public short cbReserved2;

public IntPtr lpReserved2;

public IntPtr hStdInput;

public IntPtr hStdOutput;

public IntPtr hStdError;

[StructLayout(LayoutKind.Sequential)]

public struct PROCESS_INFORMATION

public IntPtr hProcess;

public IntPtr hThread;

public uint dwProcessId;

public uint dwThreadId;

[StructLayout(LayoutKind.Sequential)]

public struct SECURITY_ATTRIBUTES

public int Length;

public IntPtr lpSecurityDescriptor;

public bool bInheritHandle;

[DllImport("kernel32.dll", EntryPoint = "CloseHandle", SetLastError = true, CharSet = CharSet.Auto, CallingConvention = CallingConvention.StdCall)]

public extern static bool CloseHandle(IntPtr handle);

[DllImport("advapi32.dll", EntryPoint = "CreateProcessAsUser", SetLastError = true, CharSet = CharSet.Ansi, CallingConvention = CallingConvention.StdCall)]

public extern static bool CreateProcessAsUser(IntPtr hToken, String lpApplicationName, String lpCommandLine, ref SECURITY_ATTRIBUTES lpProcessAttributes,

ref SECURITY_ATTRIBUTES lpThreadAttributes, bool bInheritHandle, int dwCreationFlags, IntPtr lpEnvironment,

String lpCurrentDirectory, ref STARTUPINFO lpStartupInfo, out PROCESS_INFORMATION lpProcessInformation);

[DllImport("advapi32.dll", EntryPoint = "DuplicateTokenEx")]

public extern static bool DuplicateTokenEx(IntPtr ExistingTokenHandle, uint dwDesiredAccess,

ref SECURITY_ATTRIBUTES lpThreadAttributes, int TokenType,

int ImpersonationLevel, ref IntPtr DuplicateTokenHandle);

public Service () {

//Uncomment the following line if using designed components

//InitializeComponent();

[WebMethod]

public string HelloWorld() {

IntPtr Token = new IntPtr(0);

IntPtr DupedToken = new IntPtr(0);

bool ret;

SECURITY_ATTRIBUTES sa = new SECURITY_ATTRIBUTES();

sa.bInheritHandle = false;

sa.Length = Marshal.SizeOf(sa);

sa.lpSecurityDescriptor = (IntPtr)0;

Token = WindowsIdentity.GetCurrent().Token;

const uint GENERIC_ALL = 0x10000000;

const int SecurityImpersonation = 2;

const int TokenType = 1;

ret = DuplicateTokenEx(Token, GENERIC_ALL, ref sa, SecurityImpersonation, TokenType, ref DupedToken);

STARTUPINFO si = new STARTUPINFO();

si.cb = Marshal.SizeOf(si);

si.lpDesktop = "";

string commandLinePath;

commandLinePath = "dtexec /f C:\\SSIS\\TestPkg\\Package.dtsx";

PROCESS_INFORMATION pi = new PROCESS_INFORMATION();

ret = CreateProcessAsUser(DupedToken, null, commandLinePath, ref sa, ref sa, false, 0, (IntPtr)0, "c:\\", ref si, out pi);

if (ret != false)

return "Done";

AshokOjha at 2007-10-2 >

Don't tell to your boss. Try this

http://labs.developerfusion.co.uk/convert/csharp-to-vb.aspx

Just kidding - Ashok

AshokOjha at 2007-10-2 >