This post relates to SQL Server code-named Denali which at the time of writing is a pre-release version of the next version of SQL Server. It is also known as SQL11 and is expected to be released sometime in 2011 or 2012. The name given to it on release is not yet known. Some of the information in this blog post may not be true upon release.
UPDATE, 2011-11-24: None of the faffing around that I describe below is required any longer. At the time of writing Release Candidate 0 has been released in which SSIS now has the ability to execute packages synchronously or asynchronously. Davide Mauri has all the details.
In my last Denali-related post SSIS Server, Catalogs, Environments, Environment Variables in SSIS in Denali I mentioned that it is now possible to execute SSIS packages using T-SQL and in this blog post I’ll take a look at how we do that.
There are two stored procedures that you need to know about:
- [catalog].[create_execution]
- [catalog].[start_execution]
They’re fairly self-explanatory. An execution is an object on the SSIS server that links together a project and a package within that project with an environment (see previous blog posts for explanations of those terms).
[catalog].[start_execution] executes packages asynchronously, by that I mean that it will return immediately while the package being executed continues to execute. At the time of writing there is no way to execute the package synchronously (i.e. have [catalog].[start_execution] wait until the package has completed) but we can give the illusion of synchronous execution using a loop and T-SQL’s WAITFOR command. Here’s the code, it assumes that you have already deployed your project and given it some environment references:
First declare and initialise some variables indicating what we are going be executing, you can change as appropriate:
DECLARE @project_name NVARCHAR(128) = 'ProjectParametersDemo'
,@package_name NVARCHAR(128) = 'Parent.dtsx'
,@folder_name NVARCHAR(128) = 'Demo'
,@environment_name NVARCHAR(128) = 'Development'
,@reference_id BIGINT
,@execution_id BIGINT
,@running BIT = 1;
get the reference_id that represents the link between a project and an environment:
SELECT @reference_id = pe.[reference_id]
FROM [catalog].[project_environments] pe
INNER JOIN [catalog].[projects] p ON pe.[project_id] = p.[project_id]
INNER JOIN [catalog].[folders] f ON p.[folder_id] = f.[folder_id]
WHERE f.[name] = @folder_name
AND p.[name] = @project_name
AND pe.[environment_name] = @environment_name;
declare and kick off the execution using the stored procedures I mentioned earlier:
EXEC [catalog].[create_execution]
@folder_name = @folder_name
,@project_name = @project_name
,@package_name = @package_name
,@reference_id = @reference_id
,@execution_id = @execution_id OUTPUT;
EXEC [catalog].[start_execution]
@execution_id = @execution_id;
Here is where we need to give the illusion of synchronous execution by looping continuously until the execution has completed:
WHILE (@running = 1)
BEGIN
WAITFOR DELAY '00:00:05';
SELECT @running = CASE WHEN e.[status] IN (2,5,8) THEN 1 ELSE 0 END --Running,Pending,Stopping
FROM [catalog].[executions] e
WHERE e.[execution_id] = @execution_id;
END
Once execution is complete and we exit the loop we can find out whether our execution succeeded or not:
SELECT e.[execution_id],e.[start_time],e.[end_time],
CASE WHEN e.[status] = 1 THEN 'Created'
WHEN e.[status] = 2 THEN 'Running'
WHEN e.[status] = 3 THEN 'Cancelled'
WHEN e.[status] = 4 THEN 'Failed'
WHEN e.[status] = 5 THEN 'Pending'
WHEN e.[status] = 6 THEN 'Ended Unexpectedly'
WHEN e.[status] = 7 THEN 'Succeeded'
WHEN e.[status] = 8 THEN 'Stopping'
ELSE 'Completed'
END AS [status_name]
FROM [catalog].[executions] e
WHERE e.[execution_id] = @execution_id;
Sure enough, in this case, it did!
That’s it – synchronous execution of SSIS packages just using plain T-SQL has arrived in Denali and I can see this feature being very useful indeed. The full code listing is below.
Incidentally if you would like the ability to execute packages synchronously without this faffing around with looping and WAITFOR then there is a Connect submission that needs a vote and a comment from you: [SSIS Denali] Synchronous Executions
@Jamiet
--Declare & Init
DECLARE
@project_name NVARCHAR(128) = 'ProjectParametersDemo'
,@package_name NVARCHAR(128) = 'Parent.dtsx'
,@folder_name NVARCHAR(128) = 'Demo'
,@environment_name NVARCHAR(128) = 'Development'
,@reference_id BIGINT
,@execution_id BIGINT
,@running BIT = 1;
SELECT @reference_id = pe.[reference_id]
FROM [catalog].[project_environments] pe
INNER JOIN [catalog].[projects] p ON pe.[project_id] = p.[project_id]
INNER JOIN [catalog].[folders] f ON p.[folder_id] = f.[folder_id]
WHERE f.[name] = @folder_name
AND p.[name] = @project_name
AND pe.[environment_name] = @environment_name;
--Create an instance of an execution
EXEC [catalog].[create_execution]
@folder_name = @folder_name
,@project_name = @project_name
,@package_name = @package_name
,@reference_id = @reference_id
,@execution_id = @execution_id OUTPUT;
--and start that instance
EXEC [catalog].[start_execution]
@execution_id = @execution_id;
--wait until its executed
WHILE (@running = 1)
BEGIN
WAITFOR DELAY '00:00:05';
SELECT @running = CASE WHEN e.[status] IN (2,5,8) THEN 1 ELSE 0 END --Running,Pending,Stopping
FROM [catalog].[executions] e
WHERE e.[execution_id] = @execution_id;
END
--Get the results!
SELECT e.[execution_id],e.[start_time],e.[end_time],
CASE WHEN e.[status] = 1 THEN 'Created'
WHEN e.[status] = 2 THEN 'Running'
WHEN e.[status] = 3 THEN 'Cancelled'
WHEN e.[status] = 4 THEN 'Failed'
WHEN e.[status] = 5 THEN 'Pending'
WHEN e.[status] = 6 THEN 'Ended Unexpectedly'
WHEN e.[status] = 7 THEN 'Succeeded'
WHEN e.[status] = 8 THEN 'Stopping'
ELSE 'Completed'
END AS [status_name]
FROM [catalog].[executions] e
WHERE e.[execution_id] = @execution_id;