Introducing sp_ssiscatalog (v1.0.0.0)

re: Introducing sp_ssiscatalog (v1.0.0.0)

Koen Verbeeck — Wed, 07 Nov 2012 06:56:32 GMT

This seems awesome. I'll try it out soon.

re: Introducing sp_ssiscatalog (v1.0.0.0)

mbourgon — Thu, 08 Nov 2012 16:03:17 GMT

Jamie, can I request some functionality? We're trying to get SSIS 2012 jobs to email failure info, since the default sysjobhistory is, honestly, useless (we might be able to get it by changing the subevent type, then the logging mode, of the job step.

So I currently have the below code that will get the package that was run for a particular job/step (getting the actual job name will be done via tokens; I left placeholder code to find the most recent)

DECLARE @full_ssis_command VARCHAR(4000)

, @job_id UNIQUEIDENTIFIER

, @job_step_id INT

--get most recent failed SSIS package

SELECT TOP 1 @job_id = job_id FROM msdb.dbo.sysjobhistory WHERE run_status <> 1 AND step_id > 0 AND message LIKE '%SQL Server Execute Package Utility%' ORDER BY instance_id DESC

--determine which job_step

SELECT TOP 1 @job_step_id = step_id FROM msdb.dbo.sysjobhistory WHERE run_status <> 1 AND step_id > 0 AND job_id = @job_id ORDER BY instance_id DESC

SELECT @full_ssis_command = command FROM msdb.dbo.sysjobsteps WHERE job_id = @job_id AND step_id = @job_step_id

IF @full_ssis_command LIKE '%.dtsx%'

BEGIN

SELECT RIGHT(LEFT(@full_ssis_command,CHARINDEX('.dtsx',@full_ssis_command)-1),CHARINDEX('\',REVERSE(LEFT(@full_ssis_command,CHARINDEX('.dtsx',@full_ssis_command)-1)))-1)+'.dtsx'

END

So, the next steps for me (and hence my request) would be:

* Having sp_ssiscatalog return only one result set, so that I can invoke it via sp_send_dbmail. It looks like I can do that by invoking:

EXEC ssisreportingpack..sp_ssiscatalog

@execs_package_name = @package_name,

@execs_status_desc = 'failed',

@operation_type='execs'

* Getting the full error message. I can query message from catalog.event_messages where event_name = 'OnError', and I'm using the underlying queries from the SSMS SSIS Dashboard to get a ton of info, but since yours is going to improve and mine will probably get to a happy point (for me) and stay there, I'd rather use yours.

A dacpac limitation – Deploy dacpac wizard does not understand SqlCmd variables

SSIS Junkie — Tue, 13 Nov 2012 21:53:05 GMT

Since the release of SQL Server 2012 I have become a big fan of using dacpacs for deploying SQL Server

sp_ssiscatalog v1.0.1.0 now available for download

SSIS Junkie — Tue, 20 Nov 2012 21:42:09 GMT

13 days ago I wrote a blog post entitled Introducing sp_ssiscatalog (v1.0.0.0) in which I first made

re: Introducing sp_ssiscatalog (v1.0.0.0)

jamiet — Tue, 20 Nov 2012 21:56:15 GMT

mbourgon,

Nice idea, and I really appreciate you taking the time to submit it - thanks.

Let me clarify though - do you want to get an error message (if one exists) when querying for a list of executions using:

EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_status_desc='failed',@execs_package_name='Package.dtsx'

I'm not quite sure how that would work. If there are multiple executions returned, which one should it return an error message for? Are you suggesting (for example) that there should be an extra column that returns (say) the first error in the execution?

regards

Jamie

Documenting sp_ssiscatalog

SSIS Junkie — Tue, 04 Dec 2012 23:19:31 GMT

What is the best way to document an API ? Moreover, what is the best way to document a T-SQL API? Before