This is the 19th day of my participation in the Gwen Challenge in November. Check out the details: The last Gwen Challenge in 2021

Translation reference

This article mainly refers to Advanced Logging-Level 12 of The Stairway to Integration, translated from The Stairway to Integration Services series “Services” aims to have a comprehensive and clear understanding of SSIS. All contents are carried out in practice on the basis of the original text. Due to various reasons such as version differences and personal questions, the principle of complete translation is not adopted.

Thanks for your support!

Advanced logging

In the previous introduction we configured SSIS’s built-in logging. It demonstrates simple and advanced log configuration, export import log configuration, and custom log messages generated using Script Tasks and dTS.Events objects. In SSIS Learning Use 4: Control Flow Task error Handling, we discussed the behavior of bubbling events, shared the default behavior of bubbling action events, and introduced the parent-child pattern.

In this article we will combine what we’ve learned about event bubbling, logging, and parent-child patterns to create a custom SSIS package logging model (pattern).

The premise condition

disablePrecedence.dtsxThe event processing

Click precedence. DTSX SSIS packet control flow in any blank and press F4 to open the property. Change the DisableEventHandlers property to True.

Disabling built-in logging (Disable Built-In Logging)

The first thing we need to do is remove the existing logging configuration from the SSIS package.

Click the SSIS drop-down menu at the top and then click “Logging”. .

When the Configure SSIS Logs window is displayed, click the Delete button to Delete the existing text log file configuration.

Technically, this will stop logging Precedence. DTSX SSIS packets. In addition, it is recommended to clear the SSIS packet. Therefore, you are advised to deselect the Precedence packet in the Configure SSIS Log window.

It is recommended to delete the log File Connection Manager:

This clears the built-in logs for the previous part of the configuration.

It is recommended to review part 10 event bubbling in advanced event behavior…

Preparing for Parent-child SSIS Design Pattern Logging

First, we need a database and tables for logging. Let’s create a database called SSISStairwayConfig.

Use the script below to create the database

Use master
go
/* SSISStairwayConfig database */
If Not Exists(Select name
              From sys.databases
              Where name = 'SSISStairwayConfig')
 begin
  print 'Creating SSISStairwayConfig database'
  Create Database SSISStairwayConfig
  print 'SSISStairwayConfig database created'
 end
Else
 print 'SSISStairwayConfig database already exists.'
go
Copy the code

Next, use the following script to create the LG Schema and SSISErrors tables for logging.

Use SSISStairwayConfig
go
/* log schema */
If Not Exists(Select name
              From sys.schemas
              Where name = 'lg')
 begin
  print 'Creating lg schema'
  declare @sql varchar(100) = 'Create Schema lg'
  exec(@sql)
  print 'Lg schema created'
 end
Else
 print 'Lg schema already exists.'

/* lg.SSISErrors table */
If Not Exists(Select s.name + '. ' + t.name
              From sys.tables t
              Join sys.schemas s
                On s.schema_id = t.schema_id
              Where s.name = 'lg'
                And t.name = 'SSISErrors')
 begin
  print 'Creating lg.SSISErrors table'
  Create Table lg.SSISErrors
  (
    ID int identity(1.1)  Constraint PK_SSISErrors Primary Key Clustered,
    ErrorDateTime datetime Not Null Constraint DF_logSSISErrors_ErrorDateTime Default(GetDate()),
    ErrorDescription varchar(max) Null,
    ErrorCode int Null,
    SourceName varchar(255) Null
  )
  print 'Lg.SSISErrors created'
 end
Else
 print 'Lg.SSISErrors table already exists.'
Copy the code

We will prevent error data from the SSIS package from going to the Lg.SSISErrors table of the SSISStairwayConfig database.

Applying the parent-child SSIS Design Pattern to Logging

Open the “Event Handlers” TAB of the Parent. DTSX SSIS package to navigate to the “Parent” executable and the “OnError” Event handler.

Drag a “Execute SQL Task” to the OnError event handler interface and connect a success priority constraint from the “script Task” to the new “Execute SQL Task”.

Open the “Execute SQL Task” editor and change the ConnectionType property to ADO.NET.

Click on the Connection property value drop-down list and then click “New Connection” :

The New Connection option does three things for us.

First, it selects the correct connection manager type: ADO.NET connection.

