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:
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.