Stored Proc: xp_cmdshell to exec dtexec utility

Who is online?  0 guests and 1 members
Home  »  Forums   »  microsoft business intelligence   »  sql server   » Stored Proc: xp_cmdshell to exec dtexec utility

Stored Proc: xp_cmdshell to exec dtexec utility

Topic RSS Feed

Posts under the topic: Stored Proc: xp_cmdshell to exec dtexec utility

Posted: 7/28/2010

Jedi Youngling 98  points  Jedi Youngling
  • Joined on: 12/22/2009
  • Posts: 34

Hi all !

I hope this is the correct forum for this type of question.

 

Here's a synap of my program. 

1. C# GUI calls Stored Procedure

2. Stored Procedue Executes xp_cmdshell

3. xp_cmdshell Executes dtexec SSIS.dtsx package

(Sql Server 2008, BIDS, SSMS, Win7, 2 databases same instance)

 

The problem:  I'm running as admin.  I will not be the person running the GUI program.  The person running the GUI program will not have admin rights.

I'm using xp_cmdshell to run/execute the SSIS package. 

We've invested too much to scrap the idea.  

I'm not familiar with SQL Agent. 

If I use SQL Agent Job to run the SSIS package, can I pass in parameters that the SSIS package variables need?

Should I create Configuration Tables / Files for SSIS package variables?

Any suggestions welcomed.

 

 


Posted: 7/28/2010

Jedi Master 5646  points  Jedi Master
  • Joined on: 1/21/2010
  • Posts: 251

John,

Why not just create a job that executes the job. Then inside your stored procedure you can kick off the job. This should alleviate your security issue.

 

Brian


Posted: 7/28/2010

Jedi Youngling 98  points  Jedi Youngling
  • Joined on: 12/22/2009
  • Posts: 34

Brian,

By JOB I assume you mean Agent Job.  Am I allowed to pass parameters to the job - or did I miss you suggestion?

So I create an Agent Job to run the agent job that runs the Stored Proc?

Sorry for my confusion.


Posted: 7/28/2010

Jedi Master 5646  points  Jedi Master
  • Joined on: 1/21/2010
  • Posts: 251

Yes, I meant to create a SQL Agent Job. I think I see where you are coming from. You were passing parameters into the dtsexec right? You can get your values from a configuration file, and you may be able to make modifications to the "Command Line" tab inside the new job step properties. This may work, but I personally have not tried it.


Posted: 7/28/2010

Jedi Youngling 98  points  Jedi Youngling
  • Joined on: 12/22/2009
  • Posts: 34

Brian,

 

Okay.  Yes.  Thank you for your advice.  We have a SQL Agent job and I'm in the process of figuring this app out.  I noticed the tabs in the steps look familiar to the tabs in dtexec util. I'm sure I'll get it.   Just takes time to figure out.


Posted: 7/28/2010

Jedi Master 5646  points  Jedi Master
  • Joined on: 1/21/2010
  • Posts: 251

You're welcome! Please keep us posted if this works for you. It will be helpful when others search for resolutions to similar scenarios.


Posted: 7/29/2010

Jedi Youngling 98  points  Jedi Youngling
  • Joined on: 12/22/2009
  • Posts: 34

Update:

Sql Agent Job runs a T-Sql query that executes via xp_cmdshell, the SSIS package then imports data into the table.  That's the theory, tested doesn't work.

 

Question: can Sql Agent Job execute xp_cmdshell?

 

Eliminations:

-- Tested SSIS package with data source hard coded in SSIS package - loads data into table as expected.

 

Testing:

-- With data source hard coded in SSIS package, ran Agent Job with xp_cmdshell executing dtexec /FILE C:\Package.dtsx -- didn't work.  Which goes back to my inital question.  Can Agent Job execute xp_cmdshell?

 

A better Question:  can Sql Agent Job execute the SSIS package?

 

From the C# GUI, click IMPORT DATA, activates IMPORT DATA Stored Proc, activates SQL agent Job IMPORTSSISPKG.   Currently SQL Agent Job is setup to run a T-sql script that runs xp_cmdshell. 

 

Do I need to setup Sql Agent Job to run SSIS and use a configuration file that passes in the needed data source locations and variables?


Posted: 7/29/2010

Jedi Master 5646  points  Jedi Master
  • Joined on: 1/21/2010
  • Posts: 251

Depending on the environment, you may not be able to execute the xp_cmdshell from a proc, dts or any other way. I believe by default, this setting is disabled in the SQL Server Surface Area Configuration tool for SQL 2005. I can't remember right now how to get to that setting in 2008. I'll have to dig deeper. However, as I believe I stated before, you can run the package from a job. And you should be able to use a configuration file or table to have the values you need set.

At any rate, if you want to check to see what your settings are (and you have appropriate permissions that is) execute this to determine what your current xp_cmdshell setting is.

 EXEC sp_configure

Scroll all the way down to see what it the setting is for xp_cmdshell. Then if you really want to update this to ENABLED, you could do this either now and keep it enabled (which I would not suggest) or you could enable it by calling a stored proc that would enable it at the beginning of your main stored procedure and then disable by calling another proc that disables it.

Here is a link that will help you understand how to enable xp_cmdshell

 

 


Posted: 7/29/2010

Jedi Youngling 98  points  Jedi Youngling
  • Joined on: 12/22/2009
  • Posts: 34

UPDATE / Solution?  - You decide.

Resolution:  Lack of understanding as to how a SQL Agent Job functions when executed within a Stored Procedure.

Brief Recap: 

  • Stored procedure executed from a sql script.  Stored procedure expects 1 parameter.
  • Within Stored Procedure an Execution statement exists to execute a SQL Agent Job  (ie: EXEC msdb.dbo.sp_start_job @job_name = 'TestJob', @step_name = 'TestStep';)
  • Directly after statements to execute job, more statements to do more things to the database tables (updates, inserts, rowcounts ... etc.)

As to my resolution:

   When the Job executed, the message tab reported: job succeeded.  I immediately perform a SELECT * FROM MYTable and nothing is there.  So I assume the job failed at some point either in the SSIS package or the xp_cmdshell or the dtexec script.

   However, from a "new query" window, I enter the same Select statement and I see my data slowing being entered (repeating the Select statement) [In theory, I think this is the SSIS package executing in batches].

So it works, but it fails to give me some type of message as to where it is in the process.  I need more Agent Job understanding.

I created 3 more Job Steps and removed the excess Sql statements from the stored procedure.  The Agent Job now runs 4 steps in order, but only if the previous step was successful.

I basically broke down the original stored procedure into 4 Agent Job steps:

  1. Execute the SSIS package via xp_cmdshell dtexec /FILE....
  2. Update Table1
  3. Update Table2
  4. Update Table3

What I need now is how to tell where the Agent Job is in the process.  My next thought is to implement EXECUTE MSDB.DBO.SP_HELP_JOBHISTORY @job_name = 'TestJob';

My question is: do I put this execute statement in the same stored procedure as the sp_start_job command or execute it from a different query?

Any additional help or suggestions are always welcomed.

 


Posted: 7/29/2010

Jedi Master 5646  points  Jedi Master
  • Joined on: 1/21/2010
  • Posts: 251

If what you need is to determine if the job executed, then running the sp_help_jobhistory will give you this kind of details. You did say that this entire process is going to be getting kicked off by C# code right? If that is the case, then you could just call something like the script below at the end of your process... Be sure to update the @JobName accordingly.

I got this sample from HERE.

DECLARE @JobName VARCHAR(100)
SELECT @JobName = 'Sales'

SELECT --DISTINCT
j.[name],
jh2.[LastRun],
[Status] = CASE jh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END
,jh.run_date as [Job last run date]
,jh.run_time as [job last run time]
,j.enabled as [Job Enabled]
FROM
msdb.dbo.sysjobs j
LEFT OUTER JOIN
(
SELECT job_id,
MAX(CAST(
STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as
datetime)) AS [LastRun]
FROM
msdb.dbo.sysjobhistory jh
WHERE step_id = 0
GROUP BY job_id
) jh2
ON j.job_id = jh2.job_id
INNER JOIN
msdb.dbo.sysjobhistory jh
ON jh2.job_id = jh.job_id
AND CAST(
STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' +
STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as
datetime) = jh2.Lastrun
WHERE jh.step_id = 0
and j.name = @JobName 


 Then the result could be shown to the user. Really the possibilities are endless.

