SSIS: Logging in SSIS

What is Logging?

SSIS includes log provider, which can be used to implement logging in Package, container and tasks. With logging, one can capture run-time information about a package, helping audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished.

Also when SSIS log provider for SQL Server is enabled, then one table ‘sysssislog’ is created in the database. This table can be customized by adding some more columns like number of records transferred.

When you add the log to a package, you choose the log provider and the location of the log. 

Integration Services includes the following log providers:

• The Text File log provider, which writes log entries to ASCII text files in a comma-separated value (CSV) format. The default file name extension for this provider is .log.

• The SQL Server Profiler log provider, which writes traces that you can view using SQL Server Profiler. The default file name extension for this provider is .trc.

• The SQL Server log provider, which writes log entries to the sysssislog table in a SQL Server database.

• The Windows Event log provider, which writes entries to the Application log in the Windows Event log on the local computer.

• The XML File log provider, which writes log files to an XML file. The default file name extension for this provider is .xml.

How to implement Logging in Package 

This post will show how to configure logging through SQL Server log provider.

Step1: 

Create one SSIS package create some DFTs. Or we use any already available SSIS package. My package includes two DFTs SCD Type1 and SCD Type 2.

Step 2:

 Right click, and select Logging

Step 3:

a.    Select provider Type ‘SSIS log provider for SQL Server’ from dropdown, and click on ‘Add’ Button

b.    Check the checkbox and select ‘New Connection’ from configuration dropdown

c.    Create a new OleDB connection to the database in which you want ‘sysssislog’ table to be created.

d.    Then go details tab and select the events which you want to be logged during package execution. One can select events for package, container or some tasks also.

In the below snapshot, I selected events for package and two DFT’s SCD type 1 and SCD type 2. One can select various events like ’On error’,’On Post Validate’, ‘On Pre Validate’ etc.

e.    If we click on ‘Advanced’ button, then one can select the events along with the information which values will be recorded in sysssislog table.

After everything is done, click on Ok button. But this will not create sysssislog table in the database (for this post I have used adventure works) also the table will not be created if we only execute some task like DFT etc. The table sysssislog will only get created, if we execute the whole package.

 So when you execute the package you can check the sysssislog table in your database, which you configured.

We will get to see the below information in sysssislog table

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.

SSIS: Sysssislog

What is sysssislog?

It’s a table created in database. This table is automatically created, when we enable logging for SSIS package (SSIS log provider for SQL Server), and after execution of package first time.  For this we need to configure one database connection to a database in which this table will be created.

This table contains one row for each logging entry generated by SSIS package or their tasks during runtime. Each row entry depends contains event for which we have enabled logging such as OnPostValidate, PackageStart, OnPreExecute, OnPostExecute, PackageEnd etc.

By default, each row contain below columns. We can add more column as per our requirement.

id	event	computer	operator	source	sourceid	executionid	starttime	endtime	datacode	databytes	message
1	OnPostValidate		ragini.gupta	Package9			2013-06-13 14:20:10.000	2013-06-13 14:20:10.000	0	0x

How it is created?

Integration Services writes logging entries in this table only when packages use the SQL Server log provider.

Sysssislog table is not created just by executing the task, but we need to execute the package first time to create the table.

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.

SqlServer: return range of records

Problem:

How to get top 10 records then records from 11-20 then 21-30

The scenario is like, if the index from front end is given, then it should return records accordingly,

Index   rows

1              1-10

2              11-20

3              21-30

Solution:

declare @index int

set @index=2

;with cte

as

(select *, ROW_NUMBER() over(order by BusinessEntityId )as row_num from HumanResources.Employee

)

select * from cte where row_num>=(@index*10-9) and row_num<=(@index*10)

In the above solution, I have applied this on table ‘HumanResources.Employee’ of AdventureWorks2008 database. It returns records for index 2 thus returns records from 11-20

We can make this solution generic,

select *, ROW_NUMBER() over(order by [Column_NameForOrderBy] )as row_num from [Table_Name]

Please provide your feedback for the post, if you find this post useful.

SSIS: SCD Type 2, step by step

SCD Type 2, step by step

Type 2 (historical attribute): when we need to maintain the history of records, whenever some particular column value changes. 

By considering Type2, I assume that the employee gets promotion, Job Title changes. In such case we need to maintain the history of the employee, that with which designation or job Title he joined, and when his designation or Job Title changed.

  

For making such scenario, I have created two tables; one is tmpPerson, which is created as follows by using Adventure Works database’s tables:

---Start-TmpPerson table ---

create table tmpPerson (EmpId int, Title varchar(10) Null,FirstName varchar(20), MiddleName varchar(20),LastName varchar(20),

JobTitle varchar(100),BirthDate Date,MaritalStatus char(1),Gender char(1),NationalIDNumber varchar(20)

,CurrentFlag int , StartDate date,EndDate date default ('2050-12-31'))

insert into tmpPerson  (EmpId , Title ,FirstName , MiddleName ,LastName,

JobTitle ,BirthDate ,MaritalStatus ,Gender ,NationalIDNumber

,CurrentFlag, StartDate )

select e.BusinessEntityID,per.Title,per.FirstName,per.MiddleName,per.LastName,

e.JobTitle,e.BirthDate,e.MaritalStatus,e.Gender,e.NationalIDNumber,

e.CurrentFlag,e.ModifiedDate

from HumanResources.Employee e inner join Person.Person per on e.BusinessEntityID=per.BusinessEntityID

----End –TmpPerson table----

I have also created one table tmpPersonStage similar to tmpperson, which stores the records which are changed by changing any of the column value. For Type 2, I have made some changes in Job Title, as below:

---Start--tmpPersonStage table----

create table tmpPersonstage (EmpId int, Title varchar(10) Null,FirstName varchar(20), MiddleName varchar(20),LastName varchar(20),

JobTitle varchar(100),BirthDate Date,MaritalStatus char(1),Gender char(1),NationalIDNumber varchar(20)

,CurrentFlag int , StartDate date,EndDate date default ('2050-12-31'))

insert into tmpPersonstage  (EmpId , Title ,FirstName , MiddleName ,LastName,

JobTitle ,BirthDate ,MaritalStatus ,Gender ,NationalIDNumber

,CurrentFlag, StartDate,EndDate )

select EmpId , Title ,FirstName , MiddleName ,LastName,

JobTitle ,BirthDate ,MaritalStatus ,Gender ,NationalIDNumber

,CurrentFlag, StartDate,EndDate

from dbo.tmpperson where empid in (3,6,8,58,49)

----changes for type 2

 update tmpPersonstage set JobTitle ='Design Engineer' where EmpId=3

 update tmpPersonstage set JobTitle ='Production Technician - WC10' where EmpId=6

 update tmpPersonstage set JobTitle ='Production Technician - WC50' where EmpId=8

 update tmpPersonstage set JobTitle ='Research and Development Engineer' where EmpId=49

 update tmpPersonstage set JobTitle ='Engineering Manager' , LastName='Keill' where EmpId=58

 ---End---tmpPersonstage table -------

In the above script, I have updated the JobTitle of some employees, for which initially the job titles in tmpPerson table were as below

 After preparing the initial steps for the scenario, let’s move to SSIS package to apply SCD transformation. The below steps are the implementation of SCD Type 2

Step 1:

 Drag and drop one Data Flow Task in the Control flow tabs.

And name this DFT as ‘scd type 2’

Step2:

 

Go to Data Flow tab, Drag and drop OleDB Source and Slowly Changing Dimension transformation from Data flow transformations.

Step 3:

Configure OleDB source, here the source will be the table which has changed or new records, and for which history will be maintained, or inserted in the main table. In our scenario the source table is ‘tmpPersonStage’, which keeps some updated and new records for updated records history will be maintained and new records will be inserted into the main table ‘tmpPerson’

 

 Step 4: 

Configure the SCD transformation; double click on SCD transformation, one wizard will open as below:

Click on next and create either new connection or already created connection to AdventureWorks2008, and while mapping the input columns to dimension column, at least one column need to be mentioned as ‘Business Key’.

 Business key is the column on the basis of which the updation or insertion into ‘tmpPerson’ will be made. Here we have mentioned EMPID as BusinessKey, so if EmpId of input table ‘tmpPersonStage’ is found in ‘tmpPerson’ table then the respective row  in ‘tmpPerson’ will be updated with the changes  from ‘tmpPersonStage’ input table 

Click on next. In the below window, change type need to be mentioned on certain column. Change Type can be ‘Fixed attribute’, ‘Changing attribute’ and ‘Historical Attribute’

As in this scenario is based on Historical attribute we will select some columns for which history will be maintained if found modified and new record will be inserted.

 Go to Next and select one of the below option, 

As in ‘tmpPerson’, we have both the option available one through ‘CurrentFlag’ column and another through ‘Startdate’ and ‘EndDate’.

We can opt for first option and use column ‘CurrentFlag’ as the indicator of the current record. By this the updated record will be inserted with ‘CurrentFlag’ value as ‘1’ and the records which became old for the respective ‘EmpID’ the ‘CurrentFlag’ will be set to ‘0’.

And if we opt for second option then we need to update the old record’s EndDate with ‘GetDate ()’ and need to insert the updated record with ‘StartDate’ as ‘GetDate ()’. For new records, we need to use StartDate as ‘GetDate ()’ .

Through this post, I am selecting first option, and I will be using ‘CurrentFlag’ Column to show the current record.

Go to next and don't select  ‘Enable inferred member support’, as we don’t require it for this scenario.

 

Click next, which displays New Records, other Outputs(Historical Attribute Output), which means there will be two arrows from SCD, one will take the new records, another will take the records for which history needs to be maintained.

Click next and Finish the wizard. As we finish the wizard we see some transformation like ‘OleDb command’ and ‘Insert Destination’ are automatically created.

 Oledb command will update the ‘CurrentFlag’ column of old records and will set it to ‘0’ for the ‘EmpID’ for which records are updated. These all will be union all with all new records along with the new updated records having ‘CurrentFlag’ 1 and will be inserted into the main table ‘tmpPerson’

Step 5:

Step 5.a:

Double click on Derived Column, and we see that one column with ‘Replace Currentflag’ has been derived from current flag and the value is set to ‘0’.

Step 5.b:

Double click on Oledb command, and it will be configured automatically as below. The connection will be automatically configured to table ‘tmpPerson’ of Adventure works 2008, in which the records will be updated.

Also when clicked on Component Properties tab then the update command is also configure, which we can see on string value editor.

The update command updates Current Flag to 0 on the basis of EmpId , and Current Flag(value 1)

In column mapping tab the parameter are mapped with the columns mentioned in query. These Parameters contain the Replace Current Flag column from derived transformation and EmpID from SCD

Step 5.c:

When clicked on next Derived Column transformation, the same Current Flag column is derived column of Replace Current flag, and set it to 1, for new and updated records.

Step 5.d:

Insert Destination will also be configured automatically, to insert the new records coming from SCD to tmpPerson table. 

 

Step 7:
Execute the DFT 

 

 After execution, we can see that, we updated 5 records in the tmppersonstage table, which are inserted into tmpperson table, along with the old records current flag updated as 0. As there was no new records, so no row is inserted into tmpperson.

 

Click For implementation of SCD type1 step by step
for alternate mehods of SCD
For more information on SCD 

Please provide your feedback for the post, if you find this post useful.