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: -
For loop container loops a constant value.For Loop is a NON-ETL activities and that code has to be done in control flow. For loop container loop till a fixed count.
For Loop container groups set of tasks together and allows you to looping through the tasks in your package which is similar like for loop in any programming language. In case until a fixed count than you will use the “For Loop container”.
Drag and drop For Loop container from tool box on to Control flow design editor as shown in
Double click on For Loop container task which takes us to configuration editor as shown in
Configuration editor has only two options For Loop and Expressions.
InitExpression: This will initialize the loop with specified values
EvalExpression: specifies an expression to stop the loop when the expression evaluates to false. This expression must evaluate to a Boolean.
AssignExpression: specifies an expression that changes a condition in the same way each time the loop iterates.Which is optional one.
Drag and drop “For loop” container and configure as shown below
When we use For Loop Container task.
For Loop Container Task: When we know the exact how many times we have repeat given data flow for execution.
Difference between For Loop and Foreach Loop Container?
A For Loop container loops till a constant value. So for example if you want to loop until a value is 12 or less than 12 so for upto fixed count you have to use For Loop container.
Whereas Foreach Loop container loops through collection so this collection can files in a folder or collection can be in record set.
Foreach loop container actually loop through a collection. Now this collection row in a record set files in a folder.
For Each Loop Container Task: When there is dependency of number of time task should execute on data such as files, variables we will use this.The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.
Create a source folder (I have created Folder Named Customer) containing some text files and create Destination Folder where you want to copy these files.
First, we need to create an ETL process for a single file. So, create an ETL process for single file.
After that ETL Process is completed now to make it easy we will save both File to a folder Called “Bulkfile”.
In this step on “Control Flow” tab we will create a variable why because if you see on “Data Flow” tab pue source in Flat File Connection we have physical file name is “CostomerSales.txt” which is one single file name. Weworking with multiple files. We will create a variablein “CONTROL FLOW” tab and pass that full file name through this variable name to “DATA FLOW” using “Foreach loop”.
It is temporary stores. We go and store some value.
Create a variable.
In order to create a variable go to Control Flow Right Click Add Variable as Shown in below screen shot.
Add variable and give the name and then we are passing file name so our variable data type should be “String” as shown in below screen shot..
We will configure FOREACH LOOP container. Select container and right click -->edit.
From left-hand side menu select collection and from enumerator select "FOREACH FILE Enumerator".
Go to below in the folder section folder path "rahulxadfg" which we have created earlier and below that give extension as ".txt" since our data is in text format as shown in below screen shot…
Select Variable mapping from left-hand side and choose variable name which we have created earlier i.e. "VarFullFilePath" as shown in below screen shot…
Everytime "FOREACH FILE Enumerator" loops and get a file it will assign that file path to this variable "VarFullFilePath".
After that we configure the Data Flowtable at source. Where file name is needed for ETL process.
So go to Data Flow tab and Connection Manage right clicks on CSV Connection Manager Properties.
In the properties window if you see current "ConnectionString" it consist of single file path that we want to make it dynamic by assigning "VarFullFilePath".
The same property window there "Expression" menu -> configure -> click. In the property select "ConnectionString" and click on expression select variable "VarFullFilePath" and drag it to expression and click on OK as shown in below image.
Run this project to see the outputbelow is the screen shot on Control Flow.
And here is the screenshot from Data Flow.
Hope that practical of looping in SSIS is understood.
Do not forget to see following one such video from project series of MSBI(Microsoft Business Intelligence) for newbie’s: -
Variable is more internal to SSIS. Variable is used to pass the variable from control flowto data flow.
They help to pass data from control flow to data flow.
They are passed from outside and help during deployment in production server.
They are just written with simple name as given.
They are denoted with $ symbol by the name.
They are more internal to SSIS application to pass data.
They are more internal to SSIS application to pass data.
Using Parameters with SSIS deployment
Parameter is more external these are passing from outside.
Parameters allow you to assign values to properties within packages at the time of package execution.
Now we will create a parameter for destination path. So we will go back to project and add parameter as shown in below screen shot…
Click on parameter tab and add new parameter for destination path as shown in below image.
If you see we have created a string type parameter called "BulkFilePath" to set destination value which we have already set in the value.
We will configure this parameter to destination file connection in the connection manager. Right click on that “CVS Connection Manager” and go to property à choose expression.
We need to specify a property as ConnectionString because we want to parameterize our connection and then choose expression as shown in following screen shot….
Expression:- it is help set parameter and variable name values to the properties. To click on the Directory and set a parameter ($Package for parameter which is represent by $) and “VarFullFilePath” is variable.
helps you set variable of properties of SSIS component from variable and parameter”.
Right click on that “ Foreach Loop Container” and go to “Edit” as shown in following screen shot….
The parameter value will be set during deployment in production.
Hope that variables and parameters used within SSIS is understood.
Below see one video from project series of MSBI (Microsoft Business Intelligence)-