How to check the SSIS package job results after it

问题:

I have an SSIS package which imports the data into the SQL Server 2008 database. I have set up the schedule job in the SQL Server Agent to run that package. When I check the history, I could only see whether the job ran successfully or not. I could not see other messages apart from that.

I would like to know how many records are imported whenever the job is executed. How can I monitor that? Should I use the additional components in SSIS package or set some configurations in SQL Server Agent Job Setup?

I found some logging facilities in SQL Server Agent Job Setup but I am not sure it can fulfill my requirements or not.

回答1:

If you are just interested in knowing the columns being processed and not interested with the info for further use, one possible option is making use of the SSIS logging feature. Here is how it works for data flow tasks.

1. Click on the SSIS package.
2. On the menus, select SSIS --> Logging...
3. On the Configure SSIS Logs: dialog, select the provider type and click Add. I have chosen SQL Server for this example. Check the Name checkbox and provide the data source under Configuration column. Here SQLServer is the name of the connection manager. SSIS will create a table named dbo.sysssislog and stored procedure dbo.sp_ssis_addlogentry in the database that you selected. Refer screenshot #1 below.
4. If you need the rows processed, select the checkbox OnInformation. Here in the example, the package executed successfully so the log records were found under OnInformation. You may need to fine tune this event selection according to your requirements. Refer screenshot #2 below.
5. Here is a sample package execution within data flow task. Refer screenshot #3 below.
6. Here is a sample output of the log table dbo.sysssislog. I have only displayed the columns id and message. There are many other columns in the table. In the query, I am filtering the output only for the package named 'Package1' and the event 'OnInformation'. You can notice that records with ids 7, 14 and 15 contain the rows processed. Refer screenshot #4 below.

Hope that helps.

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

回答2:

Here's another approach for when SQL Server job history is not showing output from SSIS packages: use DTEXEC command lines.

(Upside: this approach puts the job's output where anyone else supporting it would expect to find it: in job history.
Downside for big packages: if you have a long SSIS package, with lots of tasks or components, and lots of output, then the job history will split package output into many lines of job history, making the approach in the previous answer--logging to a table--easier to read.)

To show SSIS package output in the job's View History:
(1) Change the job steps from type "SQL Server Integration Services Package", to "Operating system (CmdExec)",
(2) Use DTEXEC command lines, to execute the packages.

Example of command line:

DTExec /DTS "\MSDB\myPkgName" /DECRYPT pkgPass /MAXCONCURRENT " -1 " /CHECKPOINTING OFF 

Note that if the SSIS package requires 32-BIT execution (true for exporting to Excel, for example), then use the DTEXEC utility in "Program Files (x86)" by fully qualifying it. Example, where the SQL Server application was installed on an "E:" drive, and where SQL Server 2014 is being used:

"E:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTExec.exe" /DTS "\MSDB\myPkgName" /DECRYPT pkgPass /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

If your SSIS packages are in the file system (as ".dtsx" files), then replace "/DTS" with "/FILE".

If your SSIS packages were placed in SSISDB (using the "project deployment model", which is available starting with SQL Server 2012, instead of the older "package deployment model"), then replace "/DTS" with "/ISSERVER".

Next, go into the job step's "Advanced" page, and make sure that the box is checked for "Include step output in history".

Lastly, consider your job step's "Run as": if your job steps "Run as" were already set to a proxy, on job steps of type "SQL Server Integration Services Package", then you already made that proxy active to the subsystem "SQL Server Integration Services Package". Now, to do command lines like the above, check the proxy's properties, and make sure it is also active to the subsystem "Operating system (CmdExec)".

MSDN reference: SSIS Output on Sql Agent history

回答3:

If you have deployed the package to the database's Integration Services Catalog (rather than load it from a file system) you can easily get detailed reporting.

Open the catalog node in SQL Server Management Studio, right click the Package name, select Reports | Standard Reports | All Executions and see details about every step of the job and its subcomponents, including records imported.

回答4:

use the below procedure for getting SSIS errors with execution id

CREATE PROCEDURE [dbo].[get_ssis_status] @EXECUTION_ID INT\n
AS
BEGIN
  SELECT o.operation_id EXECUTION_ID
    ,convert(datetimeoffset,OM.message_time,109) TIME
    ,D.message_source_desc ERROR_SOURCE
    ,OM.message ERROR_MESSAGE
    ,CASE ex.STATUS
        WHEN 4 THEN 'Package Failed'
        WHEN 7 THEN CASE EM.message_type 
            WHEN 120 THEN 'package failed' 
            WHEN 130 THEN 'package failed' ELSE 'Package Succeed'END
        END AS STATUS
FROM SSISDB.CATALOG.operation_messages AS OM
INNER JOIN SSISDB.CATALOG.operations AS O ON O.operation_id = OM.operation_id
INNER JOIN SSISDB.CATALOG.executions AS EX ON o.operation_id = ex.execution_id
INNER JOIN (VALUES (- 1,'Unknown'),(120,'Error'),(110,'Warning'),(130,'TaskFailed')) EM(message_type, message_desc) ON EM.message_type = OM.message_type
INNER JOIN (VALUES 
 (10,'Entry APIs, such as T-SQL and CLR Stored procedures')
,(20,'External process used to run package (ISServerExec.exe)')
,(30,'Package-level objects')
,(40,'Control Flow tasks')
,(50,'Control Flow containers')
,(60,'Data Flow task')
    ) D(message_source_type, message_source_desc) ON D.message_source_type = OM.message_source_type
WHERE ex.execution_id = @EXECUTION_ID
AND OM.message_type IN (120,130,-1);
END

回答5:

If you insert rows using Execute SQL task you can get record count using @@ROWCOUNT variable. Then you should write it somewhere to read it later for report.