Row Count Transformation [Audit Information]

-

Row Count Transformation [Audit Information]

Scenario:

Let’s say we receive flat file from our client that we need to load into our SQL Server table. Beside loading the data from flat file into our destination table we also want to track how many records loaded from flat file. To keep track of Records Inserted we can create Audit table.

Solution:

As we have to keep track for number of records loaded, we need to create a table where we can insert this information. Let’s create a table with three columns

CREATE TABLE dbo.PkgAudit
  (
     PkgAuditID      INT IDENTITY(1, 1),
     PackageName     VARCHAR(100),
     LoadTime        DATETIME DEFAULT Getdate(),
     NumberofRecords INT
  ) 


Step 1: 
Create Connection Manager for your flat file. I used below records in flat file

CountryName,SalePersonName,SaleAmount
uSA,aamir shahzad,100
Italy,andy,200
UsA,Mike,500
brazil,Sara,1000
INdia,Neha,200
Brazil,Barbra,200
Mexico,Anthony,500

Step 2: 
Create SSIS variable called RecordsInserted as shown below

Step 3:
Place Row Count Transformation to Data Flow Pane and connect Flat File Source to Row Count Transformation. Double click on Row Count Transformation and choose RecordsInserted Variable as shown below
Step 4:
Use any destination such as OLE DB Destination, Flat File where you want to insert data from Source. In our case I used Multicast for testing purpose as can be seen below

Step 4:
When we execute our package the rows are inserted into destination by passing Row Count Transformation. All the count is saved in the variable. Our next goal is to save this information to our Audit Table for record.
In Control Flow Pane , Bring Execute SQL Task and Configure as shown below

Map the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below

Final Output
Let's run our package and see if information is recorded in our Audit table. As you can see below 7 records were loading from source file to our destination. The same Audit table can be enhanced by adding more columns such as records update, record deleted , records rejected and save all these stats while execution of package in different variables and at the end insert into Audit Table.

Comments

Post a Comment

Popular posts from this blog

SharePoint 2007 - Simple Task Dashboard

-
Image
example of creating a simple Task Dashboard. This is a pretty basic example of using all OOTB (out of the box) features and combining them to quickly build a solution. Scenario: We want to provide a way for users to easily see a summary of the current project tasks. We want them to be able to see the following information: Open Tasks Assigned to Them Overdue Tasks Tasks Due This Week Task Calendar Tasks Assigned to Others Solution: To build this solution we will be using several different tools available in SharePoint, all included in the list below: Task List Task List Views We will create views for each of the different ways we want to see the data displayed on our dashboard. Web Part Page List View Web Parts Custom Navigation The first thing we will do is create our task lists and then configure all the views that we need. To create the list, go to View All Site Content, Create and then select the Task list. Next, we will need to configure our custom views. To do this, w
Read more

MERGE transformation in SSIS

-
Image
In this post we are gonna discuss about MERGE transformation. MERGE in SSIS is equal to UNION ALL in SQL Server. This transformation unions two datasets/tables.The merge Transformation combines two  sorted  dataset into single dataset. Highlighted the text SORTED in last statement because “It is not possible to use MERGE when the inputs are NOT SORTED”. There is one more transformation which is very similar to this i.e UNION ALL. The Merge Transformation is similar to the union all transformations. Use the union all transformation instead of the merge transformation in the following situations. The transformation inputs are not sorted The combined output does not need to be sorted. The transformation has more than two inputs. MERGE takes ONLY TWO inputs where as UNION ALL can take more than two. Now lets see how to configure MERGE transformation with an example. I created TWO tables with names MergeA and MergeB and inserted few records into each table as shown below.
Read more