Question on Batch File - to kick off SSIS package.

Question

Hi,
We have SSIS pkgs stored on shared drive and I created a batch file which will kickoff a parent package which inturn kicks off all the child packages.

Concern: I am uncomfortable using the Batch file, anyone trying to open to see whats inside the batch file can 
              accidentally double kick the Batch file and it will kickoff the process. The path inside the batch file
              referes to the  shared drive path where the SSIS pkg is stored, so no matter where the batch file is
              it will look for the pkg and kick it off.

1. Is there any other way around for this problem?
2. Is there any way to proram the batch file to some kind of a control?


Any kind of solution is highly apreciated.



Thanks,
Ravi

---

everything is bad..

Answer 1

do you have SQL server?
you can use a Job and call the job at any time
i can send you some codes

or use a SQL command

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post

Answer 2

hi ..thanks for replying...
no we don't store them on sql server...had they been on sql server it would have been easier to schedule using sql agent/jobs..but here we store on a share drive and use some third party tool to kick off the batch file as and schedule using the same tool....so essentially the batch file and its location(on the same shared drive but different folder) becomes very important, Here is the folder structure:

DEB1023 --> production server shared drive

\\DEB1023\project\Packages
\\DEB1023\project\ConfigFiles
\\DEB1023\project\BatchScripts
\\DEB1023\project\logs




thanks,
ravi

---

everything is bad..

Answer 3

fine, thats ok
i have pacakges as a file located in
..\PackageAndConfigFile\

and the pacakge reads from
..\ToBeProcessedFiles

story short
i have a filed based package that is reading from folders (Excel files) making backup folder on a nother server, saving log files on another server, reading configuration file on a nother folder and etc..
and i am calling my package with a sql job
will that help you ? if i send you a script will it help ????

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post

Answer 4

I maybe leaving this site soon, but i am going to post the script for you, the script is just calling an SSIS File and setting its configuration files (so that it can pick up its values for its variables) and in the second step its calling a SP

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post

Answer 5

-- $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
----------------------------------------------------
-- Running a Package with the help of CmdExec Proxy
----------------------------------------------------
-- $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

USE [msdb]
GO
--################################################################################################################
--################################## Declaring and setting variables #############################################
--################################################################################################################
DECLARE @Destination_database_name AS nVarChar(50)
DECLARE @JobCategories    AS nVarChar(30)
DECLARE @Job_Run_time    AS int
DECLARE @Job_Name     AS nVarChar(100)
DECLARE @Schedule_Name    AS nVarChar(100)
DECLARE @owner_login_name   AS nVarChar(100)
DECLARE @MainFolderPath    AS nVarChar(100)
DECLARE @JobCommand     AS nVarChar(1000)
DECLARE @output_file_name   AS nVarChar(1000)
DECLARE @proxy_name     AS nVarChar(50)
DECLARE @PkgPassWord    AS nVarChar(50)

SET @Destination_database_name = 'YOU DB'
SET @JobCategories    = 'ETL Class 2'
--SET @Job_Run_time    = 142000  ---  142000 = 2:20pm
SET @Job_Run_time    = 70000  ---  142000 = 2:20pm
SET @Job_Name     = 'Job_NTSD_Daily_Load'
SET @Schedule_Name    = 'Schedule Every day @ -> 07:00'
--SET @owner_login_name   = 'Yor login name'

----------------------------------------------------------------------------
-- This is the main folder path of the ETL not the path folder of where the package is
-- the package folder is a standard folder name
SET @MainFolderPath    = '\\.....\......\MAINETLFOLDER'           
----------------------------------------------------------------------------
SET @proxy_name     = 'SupportProxy'
----------------------------------------------------------------------------

--################################################################################################################
--################################## Deleting the Job ############################################################
--################################################################################################################
IF EXISTS (SELECT 1 FROM sysjobs WHERE Name = @Job_Name) 
 EXEC sp_delete_job @Job_Name = @Job_Name

--################################################################################################################
--################################## Making Job Categories #######################################################
--################################################################################################################
BEGIN TRANSACTION
 DECLARE @ReturnCode INT
 SELECT @ReturnCode = 0

 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=@JobCategories AND category_class=1)
 BEGIN
  EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=@JobCategories
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 END

 --################################################################################################################
 --################################## Adding the jon ##############################################################
 --################################################################################################################
 DECLARE @jobId BINARY(16)
 EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@Job_Name,
   @enabled=1,
   @notify_level_eventlog=0,
   @notify_level_email=0,
   @notify_level_netsend=0,
   @notify_level_page=0,
   @delete_level=0,
   @description=N'Running the Pakage "IntSer_ETL" and calling SP "usp_ETL_FillLocalCache_YOURSP" to run ETL',
   @category_name=@JobCategories,
   @owner_login_name=@owner_login_name, @job_id = @jobId OUTPUT
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 --################################################################################################################
 --################################## Adding Step 1 ###############################################################
 --################################################################################################################
 SET @JobCommand = 'DTEXEC '
 SET @JobCommand = @JobCommand + N' /FILE ' + @MainFolderPath + '\PackageAndConfigFile\PkgETL-SSIS-.dtsx '
 SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlDestinationDBConfig-318_QAQuiz.dtsConfig '
 SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlFolderConfig-318_QAQuiz.dtsConfig '
 SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlMailConfig-318_QAQuiz.dtsConfig '
 SET @JobCommand = @JobCommand + N' /CONFIGFILE ' + @MainFolderPath + '\PackageAndConfigFile\SSIS-XmlSourceDBConfig-318_QAQuiz.dtsConfig '

 --SET @JobCommand = @JobCommand + N' /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'

 SET @output_file_name = @MainFolderPath + '\LogFolder\Job\JobLogFileFor-ETL-Step1.log'

 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run ETL Package IntSer_ETL',
   @step_id=1,
   @cmdexec_success_code=0,
   @on_success_action=3,
   @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'CmdExec',   
   @command=@JobCommand,   
   @database_name=N'master',
   @output_file_name=@output_file_name,
   @flags=34
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 --################################################################################################################
 --################################## Adding Step 2 ###############################################################
 --################################################################################################################
 SET @output_file_name = @MainFolderPath + '\LogFolder\Job\JobLogFileFor-ETL-Step2.log'

 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Call SP [usp_ETL_FillLocalCache_318_QAQuiz]',
   @step_id=2,
   @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'TSQL',
   @command=N'EXEC YourDBNAME.dbo.[usp_ETL_FillLocalCache_SP]',            
   @database_name=@Destination_database_name,
   @output_file_name=@output_file_name,
   @flags=6
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 --################################################################################################################
 --################################## Changes the attributes of a job #############################################
 --################################################################################################################
 EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 --################################################################################################################
 --################################## Setting the job schedule ####################################################
 --################################################################################################################
 DECLARE @DateInNumericFormat AS Int
 SET @DateInNumericFormat = Cast(Convert ( nVarChar(8) , GetDate() , 112 ) AS int )

 EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@Schedule_Name ,-- @Job_Name,
   @enabled=1,
   @freq_type=4,
   @freq_interval=1,
   @freq_subday_type=1,
   @freq_subday_interval=0,
   @freq_relative_interval=0,
   @freq_recurrence_factor=0,
   @active_start_date=@DateInNumericFormat,
   @active_end_date=99991231,
   @active_start_time = @Job_Run_time,
   @active_end_time=235959
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 --################################################################################################################
 --################################## Targets the specified job at the specified server ###########################
 --################################################################################################################
 EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION
GOTO EndSave

--- Lable
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

--- Lable
EndSave:

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post

Answer 6

hi ..thanks for replying...
no we don't store them on sql server...had they been on sql server it would have been easier to schedule using sql agent/jobs..but here we store on a share drive and use some third party tool to kick off the batch file as and schedule using the same tool....so essentially the batch file and its location(on the same shared drive but different folder) becomes very important, Here is the folder structure:

Hi Ravi,
Could you let us know the third party tool?
Does it not have access to the SQL Server where you could store tha package and trigger it by the 3rd party tool?
Cant you 3rd party tool directly invoke the SSIS Package rather thatn using a batch file to executet the package?

---

Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.

Answer 7

Hi
what kind of third party tool u are using ???
why dont u parameterised the packages location .. etc information and ask user to enter the necessary information when user double clicks the batch file ???
Or say pass some information to kick off the batch file execution ...
If thats the only concern that someone might by mistake double click the batch file and kick off the package execution u can save urself by this human interaction about some parameters passed to the batcj file ....
ur third party tool can also pass the information for executing batch file ....
let us know if it helps ...

____________________________________________________________________
Hope it helps Kunal ..Please mark the post/s as Answered which answers your problem.

Answer 8

Hello folks,
Thanks everyone for all your answers, thanks Nik3 for your script.
Sorry for the delay in responding. we use an IBM tool - Tivoli Workload Scheduler
to kick off these batch file..my concern is as i already said:

Concern:      I am uncomfortable using the Batch fil as:
           1. Anyone trying to open to see whats inside the batch file can accidentally double kick the
              Batch file and it will kickoff the process. The path inside the batch file referes to the
              shared drive path(\\DEB1023\project\Packages) where the SSIS pkg is stored, so no matter
              where the batch file is it will look for the pkg on shared drive and trigger it.

           2. And SSIS packages look for config files which will have server username and password
              and obviosuly will nto fail anywhere.

Kunal:
Once we send in to production there is no manual intervention it will be scheduled to run once every 15days.

Sudeep:
I dont think the tool can kickoff the SSIS packages directly(or may be it does), it can just trigger the
batch files. Now that you asked i need to check on that as it can trigger shell scripts, i will check on
that and get back here with solution.

until then any suggestions are welcome.

thanks,
Ravi

---

everything is bad..

Answer 9

Hello Folks:
May be these are the availeble solutions:

1. Have previlages do the job, only those who have previlages will be able to trigger
   the ssis pkgs on the shared drive.(others can't although they can view and copy
   from production file share)
2. Have a full path with drive letters F:\production\project\Packages\..)

Lemme know if above makes sense or any other suggestions / better ways of doing it?

 

thanks,
Ravi.

---

everything is bad..

Answer 10

Hey Ravi,
Everything is not bad ;)

If you have the previlege to run the SQL Job ur work is done.
Of cource u will have to ask the admin to provide access to u or an account which has the rights to do so.

A point of mention while executing the package from the job is ensure that the Job has access to all the folders and files if any used in the package.

I would suggest you import the package to DB and execute the job to run the package.

let me know any concerns u have.

---

Hope this helps !! Please mark the post(s) as “Answered” that answers your query - Sudeep