SSIS Run Macro through SMO

  • 23 September 2019
  • 1 reply
  • 559 views

Hi guys,

 

I am trying to create a workflow which contains an activity that is actually running a Macro VBA code from an Excel file, but I am getting an error which is not that descriptive.

 

The only way I figured to run a macro from a VBA file is through SQL's SSIS and a Script Task. Everything runs smootly when I am running the package from SSIS, the same good outcome when I am running the Stored Proc from SQL which runs this package, but when I am running the Stored Proc from  SmartObject, I am retrieving this error: "Script Task: Error: Exception has been thrown by the target of an invocation."

 

From a quick search, I've seen that is a general issue and it does not really point to a specific issue and this is the reason of this post.

 

Could you please tell me if this error/case is something you have seen until now?

 

Also, I am open to other solutions then SSIS for running a Macro, but right now this was the single one I can see.

 

Many thanks in advance!


1 reply

Looks like permission issue.. What you can do is create a job from SQL for your SSIS. 

 

Then create stored Proc to call that job. This way the job will call based on SQL Agent service. We have similar config but user manully trigger the job using K2 smartform view using SmartObject we call the Job method. 

 

Below is SQL Job executing code...

################################

ALTER PROCEDURE [dbo].[sp_SAMPLE_ExecuteSQLAgentJob]
-- Add the parameters for the stored procedure here
@JobID uniqueidentifier

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY

IF((select count(s.name) from msdb..sysjobs s WHERE s.job_id = @JobID) > 0)
BEGIN
EXEC msdb..sp_start_job @job_id = @JobID;
BEGIN
WAITFOR DELAY '00:00:02'
SELECT LTRIM(RTRIM(s.job_id)) AS 'JobID',
LTRIM(RTRIM(s.name)) AS 'JobName',
CASE WHEN jobStatus.current_executed_step_id IS NULL THEN
''
ELSE
LTRIM(RTRIM(jobStatus.current_executed_step_id))
END AS 'CurrentExecutedStepID',
CASE WHEN jobStatus.start_execution_date IS NULL THEN
''
ELSE
LTRIM(RTRIM(jobStatus.start_execution_date))
END AS 'StartExecutionDate',
CASE WHEN jobStatus.[step_name] IS NULL THEN
''
ELSE
LTRIM(RTRIM(jobStatus.[step_name]))
END AS 'StepName'
FROM msdb..sysjobs s
LEFT JOIN
(SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON
ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON
ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js ON
ja.job_id = js.job_id AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null
AND j.job_id = @JobID) AS jobStatus ON
jobStatus.[job_id] = s.job_id
WHERE s.job_id = @JobID;
END
END

END TRY
BEGIN CATCH
DECLARE
@ERROR_SEVERITY INT,
@ERROR_STATE INT,
@ERROR_NUMBER INT,
@ERROR_LINE INT,
@ERROR_MESSAGE NVARCHAR(4000);

SELECT
@ERROR_SEVERITY = ERROR_SEVERITY(),
@ERROR_STATE = ERROR_STATE(),
@ERROR_NUMBER = ERROR_NUMBER(),
@ERROR_LINE = ERROR_LINE(),
@ERROR_MESSAGE = ERROR_MESSAGE();

RAISERROR('Msg %d, Line %d, :%s',
@ERROR_SEVERITY,
@ERROR_STATE,
@ERROR_NUMBER,
@ERROR_LINE,
@ERROR_MESSAGE);
END CATCH

END

################################

 

 

 

To retive job name use the sample stored proc.

################################

ALTER PROCEDURE [dbo].[sp_SAMPLE_GetSQLAgentJobs]
-- Add the parameters for the stored procedure here
@SAMPLEInitName varchar(12) = 'SAMPLE'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT LTRIM(RTRIM(s.job_id)) AS 'JobID',
LTRIM(RTRIM(s.name)) AS 'JobName',
CASE WHEN jobStatus.current_executed_step_id IS NULL THEN
''
ELSE
LTRIM(RTRIM(jobStatus.current_executed_step_id))
END AS 'CurrentExecutedStepID',
CASE WHEN jobStatus.start_execution_date IS NULL THEN
''
ELSE
LTRIM(RTRIM(jobStatus.start_execution_date))
END AS 'StartExecutionDate',
CASE WHEN jobStatus.[step_name] IS NULL THEN
''
ELSE
LTRIM(RTRIM(jobStatus.[step_name]))
END AS 'StepName'
FROM msdb..sysjobs s
LEFT JOIN
(SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON
ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON
ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js ON
ja.job_id = js.job_id AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null
AND j.name like 'SAMPLE%') AS jobStatus ON
jobStatus.[job_id] = s.job_id
WHERE s.name like @RTVInitName+'%';
END

################################

 

Reply