| Author |
Topic |
|
tooba111
Starting Member
22 Posts |
 Posted - 2014-12-04 : 20:56:23
|
| Hi Guys,I need urgent help.I am calling Store Procedure from my C# Code and inside the SP, I am calling my SSIS Package.It is working fineon my local because I have all rights to run xp_cmdshell COMMAND. Now I have to transfer this SP to QA and Prod and I don't have rights to run xp_cmdshell COMMAND. Here is my SP.declare @cmd varchar(1000) SET @ssispath = 'SSIS Package Path where my .dtsx package' set @ExcelF = 'Passing My source file name and full path'select @cmd = 'C:\"program files (x86)"\"Microsoft SQL Server"\100\DTS\Binn\DTEXEC.exe /F "' + @ssispath + '"'select @cmd = @cmd + ' /X86 /SET \Package.Variables[User::ExcelF].Properties[Value];"' + @ExcelF + '" /X86 'exec master..xp_cmdshell @cmdMy question is, is there other way to run/execute above Store Procedure/SSIS without XP_CMDSHELL Command?Please guide/advise. Its urgent.Thank You. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-04 : 21:06:19
|
| You could create a job that runs the package and then have the stored procedure run sp_start_job. If the stored procedure needs to call the package synchronously, then you'll need to check job history to see when the job finishes before proceeding with the stored procedure.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
tooba111
Starting Member
22 Posts |
Posted - 2014-12-04 : 21:54:29
|
| Hi tkizer, thank you for your message. I know I can create job and then call from SP. Is there other way I can use? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tooba111
Starting Member
22 Posts |
Posted - 2014-12-04 : 23:08:52
|
| I will check and let you know, One more thing.I know, I can create SQL agent job and run that job through SP sp_start_job 'JOB Name', but How I can pass variable? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-04 : 23:19:48
|
quote:
Originally posted by tooba111
I will check and let you know, One more thing.I know, I can create SQL agent job and run that job through SP sp_start_job 'JOB Name', but How I can pass variable?
You'd have to write to a table from the stored procedure, the job would the table and build the SSIS execution string, I think at least.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tooba
Posting Yak Master
224 Posts |
Posted - 2014-12-04 : 23:21:08
|
| Thanks for above link, However my SQL Server is 2008 R2. The above example in 2012. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-04 : 23:30:30
|
| I have no other options to offer you.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tooba
Posting Yak Master
224 Posts |
Posted - 2014-12-04 : 23:52:33
|
| tkizer, I am thinking and need your advise ( I am not sure its make sense or no)First SP = This SP in Testing server and this server, I have rights to execute xp_cmdshell commandSecond SP = In QA Server to call First SP in Testing ServerSo From C# code I will call Second SP and in Second SP, First SP call, is it possible ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-05 : 00:17:28
|
| Yes I think it will work, however I would highly not recommend it, especially if this code is going to rolled out to production. xp_cmdshell is a security concern and is typically disabled in production.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tooba
Posting Yak Master
224 Posts |
Posted - 2014-12-05 : 00:30:01
|
| xp_cmdshell is disable in QA as well but not in Testing Environment. I will call store procedure from production to Testing environment to run xp_cmdshell command, right? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-05 : 12:26:41
|
| You are on your own on that. I cannot offer advice on something that is completely against best security practices.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Calling SSIS Package through SP