Loading a Data Warehouse with SSIS with Robert Biddle

[@solsh6467]

It is great video . Describing enough all by itself

[@robertbiddle7789]

Hi all. The solution, scripts, and slide deck can be found on SQL Saturday site for this session that I've done in Orlando: www.sqlsaturday.com/318/Sessions/Details.aspx?sid=23151

[@TAIMIUWU]

Great presentation and very clear video. please can get the code or script for execute sql task for "Get the Modified date". Thanks in advance

[@robertbiddle7789]

@@TAIMIUWU select isnull(LastSuccessfulModifiedDate, '1900-01-01') as LastSuccessfulModifiedDate, getdate() as CurrentDate from Meta.DataFlow where DataFlowName = 'Dim_Customer'

[@vochau9806]

Hello, the site is out. Can you give me this solution as today I first watch it. Thank you

[@MatheusPavanetti]

That's awesome !

[@fatiflowe]

Hello this was great , But I'm a little bit Confused on why you didn't use the Dim_Date as a package in ur ssis Project .while creating the Fact table shouldn't we do a look up by the date dimension ?!

[@ericsos101]

great video! so how did you get the source data?

[@solsh6467]

Where can I get those spread sheets you have used for mapping

[@MrDavisv]

This was great but I'm confused on how your fact records are updated. In your sales package it's just an insert, correct?

[@robertbiddle7789]

Hi Davis, this is a good question and a common one. Depending on your source data would tell how you should handle Fact table updates and deletes. The pattern I typically recommend for this is to load your changes to a table and then apply a T-SQL Merge stored procedure. You could alternatively also use an update and insert statement. For some systems, the transaction records are write-once, meaning that to update the data, you have to back out the transaction (or negate it). In these cases, this would mean you would only insert to the Fact table. In many environments, deleting from Fact tables is not typically a good thing. Transactions should not be eliminated unless there was a major error in your source system.

[@antoniosol9863]

at 58:08 When you do Lookups againts the Dimension Tables. What is the purpose of this ? and What do you do with the Non-Matching Records ?

[@robertbiddle7789]

+Antonio Sol The purpose of the lookup transformation is to get the surrogate key from the dimensions. You can handle the non-matching records in a couple of different ways. Typically I will suggest that you use a Derived Column transformation later in the flow to convert all of the nonmatching records to have a surrogate key value of -1. This is the inferred member in all of your dimensions if you followed the methodology in the video. Effectively this will state those values are unknown, which is true as they do not match. A goal for a good performing fact table is that all joins are inner joins to the dimensions. Therefore we want all unknown or null lookups to go to an inferred member.

[@robertbiddle7789]

The lookups are meant to get the key values from the dimension tables. Because everything is on the same server in this particular example, it's less important to use lookup transformations and you can instead use T-SQL. However there are cases for using lookups as well. It's best to have all the tools available at your disposal. You don't want to do everything in T-SQL, nor do you want to do everything entirely with the SSIS components.

[@robertbiddle7789]

The non-matching records will effectively return a null key. The derived column transformation at the bottom of the fact package will handle the nulls, converting them to -1. If you do not set the lookup transformations to "ignore" and leave it as default then you will get a package error.

[@vijaykumar1299]

@@robertbiddle7789 Hi Robert Thank you for the Video.Lot of Helpful Information. My concern is once we insert -1 for non matching records what happens if we find a matching surrogate key in the future for the same 'AK' doesn't it create duplicate records in the fact table ?

[@sureshabeyweera6733]

Helll does anyone know how to make ETL using ssis from sql server to snowflake

[@raushanaryan6404]

sir plz share the data files of this project