Download - SSIS ETL Packages
![Page 1: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/1.jpg)
SSIS ETL Packages
This demo contains:
1- Update table from excel source file
2- Updates only with the correct data (e.g. Prices must be numeric)
3- Check source file existance, update if it does, oetherwise send email to DBA (or of course to
anybody else)
4- Update the exisiting and insert the new records
5- Sample project:
ETL package to update daily currencies exchange rates from multiple source files:
a- Import the data
b- Pivot query for the report
c- Send the report via email
Our database for thid demo:
Update table from excel source file: We are going now to update the price in tblPriceList using SSIS Package for this purpose:
The Data Flow Task has Excel Source and OLE DB Command
![Page 2: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/2.jpg)
As this package is so simple I’m going to show the OLE DB Command only:
![Page 3: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/3.jpg)
Updates only with the correct data In tblPriceList price is int and doesn’t accept nulls
Assume the our source file has blank cells or non numeric values in price column:
![Page 4: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/4.jpg)
And we would like to update only those products that have numeric prices in the source file
Our Data Flow :
Derived Column (PriceIsNonNumeric):
![Page 5: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/5.jpg)
Conditional Split as follows:
![Page 6: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/6.jpg)
Send email to DBA (C# Script):
Result: Only 4 products were updated...
![Page 7: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/7.jpg)
And DBA got an email like below:
Check source file existance Before we run our package and to avoid getting unhandled errors we will first to check if the source
file is the source folder, otherwise the package will not be excuted and an email will be sent to DBA
informing the missing of the file
First we need two parameters to be used by the script checks the existance:
![Page 8: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/8.jpg)
C# Script:
Let’s try to run without source file:
The check was failed so DBA should receive an email..
![Page 9: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/9.jpg)
Update the exisiting and insert the new records In the database we have the records as follows:
But in the source file there are some new products we need to upload them (with their prices) to the
database:
We have to do the following steps:
1- Convert the sourc data to be the exactly the same type like in the db (Data Conversin)
2- Sort both the source and the destination (by ID asc)
3- Merge join them, join key will be ex_id (in excel) and id in the db
4- Conditional Split to check where the destination id is null (new row) and nou null (existing
record)
5- Basen on the split above we insert the new and update the existing records
![Page 10: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/10.jpg)
![Page 11: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/11.jpg)
Let’ go!
![Page 12: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/12.jpg)
We see that 7 existing rows have been updated and 2 new record were inserted into the database.
Let’s check if this is right:
We’re done!
We receive on daily base exchange rates from the banks, we upload them to the database from
various source files (these could be excel, flat, or even xml files).
After the successful upload we have to create the follwoing pivot table and report it via email:
Our Tables:
![Page 13: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/13.jpg)
Source data files:
Our Data Flow
![Page 14: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/14.jpg)
![Page 15: SSIS ETL Packages](https://reader033.vdocuments.us/reader033/viewer/2022051009/58a629c51a28ab416c8b5c1f/html5/thumbnails/15.jpg)