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 Using Biml with SQL Server 2000; There IS a Way.

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

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

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.

Name/Twitterhandle:

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 Part Two–The Locals Window