120 How do you implement SCD Type 2 in SSIS Using Lookup

[@shaikaniph4125]

Great explanation sir, I have got full clarity on fixed attribute, SCD type 1 and SCD type 2. We usually run all packages SCD type 1, i have got clarity on it now. This is very useful to develop type 2 records. Not only me many SSIS developers can follow the basic fundamentals of this package. Many thanks sir for speeding your time for us. On KZbin i observed two people who genuinely work for people, one is Ahmed sir for SSIS and second is kudvenkat sir for SQL server.

[@learnssis]

You are most welcome Shaik Aniph.

[@sudharshanvenkatesh3697]

Thank you so much for making this beautiful video, I will keep you in my prayers once again thank you much

[@learnssis]

Np, you are most welcome.

[@mohammadzaman7383]

Excellent! ❤👍! Thank you, Mr. Aqil

[@learnssis]

You are most welcome Sir.

[@chrisdang7378]

Best SCD video. Thank you.

[@learnssis]

Glad you liked the video.

[@reddyabhik]

best video ever I have seen on slowly changing dimensions. Straight and simple.! You are really doing great job, helping the Integrations development community. Can you please also tell the types of Keys that generally used in DW. And also what is the difference between BK or SK?

[@learnssis]

Thanks for your comment. For the type of keys I think I might need to make a video on this one as it is a wide topic. On the side of difference between BK and SK. SK is key which is used to uniquely identify a record in the table, normally it is an auto increment number in a table. BK identifies the record in the data ware table as well as in the transaction table. Thus BK can be used to join the transaction table with the DW table and we can get the respected data based on BK from 2 systems.

[@reddyabhik]

@@learnssis Thank you.

[@deepakj6717]

Best video on SCD implementation !! In Interview they asked me question, Can you pls tell me most common errors in SSIS you faced till now and how you resolved it.

[@learnssis]

The errors are vary from project to project. I got data truncation error, errors while inserting NULL value into a non null field, errors related to flat file layout change, errors related to excel file layout got changed, errors related to memory issues in data flow task, errors while sending email due to connectivity issue with smtp connection, errors due to disk full, these are the common types of errors and the resolution for each errors will be different, first you would need to understand what is the error and the resolution of each error will be specific to that error only. For example if the layout of a flat file got changed, then you would need to delete the existing flat file connection manager and re create a new flat file connection manager according to new layout.

[@NK-er3ci]

Hi, why are you pulling through the active row on the Type2 dimension please? Should you not be constraining on the transaction date to choose the correct surrogate key historically? Thanks

[@rubigupta8382]

Please, anyone, help me to find the script please guide me I want to practice but in goole drive, there are lots of which one is for this vidio i am confused

[@pparthan84]

For records that exists in Source and not in destination (new records) from lookup, shouldn't you be using "Lookup No match output" ? How will the new records from lookup pass through Lookup Match Output to Conditional split ?

[@learnssis]

yes you can use "Lookup No match output" as well, and this method which I shown here it will also work.

[@Rohan-ce1sy]

Good explanation. Very helpful. But how to create FameSell & FameSellDW databases ? (SQL queries to create the tables in FameSell & FameSellDW databases is not provided). Please help.

[@learnssis]

Hi, I have uploaded the 2 files to same folder on google drive. CreateFameSellTable.sql and CreateFameSellDWTables.sql you can use them to create the required tables and databases.

[@Rohan-ce1sy]

Thanks@@learnssis

[@franciso4650]

Good job! Which other ETL tool are you savvy in? Do you use informatica or do you have any tutorial video recommendations that's as detailed as yours for refreshing?

[@learnssis]

Other than SSIS, I have not used any other popular tool. I have used an ETL tool but that tool was created by a company and very few people were using that tool. I have not used informatica.

[@abhilashmishra499]

Just one doubt why we are including columns from reference table in our output in lookup

[@learnssis]

Because we are using those columns inside the conditional split transformation to check if a value of a field got changed or not.

[@teddyfabriciocordovasaenz3018]

I love u sir. Should I drop the staging tables when finishing the process? what is better for the performance

[@learnssis]

Yeah you can drop the staging tables if you want the database NOT TO have staging tables after the package is completed. Dropping and recreating tables does not take more than few milliseconds so this is actually not related to performance, so either you drop the tables or not it won't make any difference to performance.