Good luck!

 


Posted: 8/4/2010

Jedi Youngling 98  points  Jedi Youngling
  • Joined on: 12/22/2009
  • Posts: 34

Brian,

I haven't been able to test this last post of yours.  The Agent Job worked as long as I(sysadmin) was running it and we were using Steps with Type: TSQL. 

When having a regular user run the Agent Job with Steps with Type: TSQL, the job didn't complete successfully.  It just hung in the stored procedure at Execute msdb.dbo.sp_start_job. 

I know the store proc would execute because we have update statements in the proc to load test data into a table on the dbase.

I feel the need to start a new thread, but after the last attempt - maybe I'll just continue this one.

Same over all objective:

  • Have a non-admin user (a regular user with no access to Sql Svr, no software for Sql loaded) execute a GUI program,
  • Have the GUI program run several stored procs which are kicked off by buttons on the GUI. 
  • Have the stored procs kick off Agent Jobs that run SSIS packages.
  • Be able to track the Agent Jobs to understand when completed and report that back to the user

I've scrapped the above solution for a solution that uses a config table in SSIS and an Agent Job that has a step Type: SSIS package with a Run as (my credentials proxy).  It doesn't work either.  I'm beside myself.

I have a slew of config table questions - should I post in Integration Services forum? 

 

Server\Instance\Security\Credentials\MyCreds: properties: Cred Name: MyCreds, Identity: Domain\MyLogin, Password: MyPassword, Confirm password: retype, click OK

Server\Instance\Sql Server Agent\Proxies\SSIS Package Execution\MyProx: Proxy name: MyProx, Cred Name: MyCreds, Active subsystems: OS(cmdExec) and SSIS Package

I'm a member of the sysadmin role - so I have automatic access under the Principals section.

 

Sql Server Agent\Jobs\Job to run SSIS package

  • Name - Job to run SSIS package
  • Owner - Domain\MyLogin
  • Category: Uncategorized local
  • description: none
  • Enabled: checked

Steps:

  • Name - Step 1 of Job to run SSIS package
  • Type - SQL Server integration Services
  • On Success - Quit the job reporting success  (( which I've found out this doesn't mean completed ))
  • On Failure - Quit the job reporting failure
  • Start step: Step 1

Edit Step:

  • Type: SSIS Package
  • Run as: MyProx
  • General Tab
  •       Package source: File system
  •       Package:  \\server\Dev\SSIS\Imports\Imports\ImportSSIS.dtsx
  • Configurations Tab
  •       Empty  (( this looks like it is for the configuration file ) - I have setup the configuration table )
  • Command files Tabl
  •       Empty
  • Data Sources
  •      Flat file source: pulled from configuration table
  •      Database destination
  • Execution Options - Verification
  •     Empty or not checked
  • Command Line
  •     /FILE \\server\Dev\SSIS\Imports\Imports\ImportSSIS.dtsx /CHECKPOINTING OFF /REPORTING E

Connection: Domain\MyLogin

Server: Server\Instance

 

Click okay, click okay, right click Agent Job, select Start Job, errors out.

Error Message via View History:

     Code: 0xC020200E  Source: DFT - Imports Data Flat File Source Description: Cannot open the datafile "\\server\Dev\ImportData\SourceFlatFile.tab

     Code: 0xC004701A  Source: DFT - Imports Data SSIS.Pipline Description: component "Flat File Source" failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE(1)

 

solutions / advice / comments?


Posted: 8/5/2010

Jedi Master 5646  points  Jedi Master
  • Joined on: 1/21/2010
  • Posts: 251

To be honest with you... all of these details confused me even more than I was before. I really have no idea as to what it is that you are trying to accomplish by jumping through so many hoops. Installing ssis on end users machines just to get your c# application functioning seems a bit crazy to me. Maybe someone subscribing to the SSIS rss feed can help you out. I would love to help you out, but maybe try to make your question a bit more brief. That's my suggestion!


Page 1 of 1 (12 items)