Problem is that if I go and run the project there is exception.
Under “Main.dtsx” design, there is error in the Country master and go to the “country.dtsx” design to check the Error. You can see RED cross mark on top right of the component. In the RED sign some error message here. It sees the following screen shot
Message says that, I can’t insert the duplicate key in the “Country” table. Because the “Country Table” as well as ProductTable, SalesPersonTable and StateTable all have a primary key. Re-run the package you try the re-load the data inside the table. While using SQL tables in MSBI duplicate data can occur for analysis or business sake so primary key can be disabled.
We need to go to the SSIS Toolbox➝ common➝ Data Conversion. Drag and drop the “Data Conversion” into the “Data flow” and Edit the “Data Conversion” as following screen shot…
We have a component in SSIS called as “Slowly Changing Dimension”. Slowly Changing Dimension is Available in the data flow task. Go to the SSIS Toolbox➝ Common➝ Slowly Changing Dimension.
Double Click on SCD will initiate Slowly Changing Dimension Wizard
Slowly Changing Dimension will right, all the logic as well as go and connect the SQL Server.
Drag and drop the Slowly Changing Dimension in Data flow and right click in the Slowly Changing Dimension say “Edit”. Click on the edit its start wizard and wizard helps us to configure Slowly Changing Dimension component. It sees following Screen shot…
When we click on the “New” then pop up new windows to configure the “Connection Manager”. It sees the following screen shot…
Select the “Input Columns” which is defined into the “Data Conversion”. Defined the “Dimension Columns” and “Key Type” is “Business key”. Business key is the primary kind of the think and then next. See the following screen shot..
Now, the Slowly Changing Dimension Wizard gives you three kinds of option.
Fixed Attribute ➝ Fixed attribute changing indicate the column value must not change. For which value is not change.
Changing Attribute ➝ Changing attribute changes overwrite records. This kind of change is equivalent to a Type 1 change.
Historical attribute ➝ Historical attribute changes create new records instead of updating existing ones. It is also called Type 2 change. In Type 2 change is maintain.
You can check or uncheck as per your requirement, I am un-checking these two.
Also un-check the “Enable Inferred Members Support”.
Click to “Next” and finish.
With the removal of primary now working with SCD component will be absolutely fine.
Below is “Learn MSBI in 32 hrs” series project starter video to learn more topic: -