129 How do you load a fact table

[@reddyabhik]

very well explained with examples. really very impressed with this practical example on how to load Fact Load Aqil!

[@learnssis]

Thank you Abhilash.

[@catulopsae]

Awesome job. I love how well you make your videos. Thank you for the great job. I have learned so much from you. You are the best in youtube

[@learnssis]

ha ha. Thanks for your comments. Good to know that you liked the video.

[@anandreddy9591]

Excellent videos now much cleared about dimension and fact loading thanks for the great videos ssis

[@learnssis]

You are most welcome Anand Reddy.

[@catulopsae]

I have a question. The output when writing to a destination table, does it need to be in the same order as that table or it’s ok to just match by name column? I have had issues on the destination and the data didn’t load properly

[@learnssis]

The order is not important, we just need to map the input columns with destination columns.

[@dineshvishe3]

nice well explain.. I was searching long time. now got it..

[@learnssis]

Okay great.

[@luckyrantho2344]

Hi Aqil, Thanks for the information very helpful, my question: After i run the package the data is inserted into FAct.Orders table but 2 columns ( CustomerSk and ProductSk) are both NULL except EmployeeSK

[@Eldo_thomasbaby]

Awesome Job Brother, Thank a lot for sharing the script

[@learnssis]

You are most welcome Brother.

[@PAVANKUMARDHODDA]

Hi Akhil, Your SQL Video series are outstanding, kindly make a video on Performence tuning on SQL Server.

[@Tech2BI]

Nice explanation 👏👏 some times I feel sproc much easier when I have more than 50+ columns

[@learnssis]

Yes you are correct, its always easy to update the code in sp instead of modifying the SSIS package.

[@luckyrantho2344]

Hi Aqil, thanks for the video. When creating the tables i get this error 'The specified schema name "Fact" either does not exist or you do not have permission to use it.' from SSMS,how can i change the permision

[@learnssis]

execute this query on the database create schema Fact

[@luckyrantho2344]

@@learnssis Thank you so much

[@rukshanmanchanayake9025]

Is it possible to trasfer data directly from [dbo].orders to [Fact].orders as both are having same columns???

[@learnssis]

Yes, you can do that.

[@msatheesh4495]

Thank you for kind explanation

[@learnssis]

You are most welcome.

[@msatheesh4495]

@learnssis if possible could you please create Azure data factory sessions as well. Thank you in advance..

[@learnssis]

@@msatheesh4495 At the moment, I am busy with some projects, will try to make in future when got a chance.

[@msatheesh4495]

@@learnssis thank you..

[@bahaeddinebousmina9229]

Hello Aqil, Great job. But I have a question, I still don't understand why we need to insert a surrogate key with a zero value into the dimension tables? can you please explain it more?

[@learnssis]

If in your Fact Table you have foreign key on the SurrogateKey For Dim table, then it will only allow the values into the SurrogateKey those values already exists in the Dim table. Thus if a match not found for a record in the Dim table then it will try to insert a value 0 into the Fact table for the SurrogateKey column and because there is a Foreign key on that column thus the insert will fail, if you don't have foreign keys created in the Fact table then you don't need to insert the value 0 to the Dim table.

[@sccc-l3g]

Thank you for your good explanation If the dimensions are loaded first , then the fact table, Is it necessary to insert zero sk to the dimensions?

[@learnssis]

If we won't insert the zero sk then we might miss some records to be inserted to fact table as there won't be any match for those records.

[@sccc-l3g]

My answer is: For example, loading the fact table every 1 hour and dimensions every 1 week So we need to insert zero sk But in addition price changes and quantity changes we need to consider sk changes To update zero value sk I think the conditional split can have another condition to check for sk changes thanks

[@haisimyasin5877]

hey Aqil, I have a question. If we want to create dimensions and fact using ssis in a same project do we create different packages or we can do it in one. I am new to it and currently working on the project where I am loading data into dimensions and facts . I have covered your "Loading data into dimension table employee" video. Please guide me and can u please provide the scripts to load data into other dimension tables.

[@learnssis]

Hi, its upto you if you want to write code for all Dim in a single package or want to create multiple packages. If you have written the code to load data into Dimension employee table then same logic will be used for all other tables as well, you can write the code accordingly for other tables as well.

[@haisimyasin5877]

@@learnssis thank you so much. If we want to create all dimensions and facts in a same package, will we use sequence container to contain every dimension and fact?

[@learnssis]

@@haisimyasin5877 Yeah its better to create separate sequence container for every Dim and Fact table, because it can be easy for you to disable them if required.

[@ДавидМирзоян-ы5ь]

HI Aqil. Thenks for such helpful videos. Just one question. What if something is deleted from Source table and should be cleaned in Fact table? How should we handle the situation?

[@learnssis]

I have made a video on how to handle deleted records in incremental load, you can take a look at that and can try to implement the same way. kzbin.info/www/bejne/mIvEmq2Bq7d0btE

[@ДавидМирзоян-ы5ь]

​@@learnssis Thank you!! I'll try to do it right ))

[@easyyuz2707]

Hi Aqil, Thanks a lot. This video is extremely helpful. I am not clear why we updated SK columns at the end for the matches records, but not just the columns we included in BSM calculations. Because we only split the data based on that condition and if there was any change in those? In real life time scenarios, don't we expect if any of those key, SK columns change, it should come with a new OrderId? So, my question is, why are we updating the dimension key columns also? My other question is, what if the fact table is also SCD2 and if we want to insert a new record instead of updating the existing ones, how could we handle that scenario? Is it similar to SCD2 Dimension load video you made where we need to create 1 fact. order_insert and 1 fact. order_update tables to stage that data? Thanks in advence!

[@learnssis]

Hi, yes for your question one instead of updating the SK columns if there is a change then we can insert new records. I think was required in our project thus I did way. If the Fact table is also SCD2 then yes you can handle is same way how you can handled the Dim table for SCD2. Thanks.

[@Knowledgegreenone]

Need one help can u create a stored procedure to save the information of file in database like the scenario is we have one request I'd on that basis we have to fetch information like request I'd location file name download percentage u can create a table and have those columns included and fetch the information from that table through stored procedure

[@learnssis]

In below video I have shown how to import a CSV file into a MySQL table kzbin.info/www/bejne/mYikaoKJqrGDq6s And I have already created the video on "how to create stored procedure", thus I think you should be able to do it.

[@Knowledgegreenone]

@@learnssis through query I am saying based on the conditions I provided.

[@badrnadi6233]

Why did you use Conditional Split?

[@learnssis]

I used conditional split transformation to split the data into 2 outputs based on the condition that I have written in conditional split transformation.

[@mohammadzaman7383]

Question: set @cnt = (select count(*) from [Dimension].[Date] where DateKey=19000101) if(@cnt=0) begin delete from [Dimension].[Date] where DateKey=0 end Note: Should not it be : delete from [Dimension].[Date] where DateKey=19000101

[@learnssis]

I think you are right, at the moment I am out of station and will check it once I will be back.

[@aasim1104]

I didn’t understood the purpose of inserting 0 to dim table.

[@learnssis]

If in your Fact Table you have foreign key on the SurrogateKey For Dim table, then it will only allow the values into the SurrogateKey those values already exists in the Dim table. Thus if a match not found for a record in the Dim table then it will try to insert a value 0 into the Fact table for the SurrogateKey column and because there is a Foreign key on that column thus the insert will fail, if you don't have foreign keys created in the Fact table then you don't need to insert the value 0 to the Dim table.

[@haisimyasin5877]

@@learnssis no new record cannot be added in fact table if we dont load 0 in dimension table? By unmatched record we mean the newly inserted record in table ? Also can u please explain why you used date dimension i am confused about it? Thankk you

[@Confort-Kid-Autism]

Un grand merci. Tout est bien expliqué.

[@learnssis]

Merci beaucoup pour ton soutien.

[@learner_SSIS]

Hi sir How to connect to a SFTP server via an SSIS script task package

[@learnssis]

Hi Naveen, actually at the moment I don't have access to any FTP or SFTP server so it will be hard for me to test it. I will see if any public FTP or SFTP server is available so that I can test it.

[@learner_SSIS]

Ok sir but I use SFTP server sir How to use the WinSCP .NET assembly inside an SSIS Scripting Task, as this is what WinSCP also recommends as the way to achieve SFTP using WinSCP in SSIS.