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

Karthikanbarasan
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 8464 red flag

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. 

To follow my series of articles on SSIS packages, please click here.

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.



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

ValueVariable NameDescription
0ExecutionInstanceGUIDGUID that identifies the instance running
1PackageIDUnique identifier od the package running
2PackageNameName of the package
3VersionIDVersion ID of the package
4ExecutionStartTimeTime when the package is started
5MachineNameComputer Name on which package running
6UserNameUser Name under which package running
7TaskNameName of the task which is running
8TaskIdUnique 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



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



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



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

 

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.

Page copy protected against web site content infringement by Copyscape

About the Author

Karthikanbarasan
Full Name: Karthikeyan Anbarasan
Member Level: Silver
Member Status: Member,Moderator,Microsoft_MVP,MVP
Member Since: 1/2/2011 3:00:14 AM
Country: India
Thanks Karthik www.f5Debug.net
www.f5Debug.net
www.f5Debug.net Around 5 years of experience in Microsoft technologies like ASP.Net, VB.Net, C#.net, SQL server, Biztalk Server, WCF, WPF, SSIS, SSRS, SSAS, AJAX and working as Senior Analyst in a US based MNC.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)