uSING XP_CMDSHELL

Question

uSING XP_CMDSHELL

VastSQL

SSC-Dedicated

Points: 36097
More actions
July 22, 2010 at 6:45 am

#225246

Hi Experts,
We are using a .cmd file to create temp varibles and to run an SSIS package.
This is want inside the .cmd file
@title RKN-MainScript
@color 2e
prompt $h
setlocal
REM ######### STARTING RKN#########################################################################
SET ETL_ROOT_DIR=E:\ETLEXEC\RKN
SET CONFIG_FILE_PATH=%ETL_ROOT_DIR%\RKN_CONFIG.dtsConfig
dtexec /FILE "%ETL_Root_Dir%\PKG_RKN.dtsx" /MAXCONCURRENT " -1 "
Can we use xp_cmdshell to create and execute the package just like wat the .cmd file doing??
Many Thanks

Viewing 15 posts - 1 through 15 (of 22 total)

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

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 1

you could just use xp_cmdshell to execute the cmd file..

though personally, I don't like xp_cmdshell to be enabled at all - i'd advise looking at other options, but thats just my personal opinion

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 2

Better choice would be to create a job to run this (as a batch file) and schedule the Agent to run the job.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 3

Can u please let me know the difference in executing as .cmd file and .bat batch file???

Adiga One Orange Chip Points: 27284 More actions · Answer 4

As seen in Wikipedia

The only known difference between .cmd and .bat file processing is that in a .cmd file the ERRORLEVEL variable changes even on a successful command that is affected by Command Extensions (when Command Extensions are enabled), whereas in .bat files the ERRORLEVEL variable changes only upon errors

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 5

Thanks Adiga..

So that wont resolev my problem...

The issue is that the DBA their in Client side dont want to run a .cmd file on server.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 6

A SQL Server Agent job running an SSIS package could be configured to point to a config file without a batch file. If all that the batch file is doing is telling the SSIS package where to find the config file, why do you need this? Just expose the filepath as a connection and give the DBA instructions on how to configure the job. No reason at all to do this as a batch file as the Agent can do all of this for you. In this case I support the DBA.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 7

jeff.mason (7/26/2010)
A SQL Server Agent job running an SSIS package could be configured to point to a config file without a batch file. If all that the batch file is doing is telling the SSIS package where to find the config file, why do you need this? Just expose the filepath as a connection and give the DBA instructions on how to configure the job. No reason at all to do this as a batch file as the Agent can do all of this for you. In this case I support the DBA.

Think Jeff has not gone through wat the .cmd file doing??

It creates two system varibles and the session should exists till the job end for the variables to exists i think

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 8

I did read your code. You create two variables to indicate the path that the SSIS package is in as well as the path of the config file for the SSIS package, then you run DTSExec to run the package. This could all be done via a SQL Server Agent job. You set the step as a "SQL Server Integration Services Package" type and then you set the config file up in the Configuration tab. Then the Agent runs the package. I don't see why you need system variables to do this in a batch file when you can use out of the box SQL Agent techniques to accomplish the same thing. No use of batch files, xp_cmdshell, and the server is relatively secure.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 9

jeff.mason (7/26/2010)
I did read your code. You create two variables to indicate the path that the SSIS package is in as well as the path of the config file for the SSIS package, then you run DTSExec to run the package. This could all be done via a SQL Server Agent job. You set the step as a "SQL Server Integration Services Package" type and then you set the config file up in the Configuration tab. Then the Agent runs the package. I don't see why you need system variables to do this in a batch file when you can use out of the box SQL Agent techniques to accomplish the same thing. No use of batch files, xp_cmdshell, and the server is relatively secure.

Thanks Jeff..

We dont want to hard code any values like the location where the input files will be so that we can use the same for different projects.Also this helps while moving from different environments like from DEV to QA like that..

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 10

Then script out the job, and add variables to the script for the path and the config file, and you can change the variables one time in that script. Then run that script against any SQL Server that will run the job. It will accomplish the same thing as a batch file. You have to set the details somewhere, and a DBA will be much happier to set it in the context of a job step over a batch file.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 11

Thanks alot Jeff..

But could u please giove me an example on adding the variable???

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 12

This is an edited job creation script to show what I mean.

USE [msdb]

GO

DECLARE @FileName NVARCHAR(200)

DECLARE @FilePath NVARCHAR(200)

DECLARE @ConfigFile NVARCHAR(200)

SELECT @FilePath = 'C:\'

SELECT @FileName = @FilePath + 'test.dtsx'

SELECT @ConfigFile = @FilePath + 'config.txt'

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'test ssis',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'No description available.',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'test',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'SSIS',

@command=N'/FILE "'+ @FileName + '" /CONFIGFILE "' + @ConfigFile +

'" /CHECKPOINTING OFF /REPORTING E',

@database_name=N'master',

@flags=0

Modify this script for your environment. Use it anywhere you need SQL Server to run an SSIS package. Use the variables at the top of the script to specify where the paths of the package are.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 13

Thanks alot Jeff..

This will not work as I said before we are creating system varibales..The same system varible is called inside the package..

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 14

Why would you do that? Couldn't the data from the system variable be transferred into the config file? IMO depending upon the OS for that sort of thing within SSIS is a mistake.