Archive for the ‘Audit’ Category

Creating Excel Files (.xls) dynamically from SSIS

April 25, 2011 1 comment

In this blog, I will walk you through the creation of excel files ( xls / Excel 2003)  dynamically through SSIS.

Scenario: every day one of my process runs to pull data from SQL server table to excel file. I need to use unique file everyday with name as “taxonomy_<date>.xls” , for example on 14th of January 2011 the file name should be “Taxonomy_01142011.xls” with the excel sheet name “TaxonomyValues”.

It is pretty simple to create this file dynamically. At first we need to set up an excel connection manager pointing to the file. The connection manager needs to be dynamically configured to point to the correct file everyday, in our case “Taxonomy_01142011.xls” on 14th Jan. To do this,

  1. go to properties window of the excel connection manager
  2. click on expression and the browse ( … symbol) and choose “excel File Path” property. ( please refer the pictures below)
  3. Copy and paste the expression given below or develop similar expression. Click on evaluate expression and it will display the file path as evaluated value.

Continue reading..



Adding Audit information to you dataset using SSIS.

August 13, 2009 2 comments

Adding audit information to your dataset such as user/ machine that modified  the data, package that inserted data into your table etc,. might be required while performing an ETL task. In SSIS you can achieve this by using the transformation “AUDIT” available in DFTs. Inside the DFT you can add “audit transformation” after any other transformation as shown.


You need to select the “Audit type” of your choice like package name, package ID, user name etc and assign appropriate column name to each of these  audit type .


Click OK. All the records in your dataset will now have the selected audit types as the extra columns.


In the above image you can see two new columns “executed_package_id” and “Execution_instanceID” added  to the dataset. You can use these columns in the following transformations as well as push it into a DB table. Hence any thing that gets modified in your table through packages can be tracked.