5

I'm looking for a way - either using in-built SSIS functionality or custom-built (3rd party software or internally-coded) - to "wrap" every executed SSIS bundle with specific code, e.g. launch certain SQL procedures prior to and after all execution steps.

These are meant to log flow steps, handle errors, set up notifications, etc.

The key idea here is to avoid editing and adding this code manually to every single SSIS bundle manually - albeit doable, it's a pain in the neck to maintain if anything were to change in above setup.

Any advice on the topic from you SSIS / SQL Server gurus would be highly appreciated.

EDIT: alternatively, is there a way to easily refactor existing deployed SSIS packages and add / modify that code quickly and easily in one go?

Alex
  • 73
  • 3
  • You can modify the SP executed by the SSIS when you enable logging into an SQL Server database on the DTS. The sp is marked as a system SP and it's `dbo.sp_ssis_addlogentry`. http://www.sqlcircuit.com/2013/08/how-to-enable-ssis-package-logging-for.html – EzLo Aug 20 '19 at 07:28
  • Thanks @EzLo, that does sound **very similar** to what I'm looking for. 2 questions: 1) do I need to enable logging for every package manually or is there a way to enable it with set parameters for all deployed packages? 2) will making changes to logging parameters overwrite what I might add to 'dbo.sp_ssis_addlogentry' on my own in the meantime? – Alex Aug 20 '19 at 08:18
  • 1) if you use this approach then you will have to edit all DTS to enable logging and direct them to the same database connection. You *might* be able to use some black magic to massively change the DTS (which are xml files really) and add the logging step via c# or any other programming language, but it's not easy, I don't know how to do it and I wouldn't recommend trying it unless you have a **very big** amount of dts and they are all using the same version. 2) i believe that SSIS will only try to create the SP if it doesn't already exist, but I'd test it to be sure. – EzLo Aug 20 '19 at 10:46

1 Answers1

0

1.I'm looking for a way - either using in-built SSIS functionality or custom-built (3rd party software or internally-coded) - to "wrap" every executed SSIS bundle with specific code, e.g. launch certain SQL procedures prior to and after all execution steps.

Please refer to Execute SQL Task.

2.These are meant to log flow steps, handle errors, set up notifications, etc.

log flow steps--Please refer to SSIS Logging.

handle errors--Please refer to Integration Services (SSIS) Event Handlers and

Capturing SQL Server Integration Services Package Errors Using OnError Event Handlers.

set up notifications--Please refer to SSIS Script Task and Getting started with the SSIS Script Task.

3.Alternatively, is there a way to easily refactor existing deployed SSIS packages and add / modify that code quickly and easily in one go?

Please refer to Editing Published SSIS Package in SQL Server 2012.

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
  • Unfortunately, not a single link from those you provided answers the question I posed in my initial post. – Alex Aug 20 '19 at 11:02