May 23, 2010 at 10:47 pm
Hi, this may already be answered somewhere, but I thought I would post the question, because I'm not really sure what to search for to find my answer!
I have a job that needs to execute two steps:
1) Run an SSIS Package to export some data to a dynamically named .xls file
2) Run a stored procedure to grab on to the file listed in step 1 and send it to a list of e-mail recipients.
Please note the SSIS Package resides on and is executed from a job on a 64-bit 2005 server, and as such I have to call it from a command line using the 32-bit exe or it fails, due to that famous 64-bit job bug with SSIS.
Prior to running the steps above, the user has to have checked a box on a form in our app to indicate it is OK to run the process. I realize this is a bit roundabout, but due to some issues with NT Authority coming from the client app, the users do not and cannot have permissions to run the process directly from the app... and so I have them ticking a box which is a single bit field sitting in a SQL Table, which will give the permission for the steps above to execute.
The job runs on the hour every afternoon and must check to see if this value is set to true before running, and set the value to false after having been run.
I need to add a step before the two steps described above which first checks this table to see if the value is true, and prevents the subsequent two steps from running if it is not.
Can anyone tell me the best way to do this? I don't want to quit the job reporting failure, but can I force the value in the first step to be "Go to next job step" if the value is true, and to be "Quit the job reporting success" if the value is false? How do I do this?
May 24, 2010 at 6:05 am
Incorporate SSIS package's script and copr script in a stored proc SP
then open a job , go to step and new/edit it
and add below code
if ( @check = 1 )
exec SP
set @check = 0 here @check is your checking variable
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 24, 2010 at 5:55 pm
Thanks Bhuvenesh...
A simple solution. Call the SSIS package from within the stored procedure.
It hadn't even occurred to me.
Good idea.
May 24, 2010 at 6:29 pm
One more problem.
Trying to call the SSIS Package from xp_CmdShell, as follows. We do use xp_CmdShell for other items in our SPs, so it is definitely switched on and accessible:
EXEC master.dbo.xp_cmdshell '"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /DTS "\MSDB\PayrollWeeklyFileExport" /SERVER NEMO /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'
Returns the following errors. Does it want short directory names or something?
'C:\Program' is not recognized as an internal or external command, operable program or batch file.
The same code minus the EXEC master.dbo.xp_cmdshell is currently able to run the statement from a CmdExec in a Job.
May 25, 2010 at 12:31 am
sharon.bender (5/24/2010)
EXEC master.dbo.xp_cmdshell '"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /DTS "\MSDB\PayrollWeeklyFileExport" /SERVER NEMO /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'
Check whether you have required access or not ? additionally can u exec following command shell
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\
does "Program Files (x86)" folder exist there ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 25, 2010 at 12:36 am
Yes, thanks, for that.
The path noted does exist. I have verified that, but already knew that it did, because that path is being used to call it from cmdExec in a job currently.
The problem seems to be that the SQL Server resides on a shared directory on the server, which cannot see the C:\Program Files directory on the same server, this has been limited.
If I set the job up to run as Transact SQL, and call the xp_CmdShell from the Transact SQL Window in the job, it is then able to get to the dtExec, but is not then able to write to the directory where the resulting file exported through SSIS resides!
So I've got a bit of a dilemma on my hands!
Thanks for your help. I no know what's wrong, I just have to sort out a way to fix it!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply