WITH ordered
AS (SELECT UNIT
          ,ORDER_DATE
          ,rn1 = ROW_NUMBER...

You could change the approach to use a script task to execute your powershell script.  However, if you can do this in PS - then you almost certainly can...

How are you specifying to run PowerShell, as a scripting task? Spitballing here, but could you run it as an Execute Process Task, with powershell.exe as the executable? Then...

I said 'add another THROW', not 'add another THROW 51000, '2. One or more stored procedures had errors, please investigate', 1;'!

Also, surely you want the INSERT to dbo.RunTimeErrors to...

Yes I did.  If I add the second THROW then the table...

No, only 1 TV is required. You simply run multiple INSERTS to it in your code.

To make the error appear in the SSIS Output window, you need to add...

Instead of logging your errors in a physical table (whose changes are being rolled back, I assume), log them in a table variable and then insert from the table...

• This reply was modified 7 months, 1 week ago by water490.

I swapped things around a bit. Obviously, I cannot test it.

ALTER PROCEDURE dbo.RunStoredProcedures
AS
SET NOCOUNT ON;

DECLARE @FAIL_COUNT INT = 0;

BEGIN TRANSACTION;

BEGIN TRY
    BEGIN TRY
...

I made some progress on this.

Code:

ALTER procedure [dbo].[RunStoredProcedures]
as

DECLARE @FAIL_COUNT INT = 0

BEGIN TRANSACTION

BEGIN TRY
BEGIN TRY
  truncate table dbo.temptable
END TRY
BEGIN CATCH
SET @FAIL_COUNT = @FAIL_COUNT + 1
END CATCH

BEGIN...

I have been thinking more about this.  I don't think relying on SSIS output window is the right way to capture errors.  I need a persistent record of errors (not...

• This reply was modified 7 months, 1 week ago by water490.
• This reply was modified 7 months, 1 week ago by water490.
• This reply was modified 7 months, 1 week ago by water490.
• This reply was modified 7 months, 1 week ago by water490.

deleted reply

• This reply was modified 7 months, 1 week ago by water490.

Thank you!

I have some questions:

1. re the output parameter option solution.  Will that message show up in the output window?

2. re the alternative solution.  I am getting an error message...

Actually...the above two outputs are not that important.  The SP is run in SSIS so I never see the above two issues.  Ok for now.

The more pressing issue...

Actually...the above two outputs are not that important.  The SP is run in SSIS so I never see the above two issues.  Ok for now.

The more pressing issue is that...

Thanks for the reply.  That is the entire code.  Not sure where/how it got -6.  Any ideas on how it got there?