Top Ten Reasons Julie and Audrey are Excited about SQL Saturday 242

We are so excited about SQL Saturday 242 on August 10 in Indianapolis, IN!  Why?  Well, glad you asked us.  We’re going to be presenting our first-ever SQL Saturday Pre-Con, and are forever grateful to the wonderful organizers of this event for giving us this opportunity.  We’ve done this training day here in Atlanta before, but there’s just something extra-special about being part of a SQL Saturday.

The Pre-Con is titled, From Here to BI: Data Warehousing from Start to Finish, and we think it is going to be a lot of fun.  We’re most excited that we get to spend a day talking about topics that we love, including database design, ETL, and all the cool concepts and tools that go along with building a data warehouse and moving data around.  Our goal is to show that SSIS isn’t scary, and neither is dimensional modeling.  With practical examples and real-world scenarios, our audience will hopefully walk away with great ideas that they can take to their own teams on Monday morning.

We were discussing all of the reasons why we’re excited about Indy, so we decided that what we really needed was a list (we’re nothing if not organized).  So, without further ado, our Top Ten Reasons Julie and Audrey are excited about presenting a Pre-Con at SQL Saturday 242:

 

10:  David Letterman and Kurt Vonnegut are both Indianapolis natives.  It happens that Julie has something of a crush on Dave.  Audrey would have a crush on Kurt, but he died in 2007 so that’s kind of creepy.  Let’s just say she’s an admirer.

9:  Audrey gets to talk about Dimensional Modeling, which is one of her favorite topics.  Rumor has it that she has a Ralph Kimball poster hanging in her closet, but no one’s been able to verify it yet.

8:  Julie gets to talk about ETL Frameworks, a key design concept when moving data, especially in complex integration environments.  The extra time allows her to dig into the topic and discuss best practices and lessons learned.

7:  If the SQL Saturday organizers (Hope Foley (B|T), Eddie Wuerch (B|T)) are any indication, Indiana is populated by the coolest people on the planet, and we can’t wait to meet them.   Plus, Audrey gets to finally mark Indiana off of her list of states to visit.

6:  We get to discuss Master Data Services (MDS) and Data Quality Services (DQS), two products that we’re actually using at client sites and want to share with the world. Julie has been doing a lot of research and writing on DQS, so expect a great discussion about the tool!

5:  Julie gets to preach the business key gospel to a whole new audience.  By now, everyone in the South Eastern U.S. has heard her story, and it is time to take it to the Midwest.  Audrey asks that someone in the audience suggest that business keys aren’t that important in ETL development.  She promises that you’ll all be entertained by Julie’s response.

4:  We get to hang out with Bill Pearson (B|T), who is presenting his own Pre-Con, called Practical Self-Service BI with PowerPivot for Excel.  We contend that he has the best hair in the SQL Server community.  Don’t believe us?  Come see for yourself!

3:  We hear there’s this thing in Indiana called a Tenderloin Sandwich.  Deep fried pork loin on a bun?  Sign these Southern Girls up!

2:  We get to talk end-to-end development, which is almost impossible to do in a one-hour session.  There are so many great topics to discuss, and we’re super excited that we get a full day to cover them!

1:  And the number one reason we’re excited about SQL Saturday 242…  Road Trip!

tl

 

So there you have it, just 10 of the myriad reasons that we’re thrilled to present a Pre-Con at SQL Saturday 242.  Sign up before July 12 to get the Early Bird price.  Hope to see you there!

–Julie and Audrey

Mulligan–Don’t Forget to Vote for PASS Summit’s Lightning Talks–Again!

A glitch somewhere caused PASS to redo the Community Vote for Lightning Talks–Another Chance to make your choice known for your favorite sessions.  And if you need help with the menu, may I suggest the ham?

Also, if you are in the area, I hope you are considering Columbus, Georgia’s first SQL Saturday (#167) !  I am delighted to be speaking at this event!

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 B

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 Deployments, Lesson Two — Part A

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 SQL University– SSIS 2012: Deploying Projects –Lesson One

Messing with Errors in SSIS, or does the Propagate “Property” really exist?

The default behavior of SSIS when an error occurs is for the error to “Propagate” or Bubble up in lay terms to the very top and to stop the package execution.  For instance, if I have a dataflow and it attempts to insert a null value into a non-nullable column, the task will error out and the package will stop running.

 

and Cut!

But of course this behavior of package haltage** is not always desirable.  For instance, if you are looping through a directory of xml files and using an xml task inside the loop to validate an xml file against a schema (xsd), a failure (meaning a file failed to validate) can be used as information and as means to direct another desired action.  You could move the bad file with  a file system task out of the directory and move on to the next file to validate.  In this case you wouldn’t want the package to stop.

How do you avoid a package execution haltage?  One way is to use the MaximumErrorCount Property available on most tasks and containers. Its default value is 1, meaning at the first error the package encounters, that exceeds the Maximum allowed and therefore it issues a STOP to the package.  You can change it to 0 (zero) and that means “error all you want, I will never stop.”  The problem with using MaximumErrorCount to try and control package haltage is that errors will bubble up, or propagate to the parent items (sequence containers, for each loops, etc all the way up to the package) and cause the package to stop anyway.  The way I have previously dealt with unwanted bubbling was to isolate, as much as possible, the functions for which I would tolerate errors to one package and then set the maximum error count to 0 on all of the items—the child task, the containers it resided inside and the package itself.

Arrows indicate the 3 separate MaximumErrorCount properties which would have to be individually configured to 0 to avoid Package Haltage.

Then John Welch (blog | twitter) *** told me the secret—and now I will share it with you.  There is a Legendary, Wily property (it’s actually not a property, but it should be) called Propagate–>On Error ,  and you can change its setting to False.  Once this is done, the errors will not propagate or bubble up and you have a more elegant way to prevent unwanted Package Haltage.

The following is a dialog between Julie1 and Julie2 as we attempted to find this illusive property beast.

Here is a play by play:

  1. Select the task in the control flow for which an error should not halt the package.

2.  Go to the Event Handler Tab.

3. Click the Blue text:

4. Watch as the gray screen turns to the familiar pale yellow of BIDS. (you don’t have to drag a single task onto the error handler!)

Change me to False while you have the chance! Run!

5. Go to the variables of your package.  Click the gray button for system variables. ( I click the column header over the names of the variables at this point to sort them, so that I can finally find this BEAST).

6. Change the default value from True to False.

Ta Da.  Now your package won’t halt if an error occurs at this task.

Your photograph of “Propagate” will make you millions from the tabloids and you can quit this data racket and follow your passion to become Lady Gaga’s chief groupie.

Propagate, caught!

**HALTAGE –this word is my invention, unlike Sasquatch, who is TOTALLY REAL AND NOT AN INVENTION.

*** John Welch is completely awesome.  Just sayin.

Voting for SQLRally–BI Track This Week!

The voting is open this week for the BI Track for SQLRally.  SQLRally, a new offering from PASS,  is a two day conference which will be held May 11 -13 in Orlando, Florida.  I think of it as a cross between the free SQLSaturday and the mega conference PASS Summit in Seattle.  This is the inaugural year and the organizers have opened the selection of speakers to the prospective attendees.

I’ve submitted a session (my Cool Tricks to Pull from Your SSIS Hat) and of course, would love your vote.  Regardless of whom you vote for, you should let your choice be heard.  Go here to vote.