Second, it creates a new ADO.NET Connection manager in the Connection Managers TAB at the bottom of the SSDT editor,

Configure ADO.NET Connection Manager editor

Click the “New” button to configure a new data connection.

In the Server Name drop-down box, select or enter the Server and instance Name of the SQL Server instance that you previously created the SSISStairwayConfig database. Select or enter SSISStairwayConfig from the Select or Enter a database name drop-down list.

Click the Test Connection button to ensure that you have access to the SSISStairwayConfig database. The test connection is successful as follows:

Click OK to close the connection manager editor and return to the Configure ADO.NET Connection Manager window.

“Data Connections” is stored in a Windows configuration file on your workstation. The “data connection” information will remain available on the workstation and will be accessible in future development SSIS packages. Click “OK” to close the window.

SSIS has Connection manager named “WIN – FR5GRQSCDPO. SSISStairwayConfig”, and place the name in “execute SQL task” attributes “Connection”. The actual connection manager “WIN – FR5GRQSCDPO. SSISStairwayConfig” can also be found in the connection manager window.

Next, click inside the value text box for the “SQLStatement” property, and then click the “Ellipsis” to open the “Enter SQL Query” window. Enter the following statement

Insert Into lg.SSISErrors
(ErrorCode
,ErrorDescription
,SourceName)
Values
(@ErrorCode
,@ErrorDescription
,@SourceName)
Copy the code

The window for entering SQL queries is as follows:

Click OK to close the window.

The created Insert statement contains three parameters: ErrorCode, ErrorDescription, and SourceName. These parameters are similar to the SSIS variables previously read in the Parent. DTSX SSIS package OnError event handler’s “script task “(previously created in SSIS Learning Uses iv: Control Flow Task error Handling).

Click “Parameter Mapping” in the list to the left of the “Execute SQL Task” editor to open the Parameter Mapping page, where you connect SQL query parameters to OnError event handler variables.

Click the Add button and click the “Variable Name” drop-down box to change the Variable Name to “System::ErrorCode”. Change the Parameter Name to ErrorCode.

The System::ErrorCodeSSIS variable is an Int32(INTEGER) data type whose value replaces the @ErrorCode parameter in Insert statements.

The “Direction” options are Input, Output, and ReturnValue, and the Data Type column contains a list of ADO.Net Data types. Why ADO.Net data Type, because we selected the Connection Type when we started configuring the current “Execute SQL task “(” general page “). If the Connection Type property was OLEDB, the data Type would be different. Parameter Size does not need to be modified.

Another difference between ADO.Net and OLEDB connection types is that you can use the name of a parameter in the “Parameter Name” column under SQL statements and ADO.Net. When using OLEDB, you must provide question marks in SQL statements and reference parameter names numerically (the first question mark is 0, the second question mark is 1, and so on). ADO.Net has clearer syntax for Insert(and other) statements.

Click the “Add” button to add another parameter: ErrorDescription. The System::ErrorDescription SSIS variable is a String data type whose value will replace the @errorDescription parameter in the Insert statement.

Finally, add the mapping for the SourceName parameter. The System::SourceNameSSIS variable is a String data type whose value will replace the @sourcename parameter in the Insert statement.

Note The data type in Parameter Mapping. If the data type is incorrect, the execution may fail.

Execute the Parent. DTSX package in the SSDT debugger and observe the OnError event handler.

When “Script Task 4” is asked for success, click “No”, Parent. DTSX SSIS package’s OnError event handler responds, then “Execute SQL Task” also executes.

SQL Server Management Studio (SSMS)

Use SSISStairwayConfig
go
Select * From lg.SSISErrors;
Copy the code

The result is as follows:

Pop quiz: How many lines of code or objects do you need in a subpackage to log errors in “parent-child mode”? (Answer: 0)

We also get the added benefit of consistency and collection: every error message is logged in the same format and location.

We get the additional benefit of consistency and collection: Each error message is logged in the same format and location.

conclusion

In this article, we combine our previous knowledge of event bubbling, logging, and parent-child patterns to create a custom SSIS package logging pattern.

We use the OnError event handler and Execute SQL Task of the Parent. DTSX SSIS package to capture and store the error information raised in the subpackage (precedence.dtsx). This can be done without adding additional logic to the subpackages, and a consistent format and location for storing SSIS error information is achieved.