August 19, 2009 at 8:00 am
Hi Philip, Please assist
I need to call a SSIS package from a sp which is triggered from a ASP.Net app. THe SSIS has variables which need to be passed from the app via the stored procedure to the package. do you know i can do this. I had this working on SQL 2000 see my code below:
SELECT @ServerName = @@servername,
@DatabaseName = DB_NAME()
SELECT @Import_Enabled,
@Import_Filename,
@Import_Package_Name,
@ServerName,
@DatabaseName
exec sp_OACreate 'DTS.Package', @objPackage output
select 'obj', @objPackage
exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,
@ServerName = @ServerName, @Flags = 256, @PackageName = @Import_Package_Name
if @rc 0
BEGIN
select 'Package Create'
exec sp_displaypkgerrors
EXEC @hr = sp_OAGetErrorInfo @objPackage, @source OUT, @description OUT
select @source, @description
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ServerName").value', @ServerName
if @rc 0
BEGIN
select 'srvname'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("Loyalty_Mandatory").value', @Loyalty_Mandatory
if @rc 0
BEGIN
select 'Loyalty_Mandatory'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Bulk_Import_Batch").value', @ID_Bulk_Import_Batch
if @rc 0
BEGIN
select 'ID_Bulk_Import_Batch'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("Received_Date").value', @Received_Date
if @rc 0
BEGIN
select 'Received_Date'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("Created_By").value', @Created_By
if @rc 0
BEGIN
select 'Created_By'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("DatabaseName").value', @DatabaseName
if @rc 0
BEGIN
select 'dbname'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("Import_Filename").value', @Import_Filename
if @rc 0
BEGIN
select 'Filename'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Segment").value', @ID_Segment
if @rc 0
BEGIN
select 'ID_Seg'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Channel").value', @ID_Channel
if @rc 0
BEGIN
select 'ID_C'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Product").value', @ID_Product
if @rc 0
BEGIN
select 'ID_P'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Request_Type").value', @ID_Request_Type
if @rc 0
BEGIN
select 'ID_RT'
set @output=@rc
RETURN
END
exec @rc = sp_OAMethod @objPackage, 'Execute'
if @rc 0
BEGIN
set @output=@rc
RETURN
END
exec @rc = sp_OADestroy @objPackage
if @rc 0
BEGIN
set @output=@rc
RETURN
END
SET @output=0
Your help will be highly appreciated
August 19, 2009 at 8:02 am
Hi All, Please assist
I need to call an SSIS package from a sp which is triggered from a ASP.Net app. THe SSIS has variables which need to be passed from the app via the stored procedure to the package. do you know i can do this. I had this working on SQL 2000 see my code below:
SELECT @ServerName = @@servername,
@DatabaseName = DB_NAME()
SELECT @Import_Enabled,
@Import_Filename,
@Import_Package_Name,
@ServerName,
@DatabaseName
exec sp_OACreate 'DTS.Package', @objPackage output
select 'obj', @objPackage
exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,
@ServerName = @ServerName, @Flags = 256, @PackageName = @Import_Package_Name
if @rc 0
BEGIN
select 'Package Create'
exec sp_displaypkgerrors
EXEC @hr = sp_OAGetErrorInfo @objPackage, @source OUT, @description OUT
select @source, @description
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ServerName").value', @ServerName
if @rc 0
BEGIN
select 'srvname'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("Loyalty_Mandatory").value', @Loyalty_Mandatory
if @rc 0
BEGIN
select 'Loyalty_Mandatory'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Bulk_Import_Batch").value', @ID_Bulk_Import_Batch
if @rc 0
BEGIN
select 'ID_Bulk_Import_Batch'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("Received_Date").value', @Received_Date
if @rc 0
BEGIN
select 'Received_Date'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("Created_By").value', @Created_By
if @rc 0
BEGIN
select 'Created_By'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("DatabaseName").value', @DatabaseName
if @rc 0
BEGIN
select 'dbname'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("Import_Filename").value', @Import_Filename
if @rc 0
BEGIN
select 'Filename'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Segment").value', @ID_Segment
if @rc 0
BEGIN
select 'ID_Seg'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Channel").value', @ID_Channel
if @rc 0
BEGIN
select 'ID_C'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Product").value', @ID_Product
if @rc 0
BEGIN
select 'ID_P'
set @output=@rc
RETURN
END
exec @rc = sp_OASetProperty @objPackage, 'GlobalVariables("ID_Request_Type").value', @ID_Request_Type
if @rc 0
BEGIN
select 'ID_RT'
set @output=@rc
RETURN
END
exec @rc = sp_OAMethod @objPackage, 'Execute'
if @rc 0
BEGIN
set @output=@rc
RETURN
END
exec @rc = sp_OADestroy @objPackage
if @rc 0
BEGIN
set @output=@rc
RETURN
END
SET @output=0
Your help will be highly appreciated
August 19, 2009 at 8:19 am
HI I'm using this simple code and it works fine for me. Been using it for more than 2 years and never had a problem.
Good Luck.-
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_RunDTSX]
@DTSX as char(50)
AS
DECLARE @returncode INT
if @DTSX='ANY OPTION VALUE THAT CAN BE USED AS A PARAMETER TO RUN ANY DTSX'
BEGIN
EXEC @returncode = master..xp_cmdshell 'dtexec /Dts \MSDB\FOLDERNAME_WHERE_DTSX_IS_STORED\DTSX_NAME_TO_RUN'
END
August 21, 2009 at 1:36 am
Hi Hugo,
Thanks for your response, The sp_cmdshell seem to be the best way to do this but the problem is tht the policy in our company wont let us use it for security reason, do you have an alternative??
I'm trying to set up the package configuration(SQL server one) which create a table on a database with all the configuration, so i'll try and update the table before calling the job which will execute the ssis package, not sure if this is going to work.
If you have other suggestion please let me know.
May 15, 2015 at 7:50 am
Hi, I'm experiencing the same problem, can you please share your solution?
Thanks!
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply