Blog Page

SQL Saturday Puerto Rico!!

sqlsafety 0 Comments

SQL Saturday #205 hosted in San Juan Puerto Rico is less than 2 weeks away!!  I am presenting at two sessions, What’s new in T-SQL 2012 and Dealing With Errors In SSIS.  The schedule is complete and posted here.  I have spoken and attended several SQL Saturdays over the past few years, but have always had to run in or out to other engagements.  This year I resolved that I would dedicate myself to PASS these events and enjoy fellowship and learning.  I am very excited to attend this event and hope to see many of you there at #sqlsatpr.


SSIS IsQueryStoredProcedure/IsStoredProcedure Properties

sqlsafety 3 Comments

The sample package referenced in this post can be downloaded here.

                I have blogged several posts covering using parameterized T-SQL statements in execute SQL tasks, but I was recently asked what the IsQueryStoredProcedure/IsStoredProcedure properties do in an execute SQL task and what the difference is between the two.  The short answer is that these properties can only be set in an execute SQL task that utilizes an ADO connection manager and there is no difference.

               This property is a bit confusing and took me some work to uncover the true purpose and configuration.  To clarify the(se) property(s) drag and drop an execute SQL task onto the control flow pane and examine the properties pane as well as the task editor and you will find that these two properties exist regardless of the connection manager that is utilized. 


               And here in lies the confusion.  You notice both properties, IsQueryStoredProcedure/IsStoredProcedure, obviously different names, one in the editor window while the other in the properties pane.  The editor IsQueryStoredProcedure property is greyed out and cannot be changed from False, but the IsStoredProcedure property in the Properties pane is configurable.

              WOW!!  Sure appears to be separate properties to me!!  BUT WAIT!!  Close the execute SQL editor window and change the IsStoredProcedure property to True, re-open the editor window, and presto!!  The property has changed!


                Now create an ADO connection manager, or ADO.NET, to the AdventureWorks2012 database and configure the task to use this connection and you will see that the IsQueryStoredProcedure property is no longer greyed out and you can toggle between True and False, the same as the IsStoredProcedure property in the property’s pane.


                So we have uncovered that these two proerties are truly one in the same and really only affect those execute SQL tasks that use and ADO or ADO.NET connection manager, but what are the differences in configuring this property as True compared to False?  Obviously this property should be set to true when the query used in the task is a stored procedure. 

                To demonstrate create a package and drag and drop an execute SQL task onto the control flow pane.  Configure the task to use an OLEDB connection manager using the AdventureWorks2012 database and leave the IsQueryStoredProcedure property set to false.  Set the SQL statement property of the task to execute the uspGetEmployeeManagers stored procedure from the AdventureWorks2012 database.  This stored procedure requires an input parameter, @BusinessEntityID, that is an integer data type.  Based on these configuration proeprties you can set the SQL Statement property to “EXEC uspGetEmployeeManagers ?”.  The “?” defines the place marker for the input parameter value.


Since this stored procedure requires an input parameter create a variable that will hold the parameter value called BusinessEntityID and leave the data type set to int and set the value to 5.  Then configure the Parameter Mapping tab of the execute SQL task to use that variable as an input parameter.  You will notice that in the configuration of the Parameter Mapping the parameters do not use the index value, 0, 1, 2, etc., as is the case with the OLEDB connection manager, but use the parameter name used in the stored procedure, @BusinessEntityID:


                This seems pretty straight forward and the question still arises as to what exactly the difference is between using the IsQueryStoredProcedure proeprty set to True or False.  To demonstrate copy the existing execute SQL task and change IsQueryStoredProcedure property to True and attempt to execute the task and you will see that the task fails with an error.  Here in lies the difference.  The task can execute a stored procedure regardless of the property setting, but the configuration differs based on the property value.

               In the general properties of the new task change the SQLStatement to useGetEmployeeManagers, remove the “EXEC” and “?” and re-execute the task.  The task now succeeds!  The primary difference is that a place holder of a “?” is not needed in the SQLStatement proerty as the parameters are mapped by name in the Parameter Mappings pane and EXEC is not needed, or even allowed.


                This property ONLY affects execute SQL tasks using ADO connection managers, but just in case you were wondering the effect of changing the IsStoredProcedure property in the property pane for any other type of connection manager try it out.  You can only change the property from the property pane and not the task editor, but the result should be the same.

            I spent some time trying to get some documentation on this, specifically outlining the inconsistency with the same property having two different names and maybe some samples, but best I could do was here.  If you look half way down the page you will find examples of calling stored procedures with different connection managers and notice that the ADO connection manager specifies the different syntax based on the stored procedure property.  The below is taken directly from MSDN page listed in the hyperlink above.

Connection type EXEC syntax
EXCEL and OLEDB EXEC uspGetBillOfMaterials ?, ?
ODBC {call uspGetBillOfMaterials(?, ?)}For more information about ODBC call syntax, see the topic, Procedure Parameters, in the ODBC Programmer’s Reference in the MSDN Library.
ADO If IsQueryStoredProcedure is set to False, EXEC uspGetBillOfMaterials ?, ?If IsQueryStoredProcedure is set to True, uspGetBillOfMaterials
ADO.NET If IsQueryStoredProcedure is set to False, EXEC uspGetBillOfMaterials @StartProductID, @CheckDateIf IsQueryStoredProcedure is set to True, uspGetBillOfMaterials


SSIS BypassPrepare and DelayValidation Comparison

sqlsafety 2 Comments

     The sample package referenced in this post can be downloaded here:

     I blogged about the DelayValidation property of control flow tasks and packages a few months back and was just asked the difference between this property and the BypassPrepare property.  The biggest and most notable difference is that DelayValidation is present in all tasks and packages while the BypassPrepare property is only available in Execute SQL tasks.  The BypassPrepare property prepares a statement to be sent to the connection provider defined in the task and this will be performed ONLY at runtime, another distinction between this property and DelayValidation. 

     The default value for this property is set to True, since SSIS 2005 SP2, so queries contained in the task will not be prepared before being sent to the connection.  This can have some effect on performance; I will post a follow up with a sample package to demonstrate the extent to which performance is affected.  So the question arises “Why would I want to bypass preparation of a query“.  The answer is very simple and straight forward, if you are using a parameterized query in your execute SQL task you must insure this property is set True or the task will fail.

     To demonstrate this behavior of this property:

  1. Create a package 
  2. Add two execute SQL tasks
  3. Task 1 name Bypass True
  4. Task 2 name Bypass False
  5. Create a connection manager pointing towards any instance of SQL
  6. Create a variable called “dt” of data type string with a value of GETDATE()
  7. Bypass True query statement “SELECT GETDATE()”
  8. Bypass False query statement “SELECT ?”
  9. Map the “dt” variable as an input variable to the Bypass False task as a VARCHAR data type
  10. Set the BypassPrepare property of the Bypass True task to True
  11. Set the BypassPrepare property of the Bypass False task to False
  12. Connect the On Success precedence constraint from the Bypass True to the Bypass False task

The package should look similar to this:


          Executing the package will demonstrate that the Bypass True succeeds while the Bypass False fails.  Changing the Bypass False BypassPrepare property to True and re-executing the package will result in the task succeeding.  The reason is that a parameterized statement cannot be prepared. 

     To further delineate the difference between the two properties also note that the Bypass False task successfully validated, but still failed once the query was attempted to prepare.  That is because SSIS recognizes parameterized queries in this context while query preparation does not.


     You can review how to create parameterized queries in SSIS on my blog here and query variables here and parameterized queries here.  You can also find more information on the BypassPrepare property on MSDN under Parameters and Return Codes.

     As always the sample package is available for download here.

You Don’t Spell Certification with “Cheat”

sqlsafety 1 Comment

A recent discussion about certification brought to the surface a pet peeve of mine, or rather number two on the list of what really p1$$3$ me off.  Certifications are a means of demonstrating proficiency in a specific discipline and can provide a path to a new career, incentive pay, or just personal satisfaction.  While discussing certification path with a group of developers one began to boast of the best “brain dump” sites that provide certification questions and answers.  I tactfully expressed my opinion that using these materials was dishonesty and, in my opinion, shows questionable character.  He sneered and retorted, “Get off your high horse.  It’s a stacked deck.  The only way to pass those exams is to know the questions”.

WOW!!!  I suppressed my anger, took a deep breath and considered my words before responding.  “Bite me Delta Bravo” was the best I could come up with, well there is obviously some censoring.  Most who have worked in law enforcement will probably be aware of what a Delta Bravo is, and although not vulgar, does accurately represent the true character of anyone who would rely on such means to pass a certification.  I apparently struck a nerve, to say the least, and a passionate discussion ensued.

Our debate evolved into the myth that all certifying organizations “know” that no participant could ever successfully pass their exam without being supplied the questions before hand.  WOW AGAIN!!  For ten years now I have instructed a number of technical courses, all of which have required me to have successfully passed the associated certification exam(s), as well as hosted certification boot camps.  In every course and boot camp I have always stressed the importance of hands on experience in order to prepare for certification.  I find the statement that “no one can be expected pass the exam without cheating” misleading, untrue, and an excuse for the unmotivated to cheat.

From my experience technical certifications require more than just classroom lecture, reading, and practice questions, but actual HAND ON EXPERIENCE!!  In every technical certification I have taken I have always had to draw from my actual experience for some of the questions.  THAT IS THE POINT!!  As our discussion progressed it became apparent that most believed that bookwork ALONE should be the only step necessary to prepare for a certification exam.  C’MON MAN!!  REALLY!!  Obviously my career path has focused on SQL Server and related technologies.  If you review the certification path for any one of the specific disciplines you will notice ALL have the following qualifier, “At least 2 years’ experience of working with relational databases”.  After 24 years in law enforcement serving a couple in investigations this is what we call a “clue”.

Studying a book and looking at sample questions should never be the sole means of certification preparation.  I can honestly say that I learned more in a month of hands on work in a production work environment than any single year of academic study.  When preparing for certifications that fall outside of my specific job duties I create a test environment using my personal laptop and work through the exam objectives hands on.  This hands on experience has better prepared me to pass certification exams.  A certification IS NOT meant to be a badge designating nothing more than regurgitation of memorized answers, but is meant to show proficiency.  If a certification is required to obtain a position within an organization, exactly how long will someone last if they have never completed any of the duties expected in a test or production environment?  My last rant focusing on using a certification to get a position, I recommend anyone who argues that brain dumps or cheat sheets prepare you for the exam and the position I think that best practice dictates they only find other professionals who adhere to this practice, attorneys, medical professionals, etc.

As the discussion went on I was asked, “Why are you taking this personal.  You passed your exam without it.  So what!  Don’t force your principles on us”.  I will fore go the diatribe and rant and go right to why I take this so personal.  I hold this certification which0 is a means showing proficiency and knowledge in a specific technical discipline.  Those who hold a certification without having the knowledge necessary to hold or maintain it devalue the certification.  I remember during the height of the internet bubble in the late 1990’s seeing commercials touting “You too can be a network administrator.  Regardless of your background or experience we can train you to become certified as a network administrator”.  By early 2000 this certification became almost worthless due to the surplus in the market of “Certified” network administrators.  Amazingly enough many of these certified individuals had no real life experience and little, if any, lab experience despite the skills the certification claimed to insure.

Enough of my tirade obviously you have now found a really good button to push if you are looking to get me started.  Suffice it to say I take a great deal of pride in the technical certifications that I have attained and take issue with anyone who would devalue these by cheating.

SQL Saturday Costa Rica!!

sqlsafety 0 Comments

SQL Saturday #189 hosted in San José Costa Rica is only 2 weeks away!!  I will be presenting 2 sessions, Dealing with Errors in SSIS and What’s New in T-SQL With SQL 2012.  Take a look at the scheduled sessions here.

This should be an awesome event and I hope to see you there.



2013 SUGA International Education & Training Event

sqlsafety 0 Comments

     The 2013 SUGA International Conference is just months away!!  The conference is being held at Disney’s Coronado Springs Resort June 23rd-June 27th.  I will be presenting a pre-conference work shop, Introduction to SQL Server Integration Services, on Sunday June 23rd, and post conference work shop, T-SQL for Report Writers Thursday June 27th.  The schedule and details are still in in the works, but once complete will be posted here:

I hope to see many of you there!

Fun With The ExecValueVariable Property

sqlsafety 0 Comments

              The sample package displayed in this post can be downloaded here.  In addition to the tasks noted there are several other tasks that are used to demonstrate the ExecValueVariable property.

              While exploring options to ease custom logging and reporting I came across an interesting, confusing, and poorly documented property that is available in several control flow tasks.  I was hoping to find a means to simplify capturing rows that were passed through a data flow without using a row count transformation of rows affected within an execute SQL task without using a result set or output parameter when I found the ExecValueVariable property.  I was pretty fired up and thought that I had discovered the silver bullet that would meet all my custom logging needs.  As I began to examine this property I found that it accepted a user variable and immediately thought that it would capture the affected rows or actions of the task. 

               I configured a data flow task’s ExecValueVariable property with a variable, called ExecValueVariable, of a data type integer and fired it off believing that the end result would be variable containing the number of affected rows from the task.


              I was a bit dismayed to receive an error, “Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property”.  After this minor setback I began my internet research to find out what I had done wrong.  I came across a blog post here which gave me some more insight as to what this property does and does not do.  Basically the ExecutionValueVariable can be used to capture the value of rows or objects affected, but the task must have defined the ExecValue.  There are several tasks that implement the ExecValue and allow you to assign a variable to capture this value.

SSIS Control Flow Task

Value Captured

Execute SQL Number of rows affected
File System Number of files/Folders affected
Transfer Jobs Number of jobs moved
Transfer Error Messages Number of error messages moved
Transfer SQL Server Objects Number of objects moved


              Armed with this information I wanted to test the limits of this property and see exactly how it works so I began with an execute SQL task and a message box.  I used both DDL and DML statements in the execute SQL task to see how the ExecValueVariable would handle this:

col1 INT);


                                                  ExecValueVariable configurationXQTSQLVarProp 
                                                             Control FlowExecSQLValVariable
              Executing the task the result is what would be expected, a message box showing 1, the number of rows affected in the execute SQL task.  I edited the execute SQL task and added SET NOCOUNT ON in the top of the script and again executed the package.  The execution of the task succeeded, but the message box returned with a value of -1.  This demonstrates that the ExecValue is using the @@ROWCOUNT to assign the value of the variable and absent a @@ROWCOUNT the value returned is -1.

                 I was feeling pretty good about myself now so decided to experiment a bit with the ExecValueVariable again with a data flow task.  If you look at the chart above that outlines the tasks that support this property you will obviously notice that the data flow task is not mentioned, but IT STILL has this property.  I figured it was there for a reason right?!?!

              In my data flow task I used a source to the AdventureWorks2012 database with a simple query:

SELECT FirstName,
FROM Person.Person

I then used a flat file destination for the query results and configured the ExecVariableValue once again. I directed the on failure precedence constraint to another script task to display the variable value in a message box.


                                                      Control flow task CtrlFlowExecVariable
                                            Data flow task with source and destinationDataFlowName

              The results are exactly what I expected.  The data flow task failed and the message box displayed the default value assigned to the variable in the ExecValueVariable.  The reason for the failure is made apparent in the error message I shared above, “Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property”.  The component must use the ExecValue method to assign a value to the placeholder variable in the ExecValueVariable.

              Unfortunately there is no way to configure this within a dataflow task, but using a script task we can demonstrate implementing the ExecValue.  I added two script tasks in my package and configured the on success precedence constraint connecting the two.  The first script is used to set the ExecValue property and use a message box to display the user defined variable ExececutionVariableValue of the task.  The ExecValueVariable property in the first task configured to use the ExececutionVariableValue variable:

Dts.ExecutionValue = 9999;

MessageBox.Show(“Assignment of ExecVariableValue occurs AFTER task completion. The current value is : “ + Dts.Variables[“ExecValueVariable”].Value.ToString());
 The second script task displays a message box that displays the new assigned value.

MessageBox.Show(“Assignment Complete: “ + Dts.Variables[“ExecValueVariable”].Value.ToString());

 Executing the package displays the message boxes:


SSIS Hresult: 0x80004005 Description: “Unspecified error”

sqlsafety 0 Comments

     After a few weeks of running successfully I received a failure notification from a 2008 SSIS package.  As I began to investigate I found a single error message recorded from a data flow task that was quite short and non-descriptive:

SSIS Hresult: 0x80004005  Description: “Unspecified error”

     There were several data sources within the task, all of which were OLEDB for the SQL Native Client, and all used variables for the data source queries.  I immediately began to insure that the variable expression was evaluating correctly and that all connection managers were validating.  Of 8 different data sources I found 2 that would raise the error, but only after running for 7 to 10 hours!!  After a great deal of time I was not able to get any more descriptive error from SSIS and and as I only had data_reader permissions on the offending database at the instance I was limited in the efforts I could make at the instance.

     I continued to fumble with my package making futile attempts to get a more descriptive error description to no avail.  I finally remembered that even thought the connection provider was OLEDB for the SQL Native Client it was not the SQL Native Client, querying the DMV sys.dm_exec_sessions verifies that the provider is OLEDB or SQL Native Client from the client_interface_name column.  I captured the query that was being passed to the instance of SQL from SSIS and ran it within SQL Server Management Studio, which uses the SQL Native Client.  After 10 hours of execution I finally received the errors:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

     The underlying issue appeared to be a corrupt index, again with only data_reader privleges I am forced to turn my findings over the the DBA group responsible for that instance.  For the past few months I have been speaking on error handling in SSIS and was determined not to allow this vague error description beat me.  The lack of detail of the error is not the fault of SSIS but rather the provider.  You can find documentation of the SQL Native Client here:

What’s in a Program Name?

sqlsafety 0 Comments

                While monitoring an SSIS package I noticed that the program_name