[@AshokKumar-zc8om]

This is a brilliant attempt. Thanks for all of your efforts. I I have a question. If a record has changes on both SCD type 1 columns & SCD type 2 columns, will the record will be available in both the outputs. Please explain with an example.

[@learnssis]

You are most welcome Ashok, nice question, It will be available in SCD Type 2 and a new record will be inserted with changes made in SCD Type 1.

[@Jel.Awesh.M]

Thank you. Well done.

[@learnssis]

You are most welcome Sir.

[@jyotiraikwar5249]

Hi Aqil, Thanks for your videos on SSIS. I am learning from them very fast. but I have one question I am using SQL Server 2022 and visual studio 2022 version and my processor is i7 still it takes too much time to run SSIS package, can you please suggest what the reason behind it so that I can do more practice on it. Thanks in Advance🙏

[@learnssis]

You can try adding more RAM to machine.

[@govindasindhu3728]

Hi aqil, if we want to do scd 2 type using merge function and stored procedure can you please explain how we could do it.

[@learnssis]

I have created a video on incremental load using sql query, you can take a look at it and modify it accordingly for your case kzbin.info/www/bejne/Y6OwdaB6bKyBqqc

[@govindasindhu3728]

What does buissness key refer to in scd type does it mean foreign key?

[@learnssis]

yes foreign key, the key that is available in both tables.

[@easyyuz2707]

Hi Aqil. This is a great video as always. Can you please consider making a video to switch a fact table that has been maintained as scd1 till now and now the requirement is to convert it to scd2. What if we also need to set some default values for the previous records. How do we set up the first time historical run and incremental run?

[@learnssis]

If the table was used as scd1 and now they want to make it as scd2 then we need to modify or create a new package according to scd2. You can check the logic of scd2 in my other video "120 how do you implement SCD type 1 and type 2 in ssis using lookup transformation"

[@satishkanchukatla1774]

Great Video.. Can you make SCD TYPE 4

[@learnssis]

I have never used SCD Type 4, but will see how this can be implemented.

[@rohitnath5545]

We need a video on bk and sk

[@learnssis]

Business Key: A business key is a natural, meaningful attribute of a record that already exists in the source system or is relevant to the business domain. It's typically a piece of information that uniquely identifies a record based on its characteristics. For example, in a customer database, the email address or Social Security number could serve as a business key. Business keys have inherent business meaning and are often used to match records between the source system and the data warehouse. ------------------------ Surrogate Key: A surrogate key, on the other hand, is a unique identifier that is generated specifically for the purpose of identifying records within a database. Surrogate keys have no intrinsic business meaning and are often implemented as auto-incrementing integers or GUIDs (Globally Unique Identifiers). These keys are used as primary keys in data warehouse tables to ensure uniqueness and provide a stable reference for records.

[@gui250493]

Thank you.

[@learnssis]

You are most welcome.

[@darbardarbar9460]

Thank you sir

[@learnssis]

You are most welcome Parveen Darbar.

[@rubigupta8382]

Thank you

[@learnssis]

You are most welcome Rubi.

[@rohitnath5545]

Amazing

[@learnssis]

Thank you Rohit.

[@learner_SSIS]

Really good sir 👍

[@learnssis]

Thank you Naveen Raja.

[@praveenshetty7688]

Interview question: If you have 100 data source files and 100 destinations tables in this scenario how many ETL packages will you create?

[@learnssis]

It depends. If the layout of all source file is same then only one data flow task is required. Now if the layout of all csv file is different then we can write some C# code in Script task which can load data from all 100 CSV files into 100 destination table. And then rest of the code can be written dynamically as well to hander a particular table.

[@praveenshetty7688]

Sources have different layouts only..also panel expecting from me in how many packages we can achieve this?? Do you require single or more??

[@learnssis]

@@praveenshetty7688 If you just want to import 100 CSV files to 100 sql tables then this can be done using a single ssis package. You can check this video like how to do this. kzbin.info/www/bejne/jZqVn3Wmf75kl8k

[@khaledmahmoud8803]

Good

[@learnssis]

Thank you Khaled Mahmood.

[@samphoenix1623]

thank you

[@learnssis]

You are most welcome Sam.