Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to Perform an Audit Transformation in SSIS – Querychat

In the article, we learn how SSIS Transformations works to audit the specified target table. Providing an example of SSIS Audit Transformation to a database table in SQL Server.

SSIS stands for SQL Server Integration Service. It is one of the ETL (Extract Transform Load) tools used to create ETL packages. SSIS provides an Audit Transformation that allows us to get additional information required in auditing.

An Example Playground

Here’s a scenario for our demonstration purpose using a table named major in the student database. We will conduct an Audit Transformation for student majors based on source columns to have column data in addition to audit information. 

Let’s follow step-by-step below on how we perform an Audit Transformation in SSIS.

Step #1 Create a Student table in SQL Server

We first create a student database and a major table in SQL Server. The table consists of IDs, names, and majors for each student. The following syntax is to create them through Transact-SQL and add a few sample data of student majors.

CREATE DATABASE student;

GO

USE student;

GO

 

CREATE TABLE major (student_id INT, student_name VARCHAR(50), student_major VARCHAR(50))

GO

 

INSERT INTO major VALUES (1, 'Zepsy', 'ACCOUNTING')

INSERT INTO major VALUES (2, 'Ningrum', 'MEDICAL SCIENCE')

INSERT INTO major VALUES (3, 'Fistammy', 'BUSINESS ADMINISTRATION')

INSERT INTO major VALUES (4, 'Andina', 'COMPUTER ENGINEERING')

INSERT INTO major VALUES (5, 'Arifin', 'ENGLISH')

GO

 

SELECT * FROM major;

The result of the query above will look like this:

student_id student_name student_major
1 Zepsy ACCOUNTING
2 Ningrum MEDICAL SCIENCE
3 Fistammy BUSINESS ADMINISTRATION
4 Andina COMPUTER ENGINEERING
5 Arifin ENGLISH

Table 1. Example of the major table

Step #2 Create a new SSIS package

The SSIS package contains a collection of tasks that are executed in a specific order and are the main components of SSIS. Packages can be stored in SQL Server in the msdb database, or as a .dtsx file. We create a package named SSISpackage as follows:

Figure 1. Create an SSIS project

By clicking the OK button above then the results will look like this:

Figure 2. Create a new DTS package 

Step #3 Using the Data Flow Task in the Control Flow

Control flow is the highest level of control process that can be used to manage data flow process activities and other processes in a package that contains tasks and containers. While the Data Flow is used for ETL processes in which there is a process control component. The components consist of source, transformation, and destination. 

Here’s how we put the Data Flow Task in the Control Flow:

Figure 3. Data Flow Task in the Control Flow

Step #4 Using the OLE DB connections

The most dominant component in the SSIS architecture is a variable in a package that can be managed dynamically when it is run. Connection settings, one of which uses OLE DB, is a common example of using variables. Expand the Other Source menu in the SSIS Toolbox to get the OLE DB source item and then put it into the SSIS Data Flow as follows:

Figure 4. OLE DB Source in the Data Flow 

Then double-click the OLE DB Source to determine the connection as follows:

Figure 5. The OLE DB Source configuration 

In the figure above uses the (local)\MYSQLSERVER.STUDENT.sa as the OLE DB connection and Table or view option of data access mode for the major table in the student database.

Step #5 Launch the Audit Transformation

The Audit is a very simple transformation that allows us to submit to Audit and makes it easier to read. Expand the Other Transforms menu in the SSIS Toolbox and then put the Audit item into the Data Flow as follows:

Figure 6. The Audit Transformation in the Data Flow 

Connect between OLE DB Source and Audit as shown above and then double-click the Audit to determine what columns will be displayed in the Audit information. See the following figure to add extra information about the Audit types:

Figure 7. Add the Audit information 

Before executing the Audit result, we need to put the Derived Column into the Data Flow and then enable the Data Viewer as follows:

Figure 8. Add the Derived Column and Enable the Data Viewer 

Once done the step above, now we start debugging the package by using short-key F5 and the Audit output is shown below:

Figure 9. The Audit Transformation output 

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc