128 How to load dimension table in sql using ssis

[@JoseMorales-ws6ro]

I want to thank you how you nicely explained the dimension load, I just finished to use it in a project at work. You saved me a lot of time!

[@learnssis]

Thank you Jose.

[@catulopsae]

Well explained. It’s awesome how easy you make everything. Thank you

[@learnssis]

You are most welcome catusae, its good to know that you found the tutorial helpful.

[@PradeepAnnavarapu-l7b]

Thank you so much for a very useful video

[@AlnebrasMurtada]

Dear Ahmed, I hope you are fine, I implemented the same steps but every time all updated records were redirected to the new records component which is the ID even if I changed a column related to SCD Type1 or SCD Type2. can I share with you through email a screenshot of the flow data and how it's gone? Thanks

[@learnssis]

Please watch the video again and carefully and you would need to select the Id from your destination table and select that column from Lookup component and later in Conditional Split transformation you would need to check if that Id is NULL or not, for the non exists records or new records it will be NULL and for existing records it should not be NULL, thus we will insert a record if this ID is NULL. You can try to put the data viewer between lookup and conditional split and check the value of that record. Try to test it for single record.

[@AlnebrasMurtada]

@@learnssis Solved! Many thanks 🙏 But after the change Business Key "EmployeeID" in DimTable to be not a primary key, because on SCD Type2 new record will be inserted with the same EmployeeID, in this case, duplicated primary key error will occur

[@lakshmanachandana9099]

Hi Aqil ... can u plz make videos on complete Data warehouse project..thank you

[@learnssis]

Yeah next video is coming on "how to load data to Fact table". So if you are able to insert data to Dimension table and Fact table then you should be good for the Data warehouse project because in Data Warehouse project we load the data to Dim and Fact tables.

[@DeepaKumari-v5y]

very well explained,thanks!

[@learnssis]

Thank you Deepa Kumari.

[@chandraprakashnaidu9009]

Thank you aqil for making the video

[@learnssis]

You are most welcome Chandra Prakash Naidu.

[@haisimyasin5877]

Hey Aqil, I am getting error when i am using ur create dimension sql command . The error says ,"The specified schema name "Dimension" either does not exist or you do not have permission to use it".. Plz help me.

[@learnssis]

Can you check if you have the Dimension schema in the database ? OR try to run this on the database create schema Dimension

[@haisimyasin5877]

@@learnssis thank u so much man . Problem solved.

[@learnssis]

@@haisimyasin5877 Glad you get it.

[@7to7media]

please make video on unit test

[@learnssis]

I had created an initial video on the unit test here kzbin.info/www/bejne/aKrblIRja6-YbbM I will try to make another video on it.

[@mmusciano]

Hi Aqil, I got this error in the Conditional split [Conditional Split [2]] Error: The expression "LogonName != Dest_LogonName || Email != Dest_Email" on "Conditional Split.Outputs[SCD Type 1]" evaluated to NULL, but the "Conditional Split" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.. Can you tell me what corrections I can make please? Thank you

[@learnssis]

It means there is a NULL value in either LogonName or in Dest_LogonName or in Email or in Dest_Email field. Try to replace the NULL with a blank string before using the conditional split transformation. Either you can do that while selecting the data from the source and destination table or just replace the NULL value with a blank string in the sql server table itself.

[@mmusciano]

@@learnssis Thank you Aqil

[@learnssis]

@@mmusciano You are most welcome.

[@habrom1000]

Hi Mr. thank you for tutorial. I couldn't find the sample data for Order, Product,Supplier in the google drive you attached. I get only the sample Customer and Employee. Could u plz load the data. Thanks.

[@learnssis]

In this video I have only shown how to load data into Employee Dimension table thus I only uploaded the data for Employee table. I am going to make a video on "how to load Fact table" and for that video we would need to use the other Dimension tables as well because in Fact table we use the Surrogate keys from Dimension tables. I will upload the data for other tables in a separate folder tomorrow along with the requirements for loading the data into Fact table.

[@habrom1000]

is it available in Adventureworks or other open database

[@habrom1000]

@@learnssis onces again thank you for your quick reply.

[@learnssis]

@@habrom1000 No problem.

[@AlnebrasMurtada]

Hi Ahmed Thanks for great video. How can I load delimited comma file with more than one column, and each column contains more than one comma? Like address column in this video. Thanks.

[@learnssis]

In the delimited file if column values are surrounded by double quotes then while configuring the flat file connection manager you can easily get the values by providing double quotes as text qualifier and it should work. But if in your csv file there are no double quotes then it will be really hard to get the proper column values, then you would need to write some kind of parser in C# to get the column values correctly and it won't be that easy. One of the very easy case is below. kzbin.info/www/bejne/lZTLcmawpbZlr5Y

[@AlnebrasMurtada]

@@learnssis I wrote C# code to handle this case, and it works for me well, I will share it with you in your email.

[@learnssis]

@@AlnebrasMurtadaPlease do.

[@AlnebrasMurtada]

@@learnssis sent to you last two days

[@alimirazimzadeh544]

I learned a lot. Thanks

[@learnssis]

That's great to know.

[@NebroProg]

Hi Ahmed, I hope you are well, Very useful video. What is different if I use this method or use the SCD SSIS component? thank you.

[@learnssis]

Thanks Nebro for your comment. This method is faster than the SCD SSIS component because in SCD SSIS component to update the records we use OLE DB Command component which updates one record at a time. Thus to update 1000 records OLE DB Command needs to run 1000 queries one by one. While in this method we use single update query to update 1000 records and which updates all records together using the set based based which is a lot faster than OLE DB Command method.

[@NebroProg]

@@learnssis A lot of thanks Ahmed, 🙌 I hope God will reward you with all the best

[@learnssis]

​@@NebroProg​Thank you Brother, God bless you too.

[@anandreddy9591]

Sir could you please create a video what is inferred member how it will be helpful for dimension/factloading

[@learnssis]

Okay will try to cover it in future videos.

[@srinivasarao-gr8pv]

Hi Aqil, Nice video and nice explanation 👍 Can you create a video on how to improve the performance of SSIS package

[@learnssis]

Thanks, sure will try to make a video on this one as well. I have created one video on performance tuning of SSIS packages, you can take a look if you have already not seen it kzbin.info/www/bejne/aaHElKuNmrmWm5Y

[@srinivasarao-gr8pv]

@@learnssis I seen already good, Even though discuss more techniques on performance tuning

[@learnssis]

@@srinivasarao-gr8pv Yeah will try to cover more topics in next performance tuning video.

[@habtamuayalew-q3z]

Hi Aqil, Thanks for all the contents you share in your channel. I really like this scd load but it fails because of null . i tried it with d/t dataset (good) but still getting ([Conditional Split [2]] Error: The expression "LogonName != Dest_LogonName || Email != Dest_Email" on "Conditional Split.Outputs[scd1]" evaluated to NULL, but the "Conditional Split" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.

[@learnssis]

It seems like there are NULL values in either of the fields, please replace the NULL values while fetching the data from source and from lookup transformation. You can use Isnull function in sql to replace NULL with a blank string.

[@habtamuayalew-q3z]

@@learnssis Thanks for the response. i don't have null value in my good dataset. validto field suppose to be null to show the active record.

[@learnssis]

@@habtamuayalew-q3zThen you don't need to compare on ValidTo field in the conditional split transformation.

[@deepakj6717]

Hi Aqil, Best implementation.. Have you implemented this in your project? How was the performance of this in project? Instead of this what if we use OLEDB command to update records and instead of staging table what if we use direct dimension-fact tables? Which will perform better between this two methods?

[@learnssis]

Yeah I used this approach in one of my project. Of course this method is the best method to load the data to Dim tables as compare to Slowly changing dimension method or OLE DB Command method or Merge Join transformation method.

[@prajaakeeyaparty6578]

HI Aqil...Can you make video on bridge table, why and when to use bridge tables

[@learnssis]

Okay, will try to make a video on it, but it might be late as next video will be on Fact table and after that there are bunch of other videos as well.

[@prajaakeeyaparty6578]

@@learnssis ok, thanks Aqil

[@arimbakammeghavardhan6424]

Hi bro, in ur total experience , u feel this is the package very complex one i developed. Pl share description we will try to develope that package pl......

[@learnssis]

The most complex packages those I developed those are at the company servers and I won't be able to show that but I can try to make a complex package in coming days and can explain it to you guys.

[@arimbakammeghavardhan6424]

@@learnssis Thanks No need to show package, just give description we are develope that package to process test data

[@learnssis]

@@arimbakammeghavardhan6424 Sure will make a video on such package soon.