THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Synchronous execution of SSIS packages using T-SQL : Denali

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!

image

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;


Published Friday, November 19, 2010 8:25 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jeff Wharton said:

Great article

July 5, 2011 8:25 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement