Using Biml with SQL Server 2000; There IS a Way.

by Audrey and Julie

Summary: The BimlScript methods of Business Intelligence Markup Language (Biml) will only work (ie access SQL metadata) with SQL Server versions 2005 and higher. This article briefly tells the story of how Innovative Architects worked around this limitation for one of our projects and successfully tricked Biml by creating system views in SQL Server 2000. They ddl script for the views is found in a link at the end of the post.

————————————- Continue reading

Another NULL Handling SSIS Expression

by Julie Smith

So two days ago I posted this. It’s a way to generate an SSIS Expression for use in an Incremental Load’s Conditional Split. A friend had pointed out that this pattern was not the best– as NULL handling is not always as easy as replacing the NULL with what you might consider a safe value. I also got a very thoughtful comment on the post from a lovely gentlemen expressing the same concern. So obsessed, I went back to tinkering.  I came up with ANOTHER expression (and consequently another T-SQL generator for it). I like this one a little better as it seems to me that it performs what is asked without introducing risk of replacing NULL values. So folks please read this, use it, bash it up and let me know what you think.

Here’s the new (to me, sure someone had already figured this out) NULL Handling expression for DELTA rows, using the column Color as an example:

This does NOT break the Conditional Split if there are NULLS. There can be NULLS in either the source or the destination or both and it does not break the pipeline. I love that.

How to read it from the left:

The whole expression will evaluate as TRUE and the row split into the Delta path when either the yellow portion or the Green and blue portion together evaluate to True.

The yellow highlighted expression asks: is either side NULL while the other is not? If yes, then evaluate to TRUE.

The Green highlighted section: Are both sides NOT NULL ? If yes, Then Blue highlighted section asks: are they unequal? If yes,  then evaluate to True.

If both sides are non null, yet equal, or if both sides are NULL, then the condition is not met and the row is ignored. Just like we want it to be.

Now the SQL to generate the whole expression can be datatype agnostic. I love that too. Here is the SQL to generate the whole concatenated shebang for all of your columns:

Click on the SQL below to get a copy/past version 🙂

Click on this to get copy/paste version.

Continue reading

Using SQL Metadata to Create NULL Handling SSIS Expressions

By Julie Smith

Happy 2014! Happy to report that Audrey and I were both renewed as SQL Server MVPs today!  To celebrate I’m publishing a really long blog post.

This is a MONSTER long post. The main point of this post was to give you guys some T-SQL code which can be used against the information schema view of a SQL Server table destination to spit out a complex SSIS Expression. While that was the point of the post, I felt like I also needed to provide context of what I was trying to do or what you might be doing when the need for such SQL would arise. 

also, I had Audrey read it over and she scolded me for a sad lack of chuckles. So I’m adding in some random Chuckle-y interludes. Enjoy them. Or skip them completely. Chuckle interludes are indicated by the face Chuck Norris doesn't sleep. He waits. of Chuck Norris for easy recognition.

One of the most common scenarios encountered while ETL’in is an Incremental Load –that is determining if a source row already exists in the target database (often your warehouse), and if it does, has it changed since the last time it was loaded? The pseudo code goes like this: Continue reading

Full Day of Training Offered by the DataChix! (On Pi Day, no less)

Do you want to learn about Data Warehouses? Do you want to learn about Extract, Transform, Load with SSIS?

Do you like Pi?

piday











Do you like Pie?

Learn DataWarehousing with the DataChix!

SQL Server MVPs and popular blogging team Audrey Hammonds and Julie Smith of DataChix.com offer this one day of training to benefit The Cloverleaf School of Atlanta in cooperation with The American Legion Post 251, Duluth, GA. Audrey will take you on a guided tour of converting a transactional model into Dimension and Fact tables. After the model has been created, Julie will guide you through loading those tables using SQL Server Integration Services.

To top it all off, it’s happening on March 14th folks.

That’s right. 3/14 Pi Day. Could there be a better way to celebrate the irrational number which represents the ratio of a circle’s circumference to its diameter than by using SSIS? NOPE! But maybe we’ll get some delicious Pie to celebrate as well!

Audrey and Julie bring over 25 years of warehousing experience to this fantastic day of training, but don’t say that to their faces!

audreyjulieaction2

Part I Dimensional Modeling with Audrey

Why Transactional databases are the way they are—Codd’s rules to live by

Why Codd’s rules don’t work for Reporting– Kimball’s rules to live by

Fun at the whiteboard converting a transactional model to a dimensional model.

Part II Extract Transform and Load using SSIS with Julie

A tour of SSIS

audreyjulieaction3












Control Flow Basics

Data Flow Basics

How to load Dimensions with SSIS

How to load Facts with SSIS

Why SSIS has got such a bad rap sometimes.

Frameworks

What happened and when

When things go wrong, how to know the details

Top quirks of SSIS—what, why, and how to get around them.

Data Quality! We’ll throw in a little Data Quality Services with our ETL!

Lunch and Continental Breakfast provided. Please contact the organizer at the above email for any special dietary considerations! Thanks and see you on Pi Day!

FOR TICKETS—–http://datawarehousingdatachix.eventbrite.com/#

Can’t make it to this great day of SSIS training to benefit the school, but you’d still like to support our mission? http://t.co/VX6yGftC

Atlanta MDF Presents Three Awesome PreCons at SQL Saturday 111 in Atlanta, GA! April 13, 2012

Atlanta MDF presents:

SQL Saturday #111 Pre-Conference Sessions

Attend a session presented by Microsoft SQL Server MVPs (and one Microsoft Guy):

Andy Leonard and Matt Masson– A Day of SSIS – http://dayofssis.eventbrite.com/

A Day of SSIS was developed by Andy Leonard to train technology professionals in the fine art of using SQL Server Integration Services (SSIS) to build data integration and Extract-Transform-Load (ETL) solutions.  The training is focused around lectures and emphasizes a practical approach. The target audience for this training is database professionals, application developers, and business intelligence practitioners interested in acquiring or expanding their existing SSIS skill set. No experience with SQL Server or SQL Server Integration Services is required before attending this training.  It is helpful (but not required) that students possess some knowledge of and experience with relational databases. SQL Server knowledge / experience will be more helpful than experience and knowledge with other technologies.

Adam Machanic – No More Guessing! An Enlightened Approach to Performance Troubleshooting – http://nomoreguessing.eventbrite.com/

Scratching your head, you stare at the screen. Should you rebuild an index? Create a new one? Reboot the server? Why is this query so slow?!? Figuring out performance problems can sometimes feel like fumbling your way through a dark room. Maybe you’ll get lucky and find the right solution―or maybe you’ll stub your toe. Either way, it’s a slow, potentially painful process. Yet finding the root cause of most performance issues is a simple exercise, once you understand where to look and when. In this full day seminar, you will learn a proven methodology that can be used to approach virtually any performance problem. Created and refined by Adam Machanic over several years, this strategy leverages core SQL Server performance tools (including dynamic management views, Extended Events, and WMI counters), applying them to various performance troubleshooting techniques (such as waits and queues analysis, baselining, and real-time activity monitoring). Each of these tools and techniques has a unique role, and you will learn to use them cooperatively to quickly and adaptively find the actual cause of performance issues. All of this will be illustrated through complete demonstrations that will teach you how to drill from high-level problem detection all the way to specific spot in a query plan or deeper―pinpointing the exact problem and helping you to quickly solve it. Attend this seminar to take full control of your databases—and never again stumble blindly through the dimly lit world of performance troubleshooting.

Jessica Moss – Learn SSRS in a Day – http://ssrsinaday.eventbrite.com/

