Very Slowly Changing Dimensions

– Traditional design patterns for Slowly Changing Dimensions (SCD) normally append extra attributes within the main table. This adds an overhead of maintaining these attributes. They are great when these attributes are few (like one for insert, delete or update), when the dimension itself is relatively small, and when the rows are relatively changing not[…]

Giving access to Run SSIS Packages

To give access for users to run SSIS packages, follow these steps: – From windows start menu search, find and access this file: Dcomcnfg.exe – Access > Console Root > Component Services > Computers > My Computer > DCOM Config > Microsoft SQL Server Integration Services (right click) > Properties > Security Tab > Launch[…]

ETL with T-SQL Merge Statement

I sometimes use ETL through pure SQL (no SSIS). I use the following. We have two parts: 1. First connect to the OLTP/ODS server from the DW/ETL server through OPENROWSET (I use this instead of OPENQUERY, since OPENQUERY requires Linked Servers). To enable OPENROWSET just enable Ad Hoc Distributed Queries, as follows: EXEC sp_configure ‘show advanced options’, 1; GO reconfigure; GO EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1[…]

Common SSIS ETL issues

Message: Cannot convert between unicode and non-unicode string data types: This happens when we want to insert unicode (such as MySQL varchar with latin_swedish collation) data into a non-unicode (such as . Or a non-unicode (such as MySQL varchar with utf8 collation) into a unicode field (such as SQL Server Nvarchar or Nchar). The problem[…]

SSIS ETL Conversion Issues

SSIS conversion from Unicode to Non-Unicode characters data set. I received this when trying to ETL from MySQL to SQL Server: varchar(100)-latin1_swedish_ci varchar varchar(80)-utf8_general_ci Nvarchar The value violated the integrity constraints for the column. [OLE DB Destination [334]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[date_ordered1] on OLE DB Destination.Inputs[OLE DB[…]

SSIS Project Permissions

After you deploy SSIS project, you can provide specific permissions for different users. Go to SSMS > Connect to Your BI Server > Integration Services Calalogs > SSISDB > Projects > Your Project > Properties: Then you will find this page. Click Browse to select the user then grant or deny this user from these specific permissions: Select, Modify,[…]

Fuzzy Logic in SQL and SSIS

SQL Server provides the capability of detecting similarity between strings for the purpose of data entry discrepancy or other tasks in mind. An on-demand capability which SSIS came up with: applying intelligent detection of string data entry discrepancy. There are two distinguished dataflow tasks for this: Fuzzy Grouping dataflow task allows detection of similar strings within the[…]

SSIS Version Control

If you published your SSIS project into an SSIS catalog more than once, and later wanted to roll back or forward to another deployed version, in SSIS 2012 this process has become very easy. Here is a nice article I read and applied successfully to accomplish this.