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

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

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:


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.

I went to SQL Saturday in Tampa!

 My husband and I got married May 3 (I’m not telling the year).  As we were rushing to the airport en route to our honeymoon, we realized that in the chaos of the wedding, we had almost forgotten a present for our mothers on the upcoming Mother’s Day.  I somehow found the perfect card given the circumstances, and we mailed them just as we were leaving.  Here is a rendition of the card we each sent our mothers that year:


The reason I’m thinking of that anecdote is that here it is on Tuesday, and it’s my turn for a blog post, and I’m in sort of a rush again.  Not on my way to Europe this time, but anyway; I’m busy.

I wanted to do a write up of SQL Saturday #62 in Tampa which occurred two weeks ago, but I feel like the content is going to be as dense and informative as the above referenced greeting card. 

Suffice it to say that I had a fantastic time.  Organizers Pam Shaw and Jose Chinchilla are to be commended for the hospitality, the food, the speed pass idea.  It was a great weekend. 

It was warm in Florida.  This cannot be overemphasized.  I really enjoyed the Sun (Blog | Twitter) and the way its shininess let me cavort around mittenless.  I hope to return to Tampa every time there is a SQLSaturday in the winter.

My presentation was well attended and well received.  Lots of folks came, including many people who work with SSIS and are pretty famous for it.  Fortunately for me their presences neither made me faint nor throw up, always a bonus. 

I left with more friends and professional connections, and perhaps most importantly, the most awesome SQL Saturday presenter’s shirt EVAH:

If only I had something yellow to wear. Not a polo.


Finally, I’d like to “roll credits” so to speak and give a shout out to all the great folks I got to spend time with that weekend.  (Loving the networking tab on the SQL Saturday Page!).  Can’t wait to see you all again.


Aaron Nelson @sqlvariant   
Adam Jorgensen   @adam_jorgensen
Andy Warren    @sqlandy
Bobby Dimmick    @kr4ster
Brad Schacht   @bradleyschacht
Brian Knight @BrianKnight
Brian McDonald    @briankmcdonald
David Taylor    @dyfhid
Denny Cherry    @mrdenny
Jorge Segarra    @sqlchicken
Jose Chinchilla    @sqljoe
John Welch    @john_welch
Jason E Bacani    @jebacanisqldude
Jack Corbett    @unclebiguns
Geoff Hiten   @sqlcraftsman
Gareth Swanepoel    @garethswan
Ed Wilson   @scriptingguys
Erika Bakse    @baksedoesbi
Eric Wisdahl    @old_man_fish     @EricWisdahl
Kevin Boles    @thesqlguru

Mike Wells @SarasotaSQL
Pam Shaw    @pamshaw
Troy Gallant    @gratefuldba
Tim Radney    @tradney
Stacia Misner    @staciamisner 


And lastly, SQLRally voting for the BI Track will be next week.  Don’t forget to vote! (for me! ) 

Debugging SSIS Variables Part Two–The Locals Window

From the book of Bloggering, Chapter 59 Verse 11: Thou shalt humbly accepteth better solutions to thine posits as they appeareth in thine Comments Section.   Yea verily, although thou may fearest change, thou shalt learneth new and better coding in this manner and be pleasing in the sight of thine employer.

I had a blog post several weeks ago about debugging the values of SSIS Variables. I shared the method I have always used, which was simply (actually maybe it’s not simple at all) adding a script task into the control flow and placing a message box in the control flow, displaying the value of the variable. Continue reading

Debugging SSIS Variables—and a Happy Announcement from the Datachix.

Audrey and I have decided to blog regularly and alternately on Tuesdays. We’ve each had good runs of things and had months where we posted a lot, but we’ve never had great consistency. So now we’ve committed to a schedule.

From the Book of Bloggering Chapter 23, Verse 9: The Datachix shall blog weekly. The day of the week on which they shall post shall be Tuesdays. Mondays will not be the day the postings shall occur, unless it is only to load and schedule a post for the following day–Tuesday. Neither shall Wednesdays be the day of the posting, unless there be a time zone situation where the reader is residing in Wednesday time whereas the Datachix (the said aforementioned poster/bloggers) are residing in Tuesday, and therefore posting their blog. Thursdays are right out, as are Fridays. Don’t even mention Saturdays.

Continue reading

The Case of the Mysterious Failing Packages

Or, that time I pimp-slapped some GUIDs…

Let me set the stage for you. We’re less than a week out from a major production push. We move the SSIS (2008) packages over to the integration testing server to make sure everything is good to go. On each data load, something fails. Never in the same package, never in the same task, never from the same source data. We think it’s the server. We move the packages again. More failures.

I’m losing sleep, breaking out in a weird rash that may or may not be related to my panic, and my boss is at my cube every hour asking how things are going in his, “I’m trying to be supportive, but fix the damned thing already”, voice. I’m rethinking my Don’t Drink During Work policy. This is not good. I just can’t find anything wrong. I can run the packages independently and they’re all happy green boxes. I can run the whole load from the beginning and it’ll get past the point of the prior failure only to crap its pants further down the line. Now, full disclosure: I sort of suck at SSIS. It’s fairly new for me, and while I’m learning, I still have a long way to go. Let’s just say that my self-confidence was not at an, “I’m Okay, You’re Okay”, sort of level.

After the latest failure and an inappropriate-for-the-workplace, profanity-laden outburst from me where I may or may not have referred to SSIS as “the stinking offspring of a poxy two-dollar…”, oh, never mind… I hear a question from the next cube over. “Have you reset the GUIDs?” What? GUIDs? Uh, no. I stand up, plant myself in my co-worker Tom’s cube, and ask for an explanation. He explains that if packages are copied, sometimes the GUIDs can be the same in two packages. Then, if they end up running in parallel, it can screw up memory management and SSIS freaks out. And as we all know, when SSIS ain’t happy, ain’t nobody happy. We use a stock template for all of our packages that is copied and then saved off as the new package. And, since our dimension loads are pretty standard, if we need to add a new dimension, sometimes it’s easier to copy one load package and modify it rather than building one from scratch. Yes, I’ve definitely done some package copying in my short tenure as an SSIS developer.

Jeff wanders over, listens to my plight and confirms that I should definitely try resetting the GUIDs. These two guys, who have since been inducted into my Personal Heroes Hall of Fame, explain that with BIDS Helper (totally free download), I can reset the GUIDs on a package and hit every object within that package. A quick rundown of the feature and why it is useful is here. Luckily, I had already installed it for a couple of other features it offered, so I got to work. Can’t hurt, right? Our ETL process is fairly complex, and we have a lot of data moving around. Dimension loads, fact loads, auditing, staging, the works. It took about an hour between me and another co-worker, Tim, to step through all the packages, check them out of TFS (Team Foundation Server), reset the GUIDs, and check them back in. It was easy. Just right-click on the package, look down to the bottom of the menu, click Reset GUIDs, confirm, and you’re done.

We move the packages with their new and improved GUIDs back over to the integration testing server, and viola!, data’s flying around like Your Mama insults at a middle school. No failures. Let me reiterate: With no other changes, the sporadic failures stopped when we reset all the GUIDs.

Now, I can’t say with 100% certainty that resetting the GUIDs definitely fixed the problem. All I know is that we did it, and the failures stopped. That’s one hell of a coincidence if it wasn’t the issue. This is right up there with the legendary, “Try rebooting the server”, and his lesser known cousin, “Restart the service”. It is something to try when all else fails.

What did I learn? A few things:
1) SSIS does not have a personal vendetta against me. There was no ghost in the machine.
2) BIDS Helper is my friend. Just install it. You’ll love what it gives you.
3) If I copy a package, I make it part of my routine to reset the GUIDs.
4) When in doubt, curse loudly until someone comes up with an idea (just kidding). Seriously, this is a great example of why we should get up from our desks and enlist the ideas and knowledge of those around us. Turns out that I work with some pretty smart guys.