that was being displayed in the dynamic management view sys.dm_exec_sessions was “SSIS-Package-{########-####-####-####-############}ConnectionManagerName”. 
I began reviewing the package properties specifically the package name and found that the program name being returned was actually from one of the connection managers within the package, just a caveat this was an SSIS 2008 package not 2012.  I began testing to
replicate this behavior in an SSIS 2012 package and was not able to. 

              Reviewing the offending package I found that the package name, connection manager names, and task names did not contain a GUID or even resemble the program_name.  I viewed the xml of the package and found the program_name that was being shown in the sys.dm_exec_sessions was found in the connection string properties

DTS:PropertyDTS:Name=“ConnectionString”>Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Provider=SQLNCLI10.1;Integrated
Security=SSPI;Auto Translate=False;Application Name=

Ta Da!!  I changed the value to reflect the name that better described the package and after
re-running the package and again querying the DMV I found the program_name was returning the new value I entered.  After review of several 2012 SSIS package I did not find the Application Name proeprty in the XML, so I am under the impression that this is specific to SSIS 2005 to 2008.




SSIS To Fail or not to Fail??

sqlsafety 0 Comments

              The package referenced in this post can be downloaded here.  Please let me know of any issues or errors that you may encounter with either the package or the download.

               I recently posted about using precedence constraints in an SSIS package to control the flow of a package and provide limited error handling.  I received several question about this post, all of which focused on how precedence constraints can be configured to evaluate the completion of multiple tasks success or failure as a whole rather than individually.  Consider this example:

  1. Package1 contains three tasks
    1. Task 1 must always execute
    2. Task 2 must execute only if Task 1 succeeds
    3. Task 3 must always execute
    4. Task 1, 2, and 3 must execute in order
      1. Regardless of success or failure of the previous task

Now consider how we could use precedence constraints to accomplish this. 

  1. Task 1 is the first task so will always execute
  2. To insure that task 2 executes only when task 1 succeeds an on success precedence constraint must be used
  3. To insure that task 3 always executes on completion of task 2 completing an on completion precedence constraint must be used between task 2 and task 3.

              And here lies the problem!!  With these requirements if task 1 fails task 2 will never execute, as is required, but this also means that task 3 will never execute since task 2 cannot complete unless task 1 succeeds.  This would appear to be easily remedied by connecting task 1 to task 3 with an on completion precedence constraint, but this would violate the requirement that all tasks must execute in order.  The diagram below illustrates the specific requirements:


               This conundrum can be easily overcome by encapsulating tasks 1 and 2 in a sequence container and using an on completion precedence constraint from the sequence container to task 3. 


              Kewl right?!?!  Well let’s add an extra degree of difficulty.  Based on the requirements outlined if task 1 fails and task 2 never executes then the sequence container should NOT fail based solely on the failure of task 1, and should show as successfully executing.  Before you jump to judgment and declare this a RIDICULOUS requirement, as I did when I was assigned the project, consider that you have implemented logging to capture errors and the container failing would result in an erroneous error logged since both tasks are not required to succeed, or even execute, in order to show the sequence container completed successfully.  After a quick review of the properties of the tasks within the container you would immediately think that the FailParentOnFailure property could be used to insure that if a task in the container fails the container will still show success.


              WRONG AGAIN!!  This properties default value is always set to False and executing the package with either task 1 or task 2 failing demonstrates that the sequence container will also fail despite both tasks having this property set to False.  I REALLY began to question this behavior and had to Google…  I MEAN Bing it.  I ran across this post on Microsoft Connect which explained this behavior.  The Microsoft response answered my question, although it doesn’t seem incredibly intuitive to me:

 “Despite some pretty circular previous messages, we believe that the feature is behaving as designed. When you set FailParentOnFailure to false, the parent will not fail until the number of failures in the child exceeds the MaximumAllowedErrors threshold. When you set FailparentOnFailure to true, the parent will fail on the first occurence of an error regardless of the MaximiumAllowedErrors threshold.

              Hooyah!!  Don’t hate the player hate the game, now knowing the rules it is easy to meet the goals based on the requirements outlined for this package.  The FailParentOnFailure property is already properly configured, in this situation the default value of False, and now me must change the sequence containers MaximumErrorCount from the default value of 1 to 2.  The default value of this property is set to 1, meaning that if any task or container within the container encounters a single error then the task, or container, will fail.  By changing this value, in this case it can be set to 2, if task 1 or task 2 encounters an error the sequence container will still succeed since only a single error was encountered.


Executing the package shows that

  1. Task 1 fails
  2. Task 2 never executes, which is expected and desired since it should only execute on success of task 1
  3. The sequence container shows that it succeeded, despite task 1 failing
  4. Task 3 executes


             I am REALLY hoping that some of you have been to any of my SSIS classes or SQL Satirday Sessions on Dealing With Errors in SSIS 2012 and will ask “Wait a minute David?!?!?!  You told me that a single error can raise multiple error messages?!?!  What if a tasks raises multiple error messages?  Will this affect the MaximiumAllowedErrors value?”  GREAT QUESTION!!  and the answer is no.  Even though one single error may provide a collection of error messages, it is still only a single error so you WILL NOT need to provide an estimated number for the MaximiumAllowedErrors property.  If an error occurs, regardless of the number of error messages, it equates to a single error.

             I will be presenting Dealing With Errors in SSIS 2012 at SQL Saturday Costa Rica 2013 which will cover this topic so PLEASE attend and let me know of any questions that you might have.

Hit Counter provided by Sign Holders