Hi all,
I am trying to execute SSIS package using Stored Procedure....I got this code from one of the threads I am able to understand this but the value that is passed in @params variable I don't know how to pass for my package.....In my SSIS package I have to pass my file location path along with file name as I dynamically get files and I have to validate that data and redirect the
error output into another folder whose location is also dynamic as it varies from file to file so can any one help me understand the that passed to @params
so that I can write for mine or if you have any other suggestions let me know. Thank you in advance
------- Enable Xp_cmdshell-------------
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
--------- Stored Procedure To Call SSIS Package-------------
CREATE PROCEDURE [dbo].[sp_SSIS_Package_Execution](@packagename varchar(20),@servername varchar(10))
AS
BEGIN
SET NOCOUNT ON
DECLARE @params varchar(8000), @ssisstr varchar(8000)
----package variables, which we are passing in SSIS Package.
SET @params = '/set \package.variables[FileName].Value;"\"\\127.0.0.1\Common\SSIS\NewItem.xls\"" /set \package.variables[CreatedBy].Value;
"\"Pankaj\"" /set \package.variables[ContractDbConnectionString].Value;"\"Data Source=myserver\SQL2K5;User ID=sa;Password=sapass;
Initial Catalog=Items;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;\"" /set \package.variables[BatchID].Value;"\"1\""
/set \package.variables[SupplierID].Value;"\"22334\""'
----now making "dtexec" SQL from dynamic values
SET @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
SET @ssisstr = @ssisstr + @params
------ 'dtexec /f "PackageNameWithFullPath.dtsx"'
-----print line for varification
PRINT @ssisstr
----now execute dynamic SQL by using EXEC.
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @ssisstr
SELECT @returncode
END
I am trying to execute SSIS package using Stored Procedure....I got this code from one of the threads I am able to understand this but the value that is passed in @params variable I don't know how to pass for my package.....In my SSIS package I have to pass my file location path along with file name as I dynamically get files and I have to validate that data and redirect the
error output into another folder whose location is also dynamic as it varies from file to file so can any one help me understand the that passed to @params
so that I can write for mine or if you have any other suggestions let me know. Thank you in advance
------- Enable Xp_cmdshell-------------
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
--------- Stored Procedure To Call SSIS Package-------------
CREATE PROCEDURE [dbo].[sp_SSIS_Package_Execution](@packagename varchar(20),@servername varchar(10))
AS
BEGIN
SET NOCOUNT ON
DECLARE @params varchar(8000), @ssisstr varchar(8000)
----package variables, which we are passing in SSIS Package.
SET @params = '/set \package.variables[FileName].Value;"\"\\127.0.0.1\Common\SSIS\NewItem.xls\"" /set \package.variables[CreatedBy].Value;
"\"Pankaj\"" /set \package.variables[ContractDbConnectionString].Value;"\"Data Source=myserver\SQL2K5;User ID=sa;Password=sapass;
Initial Catalog=Items;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;\"" /set \package.variables[BatchID].Value;"\"1\""
/set \package.variables[SupplierID].Value;"\"22334\""'
----now making "dtexec" SQL from dynamic values
SET @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
SET @ssisstr = @ssisstr + @params
------ 'dtexec /f "PackageNameWithFullPath.dtsx"'
-----print line for varification
PRINT @ssisstr
----now execute dynamic SQL by using EXEC.
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @ssisstr
SELECT @returncode
END
