SQL Saturday #74 Session Recap
Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Saint Augustine, FL. Bradley is a former consultant, trainer, and has coauthored 6 SQL Server and Power BI books, most recently Learn Microsoft Fabric. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. Bradley gives back to the community through speaking at events such as the SQLBits, Fabric Community Conference, PASS Community Data Summit, SQL Saturdays, Code Camps, and user groups across the country including locally at the Jacksonville SQL Server User Group (JSSUG). He is a contributor on SQLServerCentral.com and blogs on his personal site, BradleySchacht.com.
Data Flow:
As you can see in this finished data flow the aggregate transform took 12,627,608 records and turned it into 3 records. This means that we just read in over 12 million records from the source database into memory just so that we could load 3 records into the destination! Wouldn't you rather take care of all that work up front on the SQL Server side of things? This is why using a source query is so important in many cases. There will be times when you can't get around using some of these "bad" transforms, but for the most part it can be done in a source query. I can't stress enough use the right tool for the job. In this case it would be a combination of SQL (for the source query and aggregation) and SSIS (for transferring the data from one server to another.. 
One other thing that I hope I stressed enough to everyone is good naming conventions. I joked about naming all my packages "package" so I know what they are and all my SQL tasks Execute SQL so I knew what is was doing. If you don't believe me that good naming conventions are a requirement then lets chat in about 6 months once you have had to do some troubleshooting on your production environment. I'm sure it will be a piece of cake and a quick solution to determine what went wrong with Data Flow Task 23 inside of Package58. One final thought before I go: making the lines straight does not make your data flow faster because there are less turns and moving the source closer to the destination does not speed things up because the data has less distance to travel. But those things do make the data flow pretty and helps my OCD a lot! :) Thanks again for coming out, I had a lot of fun and a wonderful day. I hope I get to talk with some of you again soon. If you're in the Pensacola area come out next month to SQL Saturday. I will be speaking on SSIS configurations, expressions and variables. I will also be co-presenting a session on data warehousing with [Devin Knight](http://www.bidn.com/blogs/devinknight "Devin's Blog") ([Twitter](http://twitter.com/#!/knight_devin "Devin's Twitter Account")) and then doing Pulp SQL with [Jorge Segarra](http://www.sqlchicken.com "Jorge Segarra's Blog") (AKA SQLChicken| [Twitter](http://twitter.com/#!/sqlchicken "Jorge's Twitter Account")) where we will talk about project blunders so you can avoid the mistakes of others. WARNING: Pulp SQL is for adults only and may or may not result in random fight sequences between Jorge and myself, also the front 2 rows are considered a splash zone, bring a poncho.