Using CmdExec To Execute Dtexec In Sqlagent
I'm a bit confused. On the command line of the job step property I entered dtexec /SQL... and got an error saying file not found, I assumed dtexec itself couldnt be found. So I tried /SQL .... by itself and got something that looked more like a security error. If I make the step property type "ssis" job appears to run fine, I receive my pkg's on success (rather than on failure) email but I know everything isnt fine because even if no data is ETL'd, first executable is supposed to (and always has in client) insert a row into an audit table and it doesnt. If I set job step "type" to t-sql and simply db email myself with t-sql command, everything is fine.
The first question is "Wouldnt dtexec need to be specified, how else could sqlagent know what I'm trying to run?" If answer is yes, what's wrong with my syntax or environment?
What are the best ways to get these to return a status to test if it has got an error or not.
I've working a while, not at full time, but seeking the solution...Here what I want to do and what I've done till now:
I want to build a sql job, so I can run a package (witch loads 2 excel sheets into 2 tables) passing "dynamic" parameters, like convert(varchar,getdate(),112) in the format YYYYMMDD. From what I've found, I can do this with an Stored Procedure, which first set this variable, building a statement so it can be run by xp_cmdshell. For example:
EXEC master.dbo.xp_cmdshell 'DTEXEC /SQ PACKAGE /SET "Package.Variables[DAY].Value";20070101'
And this statement runs with no problem in the SP until it reaches the step of loading the Excel Sheets into tables. Here it gives an error. It's about the JET driver. From what I've read in the forum and from the output error the problem seems to be that this statement executes the dtexec.exe 64bits, even the suggestion to change the property Run64BitRuntime set to False, it stills running from the dtexec.exe 64bits.
So, I changed the statement to point to dtexec.exe 32bits:
EXEC master.dbo.xp_cmdshell 'C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe /SQ PACKAGE /SET "Package.Variables[DAY].Value";20070101'
But executing this statement it does not even run. The errors are:
'C:Program' is not recognized as an internal or external command
operable program or batch file
This was very wird to me, because this was very mentioned by the moderators.
So, I copy this same statement and created a job with a step type of "Operating System (CmdExec)" and it runs great...With no problem with the extraction from excel source.
Now my questions are:
1. Why the step job CmdExec recognizes the path of dtexec.exe 32bits ('C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe ), but trying to run with xp_cmdshell it gives the error mencioned above. And if there is another way to set dtexec.exe 32bits besides this way?
2. If I cannot run it throught xp_cmdshell, how could I pass a parameter like convert(varchar,getdate(),112) in the format YYYYMMDD instead of the static parameter 20070101.
Thanks in advance.
Marco Francisco (Portugal)
I have created a logging test package that I am attempting to execute in SQLAgent.
It uses an environment variable in Package Configurations to set a variable which is the expression value for the ConnectionManager's 'ole_src_Admin' ConnectionString.
The next three Package Configurations use that connection to retrieve SQL Server configuration types/
Using the same account I can execute the package succesfully from cmd line
The execution string is
dtexec /SQL "Parent Package" /SERVER "BLAHSQL-DEV" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
Attempting to execute this via SQLAgent (type CmdExec) I receive the following error:
Date 9/26/2007 9:47:45 AM
Log Job History (Logging Test)
Step ID 3
Job Name Logging Test
Step Name Execute 2005 Package
Sql Severity 0
Sql Message ID 0
Operator Net sent
Retries Attempted 0
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:47:45 AM
Info: 2007-09-26 09:47:46.28
Source: Parent Package
Description: The package is attempting to configure from the environment variable "SQL_Parameter_Connect_String".
Info: 2007-09-26 09:47:46.28
Source: Parent Package
Description: The package is attempting to configure from SQL Server using the configuration string ""ole_src_Admin";"[dbo].[Parameter]";"sv_ssis_package_store_connectionstring";".
Error: 2007-09-26 09:47:46.30
Source: Parent Package Connection manager "ole_src_Admin"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
Info: 2007-09-26 09:47:46.30
Source: Parent Package
Description: The package is attempting to configure from SQL
I have also succeffuly executed this package in the same manner locally using both the cmd line and SQLAgent.
Any clues would be appreciated.
I'm using SQL 7.0/SP1 on NT/SP4. I created and saved a DTS Package that I want to execute every 15 minutes to check for the existence of some comma delimited text files, and import them with the DTS Package if they exist.
I'm using a SQLAgent Active Script (vbscript) job that uses the FileSystemObject to check for the files. The Active Script ran fine during construction from a remote NTWS machine. Once I scheduled the job on the server the job fails with:
"Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Permission denied Error on Line 9. The step failed."
The Active Script (main function where containing line 9) is below:
Print "Sub Main::Entry Point"
Dim oFileSystem, oFolder, oFile, bDTSSuccess
Set oFileSystem = CreateObject("Scripting.FileSystemObject")
(line 9 is next)
Set oFolder = oFileSystem.GetFolder("serverTransferFromHMP") <--- line 9
For Each oFile In oFolder.Files
Print "Sub Main::Call ExecuteDTS"
bDTSSuccess = ExecuteDTS(oFile.Name)
If Not bDTSSuccess Then Exit For
Rem Move file to Archive directory
oFile.Move "serverTransferFromHMPArchive" & oFile.Name
Set oFile = Nothing
Set oFolder = Nothing
Set oFileSystem = Nothing
I've researched the other postings on this site and some newsgroups regarding SQLAgent and have checked SQLAdmin (the SQLAgent account) and SQLAgentCmdExec rights, advanced rights, share rights, directory rigths, file rights, roles, sysadmin rights, local administrator group, domain user account, etc. I've logged in at the server as SQLAdmin and can open and move the import files. When I log in at the server as SQLAdmin the same failure occurs. SQLAgent properties are set to use NT authentication.
When I shutdown the SQLAgent service and start it up using the command line (logged in as Administrator) sqlagent -c -v, two things happen. First, I get the following message twice:
" Password verification of the 'SQLAgentCmdExec' proxy account failed (reason: a required privledge is not held by the client)"
Then, the SQLAgent actually runs the job, performs the FileSystemObject operations, but the DTS Package doesn't import the data. The job completes successfully. Note, I've tested the import files and manually they import fine and do not raise any constraint issues.
The function ExecuteDTS is as follows:Private Function ExecuteDTS(sFileName)
Dim oDTSPackage, sPackName, oConn, i
On Error Resume Next
ExecuteDTS = False
If Len(sFileName) < 3 Then Exit Function
sPackName = ""
If UCase(Left(sFileName, 3)) = "PCL" Then sPackName = "DTS_Posted_Claim_From_HMP"
If UCase(Left(sFileName, 3)) = "PCN" Then sPackName = "DTS_Posted_Claim_Line_From_HMP"
If sPackName = "" Then Exit Function
Set oDTSPackage = CreateObject("DTS.Package")
If Err.Number Then
Err.Raise Err.Number, Err.Source, Err.Description
oDTSPackage.LoadFromSQLServer ".", , ,256 , , , , sPackName
Rem Set Source Connection
For Each oConn In oDTSPackage.Connections
If oConn.Name = "Comma Delimited Text File" Then
oConn.DataSource = "serverTransferFromHMP" & sFileName
Rem Set other package properties
.LogFileName = "serverTransferFromHMPLog" & Left(sFileName, 3) & ".log"
.WriteCompletionStatusToNTEventLog = True
Rem Execute package
ExecuteDTS = True
For i = 1 To oDTSPackage.Steps.Count
If oDTSPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
ExecuteDTS = False
Set oConn = Nothing
Set oDTSPackage = Nothing
When I run the Active Script remotely it works fine (of course), and I'm use a login account that belongs to the Administrators group (as does SQLAdmin, and SQLAgentCmdExec). I have also checked that the DTS RLL files and the scripting DLL are on the server and they are. Any help would be appreciated.
I have a legacy extraction ("E") .NET 1.1 application that is still going to be the driving force for our ETL process. We are going to be utilizing SSIS for "T" and "L". Now, the "E" phase is running on an application server where we have .NET 1.1 framework installed and working. The SSIS packages are running on a separate SQL Server. The problem here is - how do we call the SSIS package and be able to pass in the right parameters from this .NET app that runs on a separate box? We would like to use DTEXEC to call the remote SSIS packages through Integrated Security. The SSIS packages are stored as File System packages.
On my server, I did not install the SQL Server 2005, I have SQL server 2000, thus I did not install Integration Services.
I want to execute one package by line of command DTExec.
I need what to execute this command without installed SSIS?
I am executing a package via vb.net with package.execute using the IDTSEvents interface inheriting DefaultEvents. I am trying to emulate DTExec functionality. The package runs fine and I am catching events, but I don't get nearly as many info and progress events as are put out by DTExec. From my understanding DTExec also used the managedDTS application object, is that not the case? If it uses this does anyone have an ideas on how it is grabbing more events? For example I don't get these events that DTExec shows ..
Info: 2006-09-26 14:22:27.97
Description: The package is attempting to configure from the XML file "D:SSISConfigurationsDatabase.dtsConfig".
Info: 2006-09-26 14:22:28.05
Description: The package is attempting to configure from SQL Server using the configuration string ""Configurations Database";"[dbo].[SSISConfigurations]";"DWB02130";".
I also don't get as many progress updates during validation, I basically get 0 and 100% while DTExec shows several 1%, 3%, 10%, etc.
Any help is greatly appriciated.
I have a manager SSIS package that calls a generic loader SSIS package, via the 'Execute Process Task'. The manger package (amongst other things) creates a config file and and then executes DTExec passing the config specification in the Arguments property, i.e. '
'/FILE "D:AudiencePackagesUKDW_Core_Loader_Persist.dtsx" /CONFIGFILE "D:AudienceDataSCPmystery.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF"
It works as expected, but, I always get the warning message 'File/Process "DTExec" is not in path.', I know that it is because the exe runs and loads the correct package which then performs the correct actions.
Have I dropped the ball here? I cannot see any reason for the warning as DelayValidation==True, I could set RequireFullFileName==True, but I do not know where SQL is installed on the various nodes.
Any input would be warmly received
I have this problem executing an SSIS pkg using dtexec.
dtexec /f d:smappsssis_pkgsdts_SOAR.dtsx /REP E
Started: 10:28:58 PMEnd Error
Error: 2008-03-03 22:28:59.00
Source: dts_SOAR Connection manager "SourceConnectionExcel"
Description: An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
Error: 2008-03-03 22:28:59.00
Source: Data Flow Task Source - CHANNEL_DATA 
Description: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.
However, it runs fine when I double click the package and execute it using the Execute package utility. That really puzzles me.
The package transfers data from worksheets in excel to SQL Server 2005 DB.
Both source and destination are on the same system.
I have a serious problem with my SSIS Package while executing using 32-bit DTExec and 64-bit DTExec.
Here are the details:
Windows Server 2003 64-bit (Build 3790: Service Pack 2)
SSIS 32-bit & 64-bit installed
SQL Server 2005 (Microsoft SQL Server 2005 - 9.00.1399.06 (X64) - RTM)
SSIS Package details (compiled in 64 bit)
Script tasks only
Microsoft Visual Basic .NET (using TRY...CATCH block)
PreCompileScriptIntoBinaryCode = TRUE
Run64BitRunTime = TRUE
Batch file that uses DTExec to execute the Package.
I am trying to exeucte the above SSIS package using both 32-bit and 64-bit DTExec to make it failure by providing invalid connection string. Here are the details,
Wrong connection String using 32-bit Execution
While establishing the connection the error message has been nicely captured in to my Exception block and writes into the log file.
Wrong connection String using 64-bit Execution
While establishing the connection the error has not been catpured anywhere (although I have TRY CATCH block) and it haults there itself with the message "Process is terminated due to StackOverflowException". Later I found that the error is due to the connection string along with the unhandled exception.
Please suggest any one of the following my findings, also if you have any other advice would be very much appreciated.
1. Shall I go ahead and fix the issue by handling those unhandled errors? (e.g Appdomain, application). I tried several but still not working using 64-bit DTExec.
2. Shall I go ahead and use 32-bit DTExec to execute the package? If so, is there any other major issue...like performance or anyother bug?
P.S: We cannot apply any service pack for SQL Server 2005 at the moment. Sorry abt it. If you have any specific hotfix for DTExec (without affecting SQL Server) then we can decide.
Sorry for the lengthy one and Thanks very much for you help in advance .
I have a SSIS package with a Configuration value reading a config file from environment variable. In development environment it is able to find the environment variable. But Once i deploy on server machine. Manually create the Environment variable. Deploy the package and execute as a job in sql agent job account. The package fails stating cound not find the environment variable. When i went and saw, environment variable is still there. What is wrong with it.
I tried to debug using running dtexec
When I run the package using "dtexec /sql package /server server " sqldumper comes and i am not able to debug it.
Finally I have to run the package as sql job only.
I have created a VB 6.0 executable with the purpose of reviewing email sent to a certain MAPI mailbox, and if the mail has a specific attachment, that attachment (a text file) is saved to the hard drive. The program then runs (using a shell command) a DTS package on my SQL Server (7.0) to import the text file into an existing table, and then deletes the hard disk copy of the file. The imported information is then processed using ADO calls to a stored procedure on the same SQL Server.
My goal is to schedule this EXE to run on a regular basis using SQL Server Agent. However...
When I run the EXE from the system with the SQL Server Agent that I would like to use (not the same server as the SQL Server the code is being run against, but it has an ODBC DSN set up for the SQL Server with the data and the SQL Server Agent user has full permissions on that SQL Server) it runs fine. But when I add the exe as a cmdexec job in SQL Server and start it, it just hangs there. It says it is executing, and it allows me to stop execution, but it doesn't actually run the program itself, as far as I can tell.
Anyone have any thoughts? Any input would be very much appreciated!
I scheduled task to run daily with dbcc.
I run this task manually to test, task was running saccesfully and already 30 minuts is active and running. Our Lab test server is not busy and I don't understand what is problem, why it's running so long time.
Are you experienced with problem like that?
I have a DTS local package that consists of a single Process Task. I call a locally stored VB executable which opens a local Excel file and deletes certain rows. When I run the package manually, it runs fine. When I schedule the package for execution the package says it executed successfully, but it doesn't actually invoke the executable. Any suggestions on how to correct this situation would be greatly appreciated.
I have tried to make a DTS task running a batc-file on the same server as the SQL program is running using the CmdExec command.
My Command is as follows:
The problem is that everytime the step runs it report the following error:
"The system cannot find the file specified."
In the bat file I am also trying the de-map/map a network drive with another loginname/password than the one used on the SQL-server -> is this i problem?
Hope to hear from you soon, this is a bit urgent!!
Please provide tips to this adresse: firstname.lastname@example.org
I have to verify a .CSV file exists before I run a BULK INSERT. I am using XP_FileExist in SQL 2000 to accomplish this. After the Bulk Insert is completed and validated, I need to Rename the file and Move the file to archive the folder. For testing I figure If I can rename the file I can move it. I suspect I have permission issues and need to provide the SQL Server Agent permissions to this folder and file. I have my PC setup as a SQL 2000 Server and am attempting to get this step only working on my local machine. I created a nightly Job that remanes a file that I created in a Job and that is all it does. I am running the Job as SA but am still having issues.
The step being executed by the Job is "Ren C:MyTestFile.csv C:MyTestFile1.csv" (with the quotes). If I run this statment (without the Quotes from a command prompt, the file is renamed.
I have set the Type as "Operating System Command (CmdEXEC)". The Job history shows "The process could not be created for step 2 of job 0x71D51027F920A140A2913234DB7FF509 (reason: The system cannot find the file specified). The step failed."
As I said, I suspect that it is a permissions issue as the command works from the command prompt. What is the windows account that the SQL Server Agent uses to commit these commands? I added "Everyone" with Full access to the folder and I still get the same failure.
I would appreciate any assistance anyone could provide. Thanks in advance!
Thanks for the invitation to post a question, so I will post one.
I need to create a job step that uses cmdExec.
This is the command line I entering:
When I run this job the job fails. When I look at the job history, the only information I get is the date and time, user that ran the job and the fact that it failed. I haven't been able to get any CmdExec job to run at all. Can anyone tell me what I'm doing wrong?
1. This exact same command is used by my network administrator using windows scheduler on the server. The only reason he wants me to create an sql server job is because it's mostly sql functions.
2. I know the Sql Server agent is running, because I have other jobs that are run.
3. I have verified that I have permission to run the file because I can go to the actual directory and run the exe.
4. Do I need to enclose my command in quotes i.e. “D:odbcTimeClockUpdatesinReleaseTimeKeepingN onLogouts.exe”
5. the path of the file I need to run is the path on the server and not the path on my local machine.
If you need any other information, please let me know
Thanks for you help
I would like to trap a return value from a cmdexec that is scheduled. The cmdexec returns 0 if it is a success and something other than 0 if it doesn't.
Can I raise an error from a command file. The command file calls a console application ( i.e. no interface ).
Any help is appreciated.
WE have a number of SQL 6.5 servers all of which run many scheduled tasks. However on one all tasks which use cmdexec to run batch files fail after 2-3 seconds. The history returns : 'No message' and the error logs just indicate that the tasks have faILED.
The identical tasks run on servers with what appears to be identical configurations and setup.
Tasks scheduled using Tsql run OK !
I would be grateful for any advice or help
Many thanks in advance
I have a Job that runs a cmdexec job step which executes a batch file in sql server 7.0 that runs fine
In sql 2000 when i try to run that job it gives the following error and fails
the process could not be created for step 1 of job 0x677EF599B13FA743AA2D501D4C211AC4 (reason: The system cannot find the file specified). The step failed.
In fact i am not able to execute any cmdexec job in sql 2000
The owner of the above job is sa
Does it have to do with SqlAgentCmdExec account which is set to corporate/administrator and has required permission.
Help will be greatly appreciated.
Ive created a job in sqlserver 2000 (Management, SQL Server Agents, Jobs).
One of my steps execs a script (.sql) using OSQL (thru CmdExec) and outputs to another file (using the 'o-' parameter in the same command line.
Ok heres the problem:
On execution - sometimes that process get deadlocked. It then raises the errormsg & writes to the output file. HOWEVER, the SQL Server Agent reports back that the STEP completed successfully, when it is really not successful at all.
Is this the way it actually behaves when using CmdExec?
Do you think if I change the step from 'CmdExec' to use 'Transact SQL'
and output to a file (inside that step, and not through OSQL), And if it gets deadlocked again - Would the SQL SErver Agent report back the ERROR, instead of returning 'Successful' ?
OR IS there any other way around this problem.
thnx for ur help. Ill be trying to find another way as well.
I have a bcp job in .cmd file. But it is the d drive like 'd:foldernamefile.cmd' I use Cmdexec type and have used the following syntax:
But non of these syntax work. The job either failed or job keep executing but no output file being seen. Something wrong with the syntax? But I can type: start d:foldernamefile.cmd in command promp it works. Can any body tell me what is wrong here?
By the way I have parameter for the .cmd file I put it after the path e.g.,
start d:foldernamefile.cmd SQLSERVERNAME. Could this parameter cause the problem. If so how I put the parameter?
Thanks for the help.
In creating 'steps' in JOBS, is it possible to execute many DOS CmdExec in
one step, instead of creating several steps with a single DOS-cmd in each.
Step1: bcp sourcedb..sourcetbl out source.dat -n -T -Ssourceserver
Step2: isql -T -Stargetserver -Q "truncate table targettbl"
Step3: bcp targetdb..targettbl in source.dat -n -T -Stargetserver
If I created a job executing those 3 functions in 3 separate steps then it works fine. But if I put all those 3 DOS command in one step, it won't work. Somehow,
SQL doesn't 'understand' it should execute after the end of each command OR
I missed something here (apparently so!).
I know if I put all those 3 DOS commands into a DOIT.BAT and execute it, it will work. But I want to use SQL Job to schedule it to run on a regular basis.
Anyone has run into this same problem? Thanks in advance.
I have a scheduled job that has been running fine for 2 years, but this week I keep getting the following error:
has caused an exception in the CmdExec subsystem and has been terminated.
the enterprise manager shows that the job failed, but the app still shows up in task manager.
I want to copy a backup from the production server to another server
periodically. The following is the DOS command and it works in DOS propmt
d: is the local drive and g: is the mapping server
"copy d:ackupackup_CustomerDB.dat g:mssqlackupackup_on_3Wednesdayackup_CustomerDB .dat"
I try to use the above command in the executive task with CmdExec as type,
if failed. Then try on
"cmd c/ copy d:ackupackup_CustomerDB.dat g:mssqlackupackup_on_3Wednesdayackup_CustomerDB .dat"
this time, it started to run with no error. But it run 40 miniutes and I cancelled it, since it should only run 10 minuts.
thanks for the help
I have a job that executes an Operating System batch script which in turn executes a number of osql tasks. i.e.
osql [various options] -i query1.sql
osql [various options] -i query2.sql
osql [various options] -i query3.sql
If for example query2 fails, the job still reports success. Is there a simple method to report failure. I have tried splitting the above into 3 steps eaching executing a batch file and specifying an action of quit with failure for each, but it still reports as successful!!
I have sql 2000 job that has been setup as a "Operating System Command (CmdExec) job. I am logged into the SQL Server as DomainNameSQLAdmin this domain account is part of the Administrator group on the SQL Server that the job in running on. This account has SysAdmin rights and is also starting the MSSQLSERVER Service and SQLSERVERAGENT Service on the same machine.
The job just copies files from one directory to another, here's the code.
xcopy *.* D:MSSQLBACKUPAP /s/y/d
When run as a job, with the owner of the job being DomainNameSQLAdmin the job fails with the following error message: Executed as user: DomainNameSQLAdmin. The process could not be created for step 1 of job 0x822E9AD29DCAAF4196369A46C7FE212A (reason: Access is denied). The step failed.
Here's the weird part if I open a command window on the sql server and run the batch it works fine.
I even tried executing the commands via xp_cmdshell but that didn't work either, I recieved the message: (1 row(s) affected) with the output being NULL and the file was never copied.
Anyone have an idea whats going on?
Can someone tell me how i can generate an exit code (Cmdexec). Ive tried running an .exe file in a job but when i do that the job hangs. When i check the history it says the exit code is somewhere in 27000. Now i'm wondering if it is possible for me to generate an exit code if the executable closes.
PS. When i run the file from a batch file it works great
I have created an Integration Services package on my development machine. The package contains a configuration file witch let's say is stored in c:projectsMyIntegrationServicesProjectmyConfigfile.dtsConfig (on my dev machine).
Then I have another "Production" machine where I import the SSIS package into an SQL database. I then create an sql-job with only one step, to run my SSIS package. This works fine if I configure the step to be an "SQL Integration services package" and configure it to use my configurationfile.
However I would like to configure this package as a CmdExec step. In the commandline, I specify /CONFIGFILE "d:....myConfigfile.dtsConfig" (the correct path on the prod machine). But it seems to be ignored, because when I execute the package I get an error telling me that the configuretion file c:projectsMyIntegrationServicesProjectmyConfigfile.dtsConfig cannot be found.
What I try to say is, it seems like it ignors the config-file I specify on the command-line and tries to reach the config-file on a location that's probably stored somewhere in the SSIS package from the time it was created on my development machine.
Is there a way around this?