Dear all, I want use different variables in my SSIS package dependant on the schedule. So is there a way to use the actual schedule id from SQLAgent in a SSIS package? Thanks a lot, Nils

I want use different variables in my SSIS package dependant on the schedule. So is there a way to use the actual schedule id from SQLAgent in a SSIS package? Not clear as to what yuo want... Differenet variable in SSIS package? could you please elaborate as to what yuo want. Tweet me..

Hi. I want use the schedule_id of the Job executing the package in an SQL Statement to load a package configuration dependant of the schedule.

Hi. I want use the schedule_id of the Job executing the package in an SQL Statement to load a package configuration dependant of the schedule. Do you mean to say you want this value before loding the package? What you could do is in the pacakge have a execute SQL task where you extract the Schedule_id of the job executing the package and then make necessary modification to other variables. Tweet me..

Hi. Thats the thing I want do. Extracting the schedule_id and then do some other things in the package. What SQL I could use for that. Is there a stored procedure?

Hello, Create a variable called schedule_id , int type Drag Execute SQL task , Write this Query Select schedule_id from TableName.....( it should provide you only one schedule_id value). You will see Result Set, Select Single Row Then you will see a result set tab on your left, Result Name 0 and Select the variable name in Variable Name drop down. links http://msdn.microsoft.com/en-us/library/ms141689.aspx Here is link , that explains more about using Execute sql task with parameters http://www.sqlis.com/post/The-Execute-SQL-Task.aspx Thanks

Drag Execute SQL task , Write this Query Select schedule_id from TableName.....( it should provide you only one schedule_id value). Hi. This runs into an error. I need the schedule_id of the agent job which started the package. I know already how to read a table constrained by a schedule_id. The problem is how to get the actual schedule_id.

What is the error message that you are getting? Tweet me..

That there is no table named "tablename"?! To clearify this...I do NOT need an statement to query a table...I need a solution to get the schedule_id if the job which in running the package.

Hello, to get the schedule id USE msdb ; GO EXEC dbo.sp_help_jobschedule @job_name = N'BackupDatabase' ; GO --@job_name=N'BackupDatabase ( change BackupDatabase with the name of your job) link: http://msdn.microsoft.com/en-us/library/ms176046.aspx

Hi. Yep, I know this system procedure. But the result set does include all schedules. Not only the schedule a job is started from. THX Nils

What about using the tables from msdb Select * from dbo.sysjobschedules or Select * from sysschedules It will give you all the schedule ids http://msdn.microsoft.com/en-us/library/ms188924.aspx thanks

As I wrote before, I need the schedule_id a job is triggered from to load a schedule dependant package configuration. Or is there any other way to load a configuration dependant on a schedule? THX, Nils

Hmm, quite a few numpties misunderstood your very clear problem. Same as my problem (misery loves company), and I wish I had a nice and tidy answer for you. It seems such a simple question. Until I find that answer I've had to constraint my jobs to one job -> one schedule, give these jobs a category name and query durign runtime if any of these jobs are running (for me only one such job shoudl be runnign at a given time). Give this next_run_schedule_id is also the job id that's running. Poor man's solution, I know, but my boss is thinking I'm taking too long on this one already. Here's my code for the Execute SQL Task. I put the result of this into a package variable (will break if run interactively) truncate table dbo.jobData -- get rid of old data SELECT * INTO #JobInfo FROM OPENQUERY([WS-667\SFDEV1], 'set fmtonly off exec msdb.dbo.sp_help_job') INSERT into dbo.jobData select * FROM #JobInfo where category='Sharefunds' select next_run_schedule_id from dbo.jobdata where current_execution_status=1 where 'Sharefunds' is my custom job category and current_execution_status=1 means runnign jobs. To create an empty dbo.jobData in the first place I used; SELECT * INTO #JobInfo FROM OPENQUERY([WS-667\SFDEV1], 'set fmtonly off exec msdb.dbo.sp_help_job') select * into dbo.jobData FROM #JobInfo where 2=1 where [WS-667\SFDEV1] is my SS instance. You have to turn on data access to use OPENQUERY. It took me days just to get this far and as I said, I've had to compromise my design to get anywhere close. If I find a better solution I'll come back and update this, all I ask is you do the same. Cheers Derek

You could always just use a configuraton and not use the job execution ID, but use an arbitrary identifier which may give you more detail in the filename anyways.

You could always just use a configuraton and not use the job execution ID, but use an arbitrary identifier which may give you more detail in the filename anyways.