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 2008 Forums
 SSIS and Import/Export (2008)
 Calling SSIS Package through SP

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 fine
on 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 @cmd

My 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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-04 : 22:39:34
Try the solution in here: http://www.mssqltips.com/sqlservertip/2992/how-to-execute-an-integration-services-ssis-package-from-a-sql-server-stored-procedure/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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?
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-04 : 23:30:30
I have no other options to offer you.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 command
Second SP = In QA Server to call First SP in Testing Server

So From C# code I will call Second SP and in Second SP, First SP call, is it possible ?

Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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?
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -