Floating FB popout byF5debug

SQL Server Integration Services (SSIS) – Part 38 – Audit Transformations Control in SSIS

 

This is part 38 of the series of article on SSIS. In this article we are going to see on how to use an Audit transformation control.

Introduction:

In this article we are going to see on how to use an Audit transformation control. Audit transformation helps the uses to build a package which requires about the environment on which the package runs like the computer name, the path where the package is running, name of the package and the operator by using the system variable available with these functions. Let’s jump start into the example to see on how to use this control.

You can look into my series of article on SSIS at the url - http://f5debug.net/tutorial/ssis.aspx

[more]

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on how to use an audit control. Now drag and drop an audit control as shown in the below screen.

clip_image001

To configure the audit transformation, as mentioned earlier it uses some of the system variables to be used across the packaging process. The list of system variables used are as follows

Value

Variable Name

Description

0

ExecutionInstanceGUID

GUID that identifies the instance running

1

PackageID

Unique identifier od the package running

2

PackageName

Name of the package

3

VersionID

Version ID of the package

4

ExecutionStartTime

Time when the package is started

5

MachineName

Computer Name on which package running

6

UserName

User Name under which package running

7

TaskName

Name of the task which is running

8

TaskId

Unique identifier of the task running

Now to start configuring the Audit task, just double click on the control will open the popup where we need to select the process which we need to carry on as shown in the below screen

clip_image002

Here we are collecting the information and going to store in a file, so add a flat file destination as shown in the below screen

clip_image003

Now press F5 to build and execute the package. It will run the package and show a screen like below

clip_image004

Now navigate to the path where we gave for the output folder and open the file. We can see the output as below

clip_image005

Here you can find the packageName and the VersionID at the right end for all the records which satisfied the condition.

Conclusion:

So in this article we have seen on how to use the AUDIT transformation control to make some audit for the process happening inside the package.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter