Simple SSIS Question - Best Practice: Running same code with different variables

Question

Hi folks,

I have written an SSIS package that essentially takes data from multiple sources and writes it to an Excel file (it is a bit more complicated than this, but I do not think the specifics really matter at this point).

Now, I need to run this DTSX package every week (on a Monday), and every month (on the 1st) and save the excel file to a name specified by a variable within the package, having run several simple SQL stored procedures, which have either 'Weekly' or 'Monthly' passed in to work out the dates needed to get the right data.

The initial plan was to copy the DTSX package and have a SQL Job just run the first package every Monday and the 2nd package on the 1st of each month.

Is there a way I can use the same package to do both things (for example, can I pass 'Monthly' or 'Weekly' into the DTSX package from the SQL Job somehow) and if so, how do I do this?

Thanks, Bob

Answer 1

Create a variable in the package called ExecutionMode. Use this variable as a parameter to the appropriate stored procedures. Set ExecutionMode to "Weekly" or "Monthly" and run your package. Make sure that all procs run correctly.

Use Package Configurations and put ExecutionMode in the config file for the package. Now, the ExecutionMode can be passed as a parameter.

Create two jobs for the SSIS package of type "SQL Server Integration Services". In each one, specify the package and the configuration file. On the SET VALUES tab, choose the ExecutionMode variable and set it to "Weekly" or "Monthly" depending on the schedule.

Here is how to run it command line (including setting variables):

http://www.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/2999/

Answer 2

Thanks again for the help Raj.

My final answer was to create the job of type SQL Server Integration Services, then set a variable ('TimeScale') within the SSIS package.

Once I set the job type to SSIS I could then 'Set Values' as follows (note that it is exactly as below - 'package' should be 'package', NOT the name of your package!):

Property Path: \package.Variables[TimeScale].Value
Value: Monthly

Full sample code to import into a job if you need the example. :)

USE [msdb]
GO
/****** Object:  Job [Sample]    Script Date: 10/28/2009 16:04:22 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 10/28/2009 16:04:22 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Sample', 
     @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'NTWK\FrostbiteXIII', @job_id = @jobId OUTPUT
IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
/****** Object:  Step [Sample Step]    Script Date: 10/28/2009 16:04:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sample Step', 
     @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'/DTS "TestPackage.dtsx" /SERVER MyServer /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\package.Variables[TimeScale].Value";Monthly', 
     @database_name=N'master', 
     @flags=0
IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly Step', 
     @enabled=1, 
     @freq_type=8, 
     @freq_interval=3, 
     @freq_subday_type=1, 
     @freq_subday_interval=0, 
     @freq_relative_interval=0, 
     @freq_recurrence_factor=1, 
     @active_start_date=20091028, 
     @active_end_date=99991231, 
     @active_start_time=80000, 
     @active_end_time=235959
IF (@@ERROR  0 OR @ReturnCode  0) GOTO QuitWithRollback
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
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: