Simple SSIS question - best practice: run the same code with different variables
I wrote an SSIS package that essentially takes data from multiple sources and writes it to an Excel file (it's a little more complicated than that, but I don't think specifics really matter at this point).
Now I need to run this DTSX package every week (Monday) and every month (1st) and save the excel file for the name given to the variable in the package by running some simple SQL stored procedures that are either "Weekly" or " Monthly "passed to determine the dates needed to get the correct data.
The original plan was to copy the DTSX package and have a SQL Job to run the first package every Monday and the second package on the 1st of every month.
Is there a way that I can use the same package to do both tasks (for example, can I pass "Monthly" or "Weekly" to the DTSX package from SQL Job in some way), and if so, how is this to do
Thanks Bob
source to share
Thanks again for the help Raj.
My last answer was to create a job of type SQL Server Integration Services and then set a variable ("TimeScale") in the SSIS package.
Once I set the SSIS Job Type, I could then "Set Values" as follows (note that this is exactly the same as below: "package" must be "package", not your package name!):
Property path: \ package.Variables [TimeScale] .Value
Value: Monthly
Complete sample code to import into an assignment if you need an 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:
source to share
Create a variable in the package named ExecutionMode. Use this variable as a parameter for the appropriate stored procedures. Set ExecutionMode to "Weekly" or "Monthly" and run your package. Make sure all procs are working correctly.
Use package configurations and put ExecutionMode in the configuration file for the package. Now ExecutionMode can be passed as a parameter.
Create two jobs for the SQL Server Integration Services SSIS package. In each of them, specify the package and configuration file. On the SET VALUES tab, select the ExecutionMode variable and set it to "Weekly" or "Monthly" depending on the schedule.
Here's how to run it on the command line (including setting variables):
http://www.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/2999/
source to share