Execute SSIS package
-
Hi. I have a SSIS package named TESTPACKAGE on the 2005 SQL Server. How I can exeucte it from ASP.NET? I found some articels that refer to Microsoft.SQLServer assembly but I use older .NET Framework version (1.1) and this assembly is not availabe for me in Visual Studio (2003). I read something about DTEXEC command. Can I use it in the Stored Procedure to run SSIS. I used this code in the Stored Procedure to run DTS package on the previsous/older SQL Server version.
CREATE PROCEDURE dbo.exportData AS Exec master..xp_cmdshell 'DTSRUN /SMYSERVER /NTESTDTS /Umyaccess /Pmypassword' GOThank you, -
Hi. I have a SSIS package named TESTPACKAGE on the 2005 SQL Server. How I can exeucte it from ASP.NET? I found some articels that refer to Microsoft.SQLServer assembly but I use older .NET Framework version (1.1) and this assembly is not availabe for me in Visual Studio (2003). I read something about DTEXEC command. Can I use it in the Stored Procedure to run SSIS. I used this code in the Stored Procedure to run DTS package on the previsous/older SQL Server version.
CREATE PROCEDURE dbo.exportData AS Exec master..xp_cmdshell 'DTSRUN /SMYSERVER /NTESTDTS /Umyaccess /Pmypassword' GOThank you,Hope this will Help you. DECLARE @cmd VARCHAR(255) DECLARE @Wait INT DECLARE @result INT, @OLEResult INT DECLARE @ShellID INT SET @Wait = 0 SET @cmd = 'DTEXEC /sq "TestSSPSPackageName" /ser /Set \Package.Variables[User::varTargetName].Properties[Value];"c:\dtsxTest\Test2.txt"' -- create instance of OLE object EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT IF @OLEResult <> 0 SELECT @result = @OLEResult IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult) -- run package EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', NULL, @cmd, 0, @Wait IF @OLEResult <> 0 SELECT @result = @OLEResult IF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult) If @OLEResult <> 0 EXEC sp_OAGetErrorInfo @ShellID, @OLEResult -- drop object EXECUTE @OLEResult = sp_OADestroy @ShellID -- I think this will run an an asynchronous process.
Regards, Satips.:rose:
-
Hope this will Help you. DECLARE @cmd VARCHAR(255) DECLARE @Wait INT DECLARE @result INT, @OLEResult INT DECLARE @ShellID INT SET @Wait = 0 SET @cmd = 'DTEXEC /sq "TestSSPSPackageName" /ser /Set \Package.Variables[User::varTargetName].Properties[Value];"c:\dtsxTest\Test2.txt"' -- create instance of OLE object EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT IF @OLEResult <> 0 SELECT @result = @OLEResult IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult) -- run package EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', NULL, @cmd, 0, @Wait IF @OLEResult <> 0 SELECT @result = @OLEResult IF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult) If @OLEResult <> 0 EXEC sp_OAGetErrorInfo @ShellID, @OLEResult -- drop object EXECUTE @OLEResult = sp_OADestroy @ShellID -- I think this will run an an asynchronous process.
Regards, Satips.:rose:
Thank you. As I understand this is a Stored Procedure, right? The other question I have is: can I just run this command separetly from my code:
EXEC master..xp_cmdshell 'DTEXEC /sq "TEST_PACKAGE_NAME" /ser TEST_SERVER'Package doesn't accept any parameters. I created it when I was importing test.txt file and selected the option to create package on the fly. By the way how can I modify it if needed? Thanks a lot.