SQL Agent SSIS execution Vs EXEC [SSISDB].[catalog].[start_execution]

Hi,

Same SSIS package behaves differently depending when executed via SQL Agent compared to using TSQL. Job has two-parts.

1\ Checks location and moves .csv files to different location
2\ Imports files with ForeEachLoop.

SQL Agent job works fine. TSQL Version executes step 1 and step 2 within a second, therefore step one is still moving files - takes around 3 minutes. So step 2 completes with no files imported. How does the SQL Agent job "know" when the last file is copied to new location and Step 2 can start. Whereas scripting the job and running in Query Analyser does not.

Any ideas?

Could add a WAITFOR DELAY operation before Step 2. However we don't know how long on each run the file transfer will take. One day might be 2 files, another day 1000s.

post the TSQL you are using for comparison

Here's the syntax created opening the package(s) into Integration Catalogue | Execute | explicitly provide new parameter, then use script function to generate code. It does work if exec step 1, wait 5 minutes exe step 2 - only problem step 2 should wait until step 1 has completed the file move otherwise the folder it inserts from is empty (until step 1 completes)

-- Step #1 (this package collects .csv's from one folder and relocates into another. takes 3 minutes)

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'name.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'folder', @project_name=N'project', @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 datetime = N'2015-09-24T11:25:57.000'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'pFileDate', @parameter_value=@var0
DECLARE @var1 sql_variant = N'H:\some_Location'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'pFilePath', @parameter_value=@var1
DECLARE @var2 sql_variant = N'H:\some_Location'
DECLARE @var sql_variant = N'H:\some_Location'
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=30, @parameter_name=N'pFilePath', @object_name=N'name.dtsx', @folder_name=N'folder', @project_name=N'project', @value_type=V, @parameter_value=@var
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'pSourceDir', @parameter_value=@var2
DECLARE @var3 sql_variant = N'Data Source=instance;Initial Catalog=source;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'CM.HDS.ConnectionString', @parameter_value=@var3
DECLARE @var4 smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var4
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

-- step #2 (this package then bulk inserts the .csv into sql tables)


Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'name.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'folder', @project_name=N'project', @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 sql_variant = N'H:\some_Location'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'pFilePath', @parameter_value=@var0
DECLARE @var1 sql_variant = N'Data Source=.;Initial Catalog=source;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'CM.HDS.ConnectionString', @parameter_value=@var1
DECLARE @var2 smallint = 2
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var2
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

Missing some kind of dependency logic on Step 1

Second package starts like this. Just when run from QA all scripts run at the same time

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'name.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'folder', @project_name=N'project', @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 datetime = N'2015-09-25T11:25:57.000'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'pFileDate', @parameter_value=@var0
DECLARE @var1 sql_variant = N'H:\some_Location'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'pFilePath', @parameter_value=@var1
DECLARE @var2 sql_variant = N'H:\some_Location'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'pSourceDir', @parameter_value=@var2
DECLARE @var3 sql_variant = N'Data Source=instance;Initial Catalog=HDS_Martin;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'CM.HDS.ConnectionString', @parameter_value=@var3
DECLARE @var4 smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var4
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

So I see you doing a start execution but not checking for completion.

Thank you. Now found this

https://msdn.microsoft.com/en-us/library/ff878160.aspx

Calling [catalog].[start_execution] does not wait for the SSIS package to finish executing
It should be noted that after calling [catalog].[start_execution], your script does not wait for the SSIS package to finish executing before continuing. If any subsequent script requires the SSIS package to be complete, you must pause execution with a WHILE loop, using WAITFOR, and checking the status of the execution, before allowing your script to continue.

Example:

DECLARE @ssis_execution_status BIGINT = 1;

EXECUTE [SSISDB].[catalog].[start_execution]
@ssis_execution_id;

WHILE(@ssis_execution_status NOT IN (3,4,6,7,9)) BEGIN --The possible values are created (1), running (2), canceled (3), failed (4), pending (5), ended unexpectedly (6), succeeded (7), stopping (8), and completed (9) (https://msdn.microsoft.com/en-us/library/ff878089.aspx),
WAITFOR DELAY '00:00:01'; --Pause for 1 second.

--Refresh the status.
SET @ssis_execution_status = (
SELECT
[executions].[status]
FROM
[SSISDB].[catalog].[executions]
WHERE
[executions].[execution_id] = @ssis_execution_id
);
END

glad you found it! :grinning:

Only with your help.

Worked with SQL Server 16 years and still new challenges every day.

Big thanks