SQL Server MVP, Jessica M. Moss, presents an exciting, introductory, full day training session on SQL Server Reporting Services 2008 R2. In the three-part class, Jessica will teach you how to build reports from the ground up. In Part 1, learn the basics of report development, including picking a report development tool and creating your first report. Part 2 delves into visualizations, groupings, and drill-down functionality. Finally, Part 3 highlights core administration tasks in Reporting Services. In addition, Jessica will point out industry-wide best practices for report development and show numerous live demos using a variety of data sources.

Friday, April 13, 2012

Georgia State University – Alpharetta

3775 Brookside Pkwy

Alpharetta, GA

Register for pre-conference sessions by March 15 for $99!

($109 after March 15)

For registration to the Saturday Event and more information visit  http://www.sqlsaturday.com/111/eventhome.aspx.

SQL University – SSIS 2012 Deployments, Lesson Two — Part B

Welcome back. Today we’ll finish up this whole series by Executing packages with the SSIS Catalog in SSIS 2012

This is the conclusion of my series on Deploying and Executing Projects and Packages in SSIS 2012. If you are just joining us, you may want to read Lesson One and Lesson Two, Part A.

We’ve deployed the project to a folder in SSIS Catalog, now let’s create Environments and Environment Variables inside the SSIS Catalog:

Continue reading

SQL University – SSIS 2012 Deployments, Lesson Two — Part A

Lesson Two. Deploying Projects in SSIS 2012 (Today’s class is longer than I expected, we’ll take a break and finish tomorrow.)

Before we get started, a big thank you to Jorge Segarra (blog|Twitter) for inviting me to write for SQL University .


PreRequisite: Lesson One

By the end of this part of the lesson you should understand the following processes/terms.

  1. Building SSIS Projects
  2. Ispac files
  3. The SSIS Catalog
  4. Deploying ispac files

By the end of the second half of the lesson, the following will be covered:

  1. SSIS Catalog Environments
  2. SSIS Catalog Environment Variables.
  3. SSIS Executions
  4. Building Agent jobs with SSIS Catalog projects.

So you’ve got your Project with a Parent package. (by the way, you should designate Parent Packages as Entry Point packages inside a project by right clicking them and selecting “Entry Point Package”). You’ve got some child packages. You’ve got some variables. You’ve got project level parameters for values which should be available to all packages inside a project (like say, a directory for importing/exporting files, a client to process, a business unit to process, etc. ) and you’ve got package parameters for values which need to be passed from parent packages to child packages. You’re ready to Build, Deploy and Execute in SSIS 2012 utilizing all of its new toys features.

Oooo shiny new SSIS Project—Ready for Deployment

Continue reading

SQL University– SSIS 2012: Deploying Projects –Lesson One

Lesson One, where I introduce tons of prerequisite new concepts in preparation for Deploying and Executing in Lesson Two.  The main concepts and terms covered in this lesson are:

  1. Projects
  2. Project Deployment Model
  3. Legacy Deployment Model
  4. Project Reference reference type –Execute Package Task
  5. Ispac files
  6. Parameters, subdivided into the two following subtypes:
  • Project  Parameters
  • Package Parameters

It will help if you are familiar with the following concepts inside SSIS (pre 2012 versions) before attempting this course 😉

  1. Execute SQL Task
  2. Execute Package Task
  3. Connection Managers
  4. Variables
  5. Parent/Child packages

SSIS in 2012 brings a great number of significant changes.  It is the first version since its initial release with SQL Server 2005 to look significantly different.  There is a more modern look and feel to be sure.  However, the really big change to SSIS in 2012 is in the way in which it is deployed.  There is a new method of deployment called “Project Deployment”, which has as much to do with how and where a package is deployed as it does with how a package is bundled during development.  Let me go back in time a bit and explain. Continue reading

Cool Tricks at Integration Group of Atlanta

I was fortunate to be able to speak at the Microsoft Integration Architects monthly meeting tonight. Thanks to all who came out tonight to see the presentation! This is a great group which tackles Integration from a multi -product standpoint. I am going to be attending these meetings!

Here is a link to download the slides as pdf and a zip with the code:

–Julie