setting an ssis param dynamicaly when the sql agent job runs
-
hi i was hoping for a more elegant way of setting a pkg level param from the sql agent job that runs that pkg.
is this as good as it gets?
-
That's a good way. You could always build a wrapper proc around it to cater to your needs and defaults, making it as straightforward as possible.
-
This was removed by the editor as SPAM
-
You have to be aware that the default is to start the package asynchoneously. If you expect the jobstep to wait for the package to complete before moving on, the code above isn't enough. You wil have to state that you want the package to be run in synchroneous mode by setting the system parameter SYNCHRONIZED to on:
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type = 50, -- 50 is System Parameter
@parameter_name = N'SYNCHRONIZED',
@parameter_value = 1; -
im circling back to this right now. bear with me.
-
wow this is confusing. for starters i dont know what an object type of 50 (with parameter name SYNCHRONIZEFD, and maybe setting my pkg level param in the same EXEC) would do for us when 30 seemed a lot more promising. Would i have a separate execution of set_execution_parameter_value for my pkg param?
Anyway, something from long ago is haunting me... something about starting pkgs in this way (as opposed to straight from sql agent) where certain infrastructure needs to be in place in order for those 3 procs to even function.
But assuming that isnt a problem, and the pkg would start async via start_execution, can i at least rely on the ssis dashboard showing the results of the pkg execution? It would be a little uncomfortable not being able to assume the end time of the sql agent step coincides with the end time of the pkg but ive had worse experiences. normally when a job finishes, one feels comfortable looking at the dashboard. now we might find ourselves hitting refresh till it does. And we have some HOS (health of the system) stuff reporting on sql agent failures and now we'd probably have to plumb a different interface to know when these pkg executions fail. im starting to think that figuring out how to use the sync approach is worth the trouble.
At the moment im assuming the AI example i first posted is assumed to be pkg params, not project params. In this case there is only one pkg. We use project params often but havent seen an elegant way to make those dynamic either. what im gathering is that the approach is the same, its just the 20 or 30 that determines if you are setting a project vs pkg param.
i think what i really need is a way for the pkg to calc this param itself 99% of the time. I think maybe having a proj level param that is true or false indicating whether im also passing a proj level substitute for the pkg level param is the way to go. that way i can run the job manually if there is a problem and override the pkg level param which happens to be a string in yyyy-mm-dd format. when the job finishes, i can reset the t or f to f.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply