Scenario:
Let’s say that 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
Create variable in SSIS Package to capture Inserted Row Count |
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
Save the Row Count in variable by Row Count Transformation in SSIS Package |
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
Use Multicast as Test Destination to see the Record Count in SSIS Package |
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
How to insert variable value to SQL Server Table by using Execute SQL Task in SSIS Package |
Map the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below
How to map parameters in Execute SQL Task to variables in SSIS Package for Insert Query |
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.
![]() |
Get Insert Row Count in SSIS Package and Log to Table |
Video Demo : How to get Record Count in SSIS Package and Save in SQL Server Table
I think there is a need to look for some more information about SSIS and some very complex tools and hence it is the most useful aspect here.
ReplyDeleteSSIS Upsert
This has been very helpful. Thank you.
ReplyDeleteAivivu đại lý vé máy bay, tham khảo
ReplyDeleteve may bay di my gia re
ve may bay từ mỹ về việt nam hãng eva
khi nào có chuyến bay từ đức về việt nam
vé máy bay từ nga về việt nam bao nhiêu
giá thuê máy bay từ anh về việt nam
chuyến bay từ Paris về Hà Nội
khách sạn cách ly
The Khajane 2 of Karnataka state government has over 216 treasuries in one single portal. All the treasures are under one single server which similarly used by the secretariat office of state. As well the monetary details of other applications are all updated from the one single Khajane 2 portal. So through this one portal the financial transactions of the state are all verified in a quick manner. K2 Login Hence the salary slip generation and disbursement of salary has carried out in Khajane 2. It makes governance easier to check how much amount spent on salaries. Also in similar manner, other funds toward projects and similar kinds of services have managed under Khajane 2.
ReplyDelete