Creating SSIS and SSAS project
BI- (Business Intelligent) converting from Data into Information
Below sample project will see how we can create simple SSIS and SSAS project
You can refer this blog https://amitpnk.github.io/MSBI-Project/
For feedback can drop mail to my email address amit.naik8103@gmail.com or you can create issue
Sample SSIS project which will see Data flow and control flow in Lab1.dtsx
Data flow - ETL activities
Control flow - Non-ETL activities
Step 1 - Run SQL scripts which is available in Miscellaneous
Step 2 - Drag DataFlow task
Step 3 - Inside this add Flat file source (to read from CSV file) -> Derived column (string manipulation) -> ADO NET Destination (insert into DB)
In Lab2.dtsx will see how we can create conditional split, data conversion & error handling
Step 1 - Flat file source (to read from CSV file) -> Derivied column (string manipulation) -> Data conversion (to convert into number) -> Conditional split (Based on forumla we can insert into DB or flat file destination)
Step 2 - In case of error, we can redirect into log file (using flat file destination)
In Lab3.dtsx will see how we can extract dynamically with multiple csv files using for loop and variables/parameters
Step 1 - From previous Lab drag For loop container and inside this add Data flow task
Step 2 - Open package variales and add variable FullFilePath
Step 3 - Right click on Foreach loop container and go to Edit -> Collection -> Enumerator
- Modify ForEach file enumator
- Modify Folder path source
- Modify *.txt
- Modify Fully qualified
Step 4 - Go to Variable Mapping
- Add variable User::FullFilePath
Step 5 - Go to properties of Flat file connection manager (csv connection) -> Expression -> add connectionString to @User:FullFilePath
Step 6 - Incase of parameter : go to Parameter tab -> setValue and right click on ForEach loop container -> Expression -> Directory - $Package:ParamFilePath
There is mainly 2 category of tables
Eg: Sales Amount as per country, year and product
here
Sales Amount is Measures and Country is Dimensions
Star schema - Fact is central table with foreign key relationship with dimension Snow flake - Same as Star schema and also dimension table also connected
Step 1 - Create new SSIS package as Country.dtsx, States.dtsx, SalesPerson.dtsx, Product.dtsx and in DataFlow task create FlatFileSource-> ADO NET Destination in each file w.r.t country, states, salesPersion & product csv file
Step 2 - Create Global connection string and map to each file instead of creating individual file
Step 3 - Create Main.dtsx and add control ExectuePackageTask and map to each dtsx file mentioned in step 1