Executing SSIS Package USing Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Executing SSIS Package USing Stored Procedure

Author

Topic

karthika9
Starting Member

8 Posts

Posted - 2009-10-26 : 09:04:48

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

The Code is :

------- 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)
-- --my package name
--set @packagename = 'LatestClaimsValidation'
-- -- my server name
--set @servername = 'ATLSQL2\ATLSQL2'
----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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-26 : 09:08:46

Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=134925

Madhivanan

Failing to plan is Planning to fail

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-26 : 12:38:39

I would advise not using xp_cmdshell.
Also having this: ;User ID=sa;Password=sapass in plain text is not advisable.

Why do you need to execute the package from a stored procedure?
Are your configurations stored in a file or sql server table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources