November 13, 2018 at 3:38 am
Hi
The problem:
I am calling a stored proc from VBA which executes a stored proc. The stored proc triggers an SSIS package. Messy but I can't call the SSIS package directly from VBA using SQL server authentication.
It works fine, however, I need to pass a username to the SSIS package (I have set up a project level variable).
The question:
can anyone help me on how to pass an SSIS project variable into a Job being called from a stored proc.
I found this code which works when I call the SSIS package from management studio but can't figure out how to pass it to a job:
alter procedure dbo.MyProc
@output_execution_id varchar(255) output
,@un nvarchar(75)
as
begin
declare @execution_id bigint
exec ssisdb.catalog.create_execution
@folder_name = 'myFolder'
,@project_name = 'myProject'
,@package_name = 'myPackage.dtsx'
,@execution_id = @execution_id output
-- System parameters
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id
, @object_type = 20 -- System parameter = 50; project parameter = 20
, @parameter_name = N'pUserName'
, @parameter_value = @un
exec ssisdb.catalog.start_execution @execution_id
set @output_execution_id = @execution_id
end
-- call it here
declare @output_execution_id nvarchar(255)
exec dbo.MyProc @output_execution_id output, "myUserName"
print @output_execution_id
November 13, 2018 at 4:22 am
Try something like this what you're looking for most likely is the /Par Switch and have a look at this, too
November 13, 2018 at 7:29 am
Thanks for these, unfortunately, they still don't give advice on how to pass a variable into an agent job? unless i'm missing something.
November 13, 2018 at 12:13 pm
I fail to see where the SQL Job belongs on your VBA process.
From VBA you can call the proc directly passing whatever parameters you wish.
from what you have posted you have VBA->SP->SP(MyProc)->StartSSISexec
Where does SQL Job sit (which I assume to be a SQL Server Agent job)
November 13, 2018 at 3:06 pm
I think you need to pass the parameter some other way, like creating a parameter table in a database. The calling VBA could add a row to the table with the username, the called stored procedure could read that row and delete it afterwards so the parameter value is not reused. Or the VBA code could delete all parameter rows after the job execution is finished, in case there was a job failure that skipped the delete.
You could also do something crazy like updating msdb.dbo.sysjobsteps.command to include a /Par or /set clause with the desired username, call the job, then reset the job step command afterward.
November 14, 2018 at 1:50 am
frederico_fonseca I can't call the stored proc to start an SSIS package directly from VBA as I am using SQL authentication and you can't exec an SSIS package in the SSIS DB using SQL authentication, unless you know otherwise.
Ideally, I would have liked to have specified a windows account in my conn string in VBA. I could have then executed the package .
Unfortunately, due to time/my knowledge, I have gone down a fully SQL proc route and removed the SSIS functionality. Scott Coleman I may come back to this and try those options. Thanks.
Would still love to hear if anyone resolves this.
November 14, 2018 at 12:46 pm
Not sure why you can't execute a package in the catalog with a SQL account. You have to add the SQL account to the SSISDB database - then grant permissions on the catalog folder the same as you do for a windows account.
If the problem is a permissions issue from the stored procedure - then you could modify the procedure to either execute as dbo or another login/user or by creating a certificate that has the necessary permissions.
With that said - what does this package do that requires it to be started/executed from a VBA program? I would bet there is a better option that would allow for a scheduled job to run every day/hour/minute. For example - if you create a queue table and enter the values in this table - then have the SSIS package read that table and process based on that data - then update the table marking completion/failure - then each iteration could process any outstanding requests in the queue (in a for each loop). Thus - avoiding any requirements to interact with SSIS or SQL Server Agent from your VBA code.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 15, 2018 at 7:17 am
If you set up a required project parameter, deploy the project and tie a job to it, there will be an entry in the job properties. I don't know that details as I discovered this by accident just experimenting with SSIS parameters. This isn't a good method for a user, but if I were doing an internal job I might consider that.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply