Calling SSIS Package through Store Procedure

Question

Calling SSIS Package through Store Procedure

rocky_498

SSCertifiable

Points: 6532
More actions
December 4, 2014 at 6:57 pm

#293138

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

You can create a SQL agent job then create a step that executes the package. SSIS Packages are one of the options under Steps for "Type"

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

rocky_498 SSCertifiable Points: 6532 More actions · Answer 2

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?

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

rocky_498 (12/4/2014)
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?

Argh, I put together a longer reply and the web page crashed. I understand the issue better now.

I don't have access to SSIS at the moment but I am sure I have done this in the past successfully:

(I'm assuming you know this and just mentioning it for context) In an Agent Job > Steps... If you set it to SSIS Package, there's a tab for "Set Values"; that's were you would add one or more SSIS variables.

What if you:

1) Use dynamic sql to create an agent job and pass your variable to that dynamic sql statement.

2) Use sp_start_job to run the job

3) Use sp_delete_job to Delete it once it's done

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

Create a job that uses variables stored in a database, to configure the package then, simply write a script that updates the necessary variables, to the required values, and execute the job.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 5

rocky_498 (12/4/2014)
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?

If you are using SSIS, then why pass the variable. You can configure your package to used the variable from SSMS itself.

I mean using SQL Server Configuration in SSIS package.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Under most circumstances User Initiated Job events or SSIS package firings are bad ideas. The reasoning behind this is that it has very little collision detection. It's quite easy for multiple users to overwrite each other's files, truncate staging tables another instance was in the middle of using, things of that nature. With jobs the user may get timeouts or the like as another user is already using the job... and you may have overwrote variables the original job was using before the second user tried to fire the job.

That's why you're finding it so hard to implement. It should only be done with extreme caution.

That said, what are you trying to have the package do for you? We may be able to offer a better streamlined solution.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA