Blog Page

#SQLSatDC 10 Reasons to Attend

sqlsafety 0 Comments

It is just over one month until SQL Saturday #347 in Washington DC and if you haven’t made plans to attend then let me motivate you to git er done.

1. Allen White, @sqlrunr , Get Near Realtime ETL with Service Broker Do you need your data warehouse to maintain near real time data?  Learn how to use service broker to accomplish this!

2. Michael Bourgon, @mbourgon,  SQL Watchdog – find out instantly when SQL changes occur in production Don’t you LOVE it when changes are made to your databases without notice!!  Those AWESOME “Object doesn’t exist” and other unexpected errors can be soooo much fun!  Time to put an end to these unreported changes and get real time notice when they occur.

3. Ayman El-Ghazali, @thesqlpro, Performance Tuning Pit Stop for DBAs Need direction on creating a baseline for SQL, tuning your tempdb, log file best practices?  Perfect time for a pit stop!  

4. Carlos Chacon, @carloschacon, I Know What You Did Last Query” –SQL Server Ever wanted to query information about a workload instance, gather performance metrics, or validate system settings?  Join Carlos in this stalkerish like session.

5. Steve Jones, @way0utwest, Branding Yourself for a Dream Job Are you looking for a SQL dream job?  Join Steve in this session and he will hire you!!  OOOOPS!  Misread it again.  This session focuses on tips and suggestions to better market yourself.

6. Jessica Moss, @jessicammoss, Analysis Services: Multidimensional Vs. Tabular What exactly is the difference between SSAS Multidimensional and Tabular?  Join Jessica and find out.

7. Ravi Kumar, @sqlravi, Think like SSIS and Build a package Stop thinking outside the box and think like a package.  Need help in creating your SSIS package then stop in and Ravi will learn you.

8. Gigi Bell, @sqlspouse, Dealing With Difficult People How do you deal with difficult people?  Well after being a police officer for 24 years I arrest them, but being retired now I guess I’ll see everyone at this session.

9. Doug Purnell, @sqlnikon, Congrats on your promotion to DBA, now what? My first suggestion would be lunch, but I am guessing that this is not what Doug will cover.

10. REALLY!!  You need 10 reasons?!?  How bout this is the nations capitol and an awesome place to visit.

See you there!


sqlsafety 0 Comments

I was recently presented with the task of examining a database, from historic backups, to determine transactions applied over the past 6 years.  Unfortunately, the only background/source data available was a few weeks of full backups and, to make this more of a challenge, many conclusions had already been reached. The most notable conclusion focused on alleged malicious deletions.

After some discussion I found that the primary basis for concluding that deletions had been made were the “gaps” in the IDENTITY seed (I am hoping to get this submitted to ESPN’s “C’MON MAN!” for next season)
An IDENTITY seed is not meant and CAN NOT provide proof of a deletion.  To provide a simplistic example that details some similarities to the data structure in question the below code creates two tables:

  • Category table
    • Primary key is an IDENTITY seed
    • Has a one to many relationship to the primarydetails table
  • Primarydetails table
    • Primary key is an IDENTITY seed
    • catID column has a foreign key restraint to the category(catID) table

USE tempdb; GO CREATE TABLE category( catID  INT IDENTITY PRIMARY KEY, catdescrip VARCHAR(20)
CREATE TABLE primarydetails( ID     INT IDENTITY PRIMARY KEY, descr  VARCHAR(20), catID  INT REFERENCES category(catID)

For each INSERT on a table with an IDENTITY column the value is incremented based on the increment value, in this example the default seed and increment values of 1 are used.

*if a seed and increment are not provided then the default is that the column begins at 1, the seed, and increments by 1

The following code will insert a row into the category table and a row into the primarydetails table:

INSERT category VALUES('A plan to fail'); GO INSERT primarydetails VALUES('This''''ll work', 1); GO

SELECTing from either the category or primarydetails table will show that the IDENTITY column for each is set to 1, the single row.

The IDENTITY value is incremented at the beginning of the INSERT and will not decrement on error.  This can be replicated by attempting to INSERT data that would result in truncation in the primarydetails(descr) column:

INSERT primarydetails VALUES('This is way too long to be inserted in to this data type and char length', 1); GO

The above statement will fail, but will result in the IDENTITY column being incremented.  To display this “missing” value another successful INSERT can be made and then SELECT from the primarydetails table to show the results.

INSERT primarydetails VALUES('2 was already used', 1); GO SELECT * FROM primarydetails p JOIN category c ON p.catID = c.catID; GO

The results should like like this:


From the results it is obvious that there is a gap in the IDENTITY, but this is not the result of the “2” ID row being deleted.

The truncation error that I demonstrated can easily be handled on the client side, which was quickly brought up, but keep in mind that the more constraints and keys on the table can also result in a failed transaction and incremented IDENTITY if not handled client side.

After reviewing the target table I found that there was a cascading trigger for AFTER INSERT, UPDATE, just one out of the 71 triggers in the database, that was more than 200 lines of t-sql code consisting of numerous IF statements to handle control flow.  Apparently the client side validation was being handled server side in the trigger (after all triggers are like cursors and puppies and behave better if you have more than one so that they entertain each other).

So for arguments sake let’s assume that the INSERT does not terminate early based on violation of referential integrity, truncation, check constraints etc. The INSERT command fires the trigger, again all is right in the world and there are no issues buried in the 200 lines of t-sql,  and based on the IF statements another transaction is begun to another table that contains a trigger, which in turn fires another trigger, which……..

Enough venting.  I wont delve into how many levels this trigger dives, suffice it to say that a failure at any level results in a failed transaction and a “gap” in the IDENTITY.

Amazingly enough the question was asked about what happens to those “lost” identities?  Are they gone forever?  I never thought of it that way.  It is a bit sad that they never even had a chance to establish themselves before becoming extinct in their table.

The only way to explicitly assign an IDENTITY value is to SET IDENTITY_INSERT schema.table ON for the transaction.

SET IDENTITY_INSERT dbo.primarydetails ON GO INSERT primarydetails(ID, descr, catID) VALUES(2, '2 is now used', 1); GO SELECT * FROM primarydetails p JOIN category c ON p.catID = c.catID; GO


Easy as that number 2 is back from the abyss.  There are a few limitations, such as the specified table being used for replication and that only one table can have IDENTITY_INSERT set to ON in a session.

#SQLSat326 10 Reasons to Attend

sqlsafety 0 Comments

The countdown begins!! SQL Saturday #326 Tampa BA Edition is in just over 2 weeks and its time to register and make your plans. If you are looking for some reasons to attend let me help you out.
1. Pam Shaw, @pamshaw Tips & Tricks for dynamic SSRS Reports Looking for some pointers for creating parameter driven data sources, report formats based on your data content, or creating report templates? Then this session is for you.

2. Michael Reed, Big Data Parts 1-2 Wondering what this whole “Big Data” is about?  Michael is presenting two sessions to get you up to speed.

3. Jen Underwood, @idigdata Advanced Analytics with Excel and Power BI So you got your data and are looking for a way to present and analyze it?  Jen walks you through using Power BI, Excel, and a variety of other tools to git er done.

4. Frank Quintana, @frankquintana2 An introduction to Business Intelligence and Big Data  What’s the difference between OLTP, OLAP, and Big Data?  I’m not gonna ruin the ending.  You have to show up to find out.

5. Joe Rossi, @bigdatajoerossi Hadoop – Past, Present and Future Ever wondered what Hadoop is, how it works and where it’s going?  Find out at SQL Saturday 326!

6. Max Trinidad, @maxtrinidad SSIS – Integrating PowerShell in a ScriptTask comp Are you looking at how to integrate Power Shell in an SSIS script task?   Max will show you how!


Using SSIS To Get Data From a Web Service

sqlsafety 0 Comments

The sample package outlined can be downloaded here.

SSIS has a web service control flow task that allows you to call a web service and store the results.  This can be very useful if you need to call data from a web service that will be stored in an OLTP or OLAP database.  The configuration of the web service task is pretty straight forward as all you need is:

1. The address of the WSDL
2. The service that will be called, which is provided from the WSDL
3. The method that will be called, which is also provided from the WSDL
4. A file location for the results to be stored

So let me lay out some example requirements and step through how SSIS can be used to fulfill them. First we need to get the weather based on the zip code from the web service found at:

The service, Weather, will be used and the method, GetCityForecastByZip, will be called to return the weather based on a zip code passed to the method.  The xml results from the web service need to be saved and then an XPATH query needs to be passed to return the weather forecast.

To get started we will use a web service control flow task and configure it to use the defined WSDL.  From the web service configuration pane create a new HTTP connection:

From within the connection manager pane enter the WSDL address:


Click OK to go back to the web service configuration panel and define where the WSDL file should be stored in the file system.  There are several ways to generate the WSDL file:

1. Copy and paste the web service address into a web browser and then copy and paste the xml into notepad and save it in the location you wish to point at.
2. Create an empty file with the name you choose and the extension of “wsdl”.  Point the WSDL towards that file and once done click the Download WSDL button


Once the WSDL location has been set the Download WSDL button is enabled and clicking this will call the web service and save the WSDL in the file location specified.  In order to download the WSDL you MUST insure that the OverwriteWSDLFile set to True otherwise an error will be thrown.

With the connection and WSDL defined we can now define the service and method to be called from the Input pane.  Both the Service and Method are read form the WSDL file defined in the previous step and both are drop downs.  If there are no available services or methods then there is an error in the WSDL file and it should be recreated or re-downloaded.  In this example we will be using the Weather service and GetCityForecastByZip method.



Once the method is selected the parameters accepted by the method are exposed in the pane underneath the service/method configuration pane.  In this case there is a single parameter, zip, that is accepted.  The value passed can either be statically entered or provided within an SSIS variable.  For this example go ahead and use “33914” so we can see what the forecast is for sunny Cape Coral Florida.


The following steps have completed:

1. The web service address, WSDL
2. The service to call
3. The method to call
4. Value(s) of any accepted parameters

We now need to configure where the xml results will be stored by configuring the Output.  The output can be stored in either a variable or file.  Storing in a file requires that a file connection manager be created, this is a very straight forward task and for brevities sake I will use an existing file connection manager, C:\SSIS\Weather.xml

The web service task configuration is completed and we can validate its configuration by executing the package. If all complete successfully then the results should look similar to this:
With our results populated the next step is to use an XML task to pass an XPATH query to return the forecast. Reviewing the structure of the xml results the path for the forecast would be passed like this “/ForecastReturn/ForecastResult/Forecast[1]/Desciption”. Before configuring the XML task first create a variable called forecast that is a data type of string that will be used to hold the forecast from the XML task:

The XML task needs to be configured to:

1. OperationType of XPATH
2. SourceType file connection
3. Source weather.xml
4. DestinarionType of Variable
5. Destination User::forecast
6. OverwriteDestination of true
7. SecondOperandType Direct Input
8. SecondOperand /ForecastReturn/ForecastResult/Forecast[1]/Desciption
9. XPATHOperation of Values

This should provide the steps necessary to capture the first forecast description, PLEASE note that the element in the xml is misspelled, Desciption, and assign it to our variable.  To display this value we can use aVB.NET script task that will set the forecast variable to ReadOnly and then in the code use a MessageBox Show method to display the variable:


The package should resemble this:


By executing the package all tasks should complete successfully and finally an empty message box should pop up.


What?!?!  What went wrong?!?  The issue that arises is the namespaces that are within the xml results.  The XPATH query doesn’t fail, it just doesn’t return any results because of these namespaces.  Depending on the formatting of the results this may not be a problem, but I intentionally picked these results to demonstrate the ability to remove the namespaces within the results.  After some time searching I found an AWESOME post on using C# to remove namespaces here.  Thank you Kirk Evans!!

So I will add another script task that will

1. Read my weather.xml file
2. Remove the namespaces
3. Rewrite the file without the namespaces to C:\SSIS\weathernew.xml
With only minor changes made to the script from Kirk the code should look like this:

public void Main()
XmlTextReader reader
= new System.Xml.XmlTextReader(“C:\\SSIS\\weather.xml”);

XmlTextWriter writer = new
System.Xml.XmlTextWriter(“C:\\SSIS\\weathernew.xml”, System.Text.Encoding.UTF8);


while (reader.Read())
switch (reader.NodeType)
case XmlNodeType.Element:

if (reader.HasAttributes)
//Cannot just use writer.WriteAttributes,
// else it will also emit xmlns attribute
while (reader.MoveToNextAttribute())
if (reader.Name.CompareTo(“xmlns”) != 0)
writer.WriteAttributeString(reader.Name, reader.Value);
if (reader.IsEmptyElement)
case XmlNodeType.Text:
case XmlNodeType.CDATA:
case XmlNodeType.ProcessingInstruction:
(reader.Name, reader.Value);
case XmlNodeType.Comment:
case XmlNodeType.EntityReference:
case XmlNodeType.EndElement:

Dts.TaskResult = (int)ScriptResults.Success;

Now by pointing the xml task to the weathernew.xml file and re-running the package a message box should appear with the forecast for sunny Cape Coral Fl.!!

Whatever.  At least my package worked!

The sample package outlined can be downloaded here.

All About SSIS Variables Part 3 Variable Assignment

sqlsafety 0 Comments

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

While my last post discussed SSIS object variables this post will look at variable value assignment.  There are several ways that a variable can be assigned a value within an SSIS package:


Default Value Assignment

Once a variable is declared, scoped, and a data type defined a default value can be assigned within the Value field.  This is the value that the variable will take once it falls in scope.  This will remain the variable value until a task changes that value:



An SSIS expression can be used to assign a value to a variable which, like the Value property, is the value that will be assigned once the variable falls into scope.  In SSIS 2005-2008R2 the variable expression value had a limitation to 4,000 characters.  This could be overcome by using an expression to concatenate several together.  I outlined this technique here.


Expression Task

An expression task, first introduced in SSIS 2012, provides the ability to use an SSIS expression to assign a value to a variable once the task is execute:


Script Task

A script task provides two separate means for variable value assignment.


The first, and probably the easiest, is to list the variables for ReadOnly or ReadWrite access within the components configuration pane:


With the variable(s) being entered within the component code is written that we don’t see that handles the locking and unlocking of the variable(s) for both read and write.  The system generated code allows us to interact directly with the variables without having to first lock the variables.  For example the below VB.NET code will use the MessageBox Show method to display the value of the variable “Today” that was enabled for ReadWrite access, then change the value, and once again display the new value in a message box:

Dts.Variables(0).Value = Now.AddDays(-1)
MessageBox.Show(“The new date is ” + Dts.Variables(0).Value.ToString)

This provide quick and easy access to the variable for read and write, but does limit when the variable is locked and unlocked based on the system generated code.  For more granular control over variable locking you can utilize the VariableDispenser within your code.


Utilizing the VariableDispenser method does require more code, but again provides complete control over the locking and unlocking of variables.  DO NOT include the variable within the script configuration ReadOnly or ReadWrite property when using this method or an error will arise when you attempt to programmatically lock or unlock the variables since the components system generated code handles this.  The below VB.NET code demonstrates using the VariableDispenser to work with variable directly within a script task:

Public Sub Main()
Dim vars As Variables = Nothing
myVar As Variable

For Each myVar In vars
(“Variable value ” + myVar.Value.ToString + ” and name is ” + myVar.Name)


       For Each myVar In vars
= Now

For Each myVar In vars
(“Variable value ” + myVar.Value.ToString + ” and name is ” + myVar.Name)

Dts.TaskResult = ScriptResults.Success
End Sub

The above code uses a for each loop to iterate through all locked variables, which in this case is only the User::Today variable.  To access the variable properties and methods directly the vars(i) can be called, where “i” is the zero based indexed value of the variable.  For example, to re-assign the today variables value the following code could be used to assign the current

vars(0).Value = Now

Execute SQL Task

The execute SQL task can also be used to assign a variable a value in two different ways.

Output Parameter

Variable assignment can be done by using a query that assigns the result(s) to a placeholder of a “?“.  For example the below query would take the result of SELECT GETDATE() and assign it to the output parameter that is being held with the “?



Using the query with the placeholder alone does not complete the assignment and still requires the mapping of the variable to the output parameter which is done on the Parameter Mapping page.  On the parameter mapping you select the variable, in this case User::Today, the direction, which is output, and the ParameterName, which will be the zero based index of the value, in this case 0.  The question often comes up as to whether a named output parameter can be used rather than the indexed value and the truth is that it depends upon the connection manager.  This post outlines the different configuration methods available for output parameters.



Result Set

Another way to use an execute SQL task to assign a value to a variable is by using a result set.  This is very similar to an output parameter and only differs in how the query is written, there is no “?” place holder or parameter name, but rather just the result set type and result set mapping.  On the execute SQL configuration page result set type must be defined as None, Single row, Full result set, or XML, the type is completely dependant upon the type of result(s) that will be provided from the query:


In the Result Set pane you again map the zero based index results to the return value(s) of the query.  In this case there is only one query that will return a result so the indexed value is zero.


One benefit that you may immediately notice is that if you have multiple variables requiring assignment, such as the current date and the last date a process ran, you can do this within one single execute SLQ task using output parameters, while it would require 2 execute SQL tasks each with a single row result set. There is the possibility to utilize one execute SQL task using a result set of a full result set or possibly XML this would be overkill in such a situation and more easily accomplished with output parameters.

Data Flow Task

The data flow task can be used for variable assignment, for example using the Row Count transformation to assign the number of rows that pass between two data flow components, or a complete result set using a Recordset destination.  To go back to my previous post that covered the object variable data type I used an execute SQL task to populate the variable.  Rather than an execute SQL task I can use a data flow task that gets the FirstName and LastName columns from the Adventureworks2012.Person.Person table and sends the result set to a Recordset destination mapping my object variable, in this case the User::Names variable:


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

@SQLLive360 Count Down!!

sqlsafety 0 Comments

Only 53 days until SQL Server Live in Orlando at the Royal Pacific Resort at Universal Studios. If you are still looking for a reason to attend let me help you. Microsoft MVP  and author Edwin Sarmiento, @EdwinMSarmiento will be presenting 4 different sessions:

1. Architecting a SQL Server AlwaysOn Availability Groups Topology
2. Fundamentals of SQL Server AlwaysOn Availability Groups
3. The Transaction Log and How It Affects High Availability and Disaster Recovery
4. Troubleshoot SQL Server Performance Issues using Wait Statistics

I have sat in on several of Edwin’s sessions at SQL Saturdays and SQL Server Live and regularly read his articles on MSSQLTips.  If you are looking for insight into always on availability groups, how the t-log affects recovery, or troubleshootin performance issues then pick one of Edwin’s sessions and show up to SQL Live.

SQL Server Live 360 will be held November 17-21 at Universal Studios Royal Pacific Resort. Registration is open and you can register here.
I hope to see you all there!

All About SSIS Variables Part 2 Objects

sqlsafety 0 Comments

My last post looked at a simplistic example of using an SSIS variable in a row count transformation and then using the variable in a precedence constraint expression to direct the flow of the package.  This post will focus on the unique object variable  data type.  The object data type is the ultimate base class in .NET.  What exactly does that mean?  Object is the root of the type hierarchy, which means that everything, at its core is an object.  MSDN fully documents the object data type here as well as provides sample code here.

One of the more interesting values that can be held within an objects is a full result set, which can then be treated similar to a collection.  One thing that I want to point out is just because you can doesn’t mean you should.  Quite often I will be asked how a result set can be contained in a variable in SSIS and when I ask why I am most often told that it would be used to do complex transformations on the results in the control flow.  HOLD ON!!  That is exactly what the data flow task is for!!  In response to this I am most often told that the transformations are most easily done in a script task.  Once again there is a script component in the data flow that can act as a source, transformation, and/or destination and such complex transformations should be kept in the data flow as a best practice as well as for performance considerations.

If I haven’t scared you away already let me point out several other performance considerations.  First keep the scope of the variable limited to only what is needed, task, container, and only if necessary the package.  The memory required to hold an object will be dictated by the value(s) that are stored in the variable so extra attention is required to limit the exposure to only what is required.  Boxing and Unboxing can also carry performance costs, MSDN documents boxing and unboxing here.

Enough of the gloom and doom, let’s take a look at an object variable and how to work with it.  To properly present the demonstration let’s present the requirements of the sample package.  A result set from an execute SQL task needs to be captured within the object variable that will consist of two columns, FirstName and LastName.  The result set will then be iterated through to execute complex logic, in this case just pass the values to a script task that will present the name in a message box(I know!!  This is just a simple example though).  This package will require three variables,

1. Names                        Object

2. FirstName                    String

3. LastName                    String


With the variables created we can now begin to populate the Names variable with our execute SQL task.  The task will need to set the Result Set property to full result set and the Connection Type will use an OLEDB connection to my localhost default instance connecting to the Adventureworks2012 database.  The query is simplistic enough:

SELECT FirstName,
FROM Person.Person

We now need to define the Result Set settings in the task to specify that the results will be captured within our Names object variable.  From within the Result Set tab you must specify the zero based index value of the results to be stored in the Name property, since our query only returns a single result set this will be set to 0, and then the Variable Name of the SSIS variable that will be used to hold the result set, in this case Names.


Our package will now populate the Names variable with the results of our query and we now have several methods that we can work with this variable.

ForEach Container

The first way to work with an object is to use a For Each container and set the Enumerator to a Foreach ADO Enumerator and define the ADO object source variable to our Names variable:


Now keep in mind that the Names is an object, very much like an array or collection, so we need to take both columns, FirstName and LastName, and place them in their own variables.  This is done on the Variable Mapping page by again mapping the zero based index value of the ordinal position of the columns to the appropriate variables:


**I intentionally placed the LastName and FirstName out of order to demonstrate how the variable mapping is done based on the zero based index and not by the order in which the variables are mapped to the columns
Within the ForEach container place a script task that has ReadWriteVariables or Read Only Variables set to both the FirstName and LastName:
You may ask why access is not given to the Names variable.  The answer is that the ForEach container accesses the Names object variable and iterates though each row placing the FirstName column in the FirstName variable and LastName column in the LastName variable and passes those variable, one at a time, to the script task so access is not needed to the object.
The C# script task simply calls the MessageBox Show method to display the names one at a time:

MessageBox.Show(“The name is: ” + Dts.Variables[0].Value.ToString() +” ” + Dts.Variables[1].Value.ToString());

Script Task

 Another way to work with the variable is directly within a script task.  Since the script task will now be iterating through the Names object variable directly the variable needs to be assigned to the script tasks ReadWriteVariables or ReadOnlyVariables:

The C# script task first creates an OleDbAdapter and a data table and calls the OleDbAdapters Fill method to populate a data table with the Names object variable.  Once the data table is populated a foreach loop is used to iterate through the results and display the results using the MessageBox Show method.

 //Populate a data table with the Names Variable
OleDbDataAdapter A = new OleDbDataAdapter();
System.Data.DataTable dt = new System.Data.DataTable();
A.Fill(dt, Dts.Variables[“User::Names”].Value);

// Iterate through the data table
foreach (DataRow row in dt.Rows)
string FirstName;
string LastName;
object[] array = row.ItemArray;
FirstName = array[0].ToString();
LastName = array[1].ToString();
MessageBox.Show(“FirstName=” + FirstName + ” AND LastName=” + LastName);

 The sample package outlined can be downloaded here.

I cover this method in my SSIS End to End videos from SSWUG. Registration for the videos can be found here and you can use VIP Code E2E10 to get $10 off the class.


All About SSIS Variables Part 1

sqlsafety 0 Comments

Variables have been available in SSIS since first introduced in 2005. So what exactly are they for and what can you do with variables? Glad you asked!! Variables can be used for a number of different purposes:
1. Define the path of a package
2. Hold system information, such as error information
3. Provide a means to create custom logging
4. Hold result set(s) to iterate through

Let’s begin with a simple example such as using a variable to define the path of a package. Consider that you are having to execute a data flow task and based on the number of rows loaded will dictate what task should occur next. This is simple enough to using a variable and precedence constraint. The first step is to create a variable, which is done in the variable pane in SQL Server Data Tools:




The first thing to note from the variable pane is the scope.  The scope defines where the variable will be available.  For example the scope for my variable “RowCount” is set to my package which I have named RowCount.  This means that any task within the package, including child packages, will have access to the variable.  It is best practice to insure that the variable scope is limited to only where it is needed.  This package will have a total of three tasks:

1. Data flow task that will take the data from the Adventureworks2012.Person.Person table through the Row Count transformation and to a flat file destination

2. A script task that executes if the row count is greater than 1,000

3. A script task that executes if the row count is less than 1,000

In this case the scope is set to the package since all 3 tasks will need access to the variable.  If the package contained other control flow tasks that did not require access to the variable then scope could be limited by placing the three affected tasks in a sequence container and setting the scope to the container.


Data Type

The data type defines the type of object and the acceptable values that the variable can be held.  Another important factor is to assign the most efficient data type for a variable.  For example, if the variable will hold a numeric value between -128and 128 the most efficient data type is SByte rather than UInt, which can hold a numeric value between o and 65,535.  MSDN documents the data types here.



This holds the value of the variable.  This value can be changed within the package using script task, expression task, execute SQL task, as well as several other.



The expressions provides a means of assigning a value to the variable using an SSIS expression.  Again this can be changed later within the package, but once package execution begins and the variable scope is  invoked the value or value from the expression provides the variable value until and if it is changed.

With the variable configured now I will include my data flow task that will record the value of all rows that go from the source, Adventureworks2012.Person.Person, to the destination, to the flat file destination using a row count transformation.  This is easy enough to do as once connecting the data source to the row count you will be prompted for the variable that will be used to hold this numeric value.



Precedence Constraint

Keeping in mind the requirements we now must define the path of execution based on the success of the data flow and the number of rows processed, which is now held within our variable.  By connecting both script tasks using our On Success precedence constraint we can then change both constraints to use Expression and Constraint we can define one constraint to use the expression @[User::RowCount] > 1000 and the other @[User::RowCount] < 1000.  This method will leave one path uncovered, that is if the RowCount == 1000.  In this case we are not concerned about that so we will let it ride.


Each script task will be used to access the variable and display it in a message box and then reset the variable to 0.  This requires that both tasks will need to have read and write access to the variable.


I will use VB.NET in each script task using the following code:
<style=”font-size: 12px;”>
Public Sub Main()

MessageBox.Show(“The row count was: ” + Dts.Variables(“RowCount”).Value.ToString)
Dts.Variables(“RowCount”).Value = 0
(“The row count was: ” + Dts.Variables(“RowCount”).Value.ToString)

Dts.TaskResult = ScriptResults.Success
The final package in the control flow looks like this:


You will notice that the precedence constraints are green, meaning that the path will only be taken if the preceding task completes successfully.  You will also note that there is an “fx” next to each precedence constraint which means that the constraint is also based on a precedence.

This is obviously a very simplistic example, but the first in the series so we progress with each new post.

The sample package, completed in SSDT 2012, can be downloaded here.



sqlsafety 0 Comments

A common question is what is the difference between truncating a table and deleting a table.  The short answer is that they both do the same thing, but in different ways.  So consider that you have a table that contains 1,000,000 rows and you need to remove all of the rows.

Option 1 is to use DELETE tablename.  Using DELETE with no filter will remove every row in the table as is to be expected while leaving the table in tact, but as each row is being deleted the row will be recorded in the transaction log, 1,000,000 rows = 1,000,000 records in the log.  Some other traits of DELETE are:

1. DELETE uses a row lock each row in the table is locked for deletion

2. After DELETE is run the table can still contain empty data pages

3. If the table contains an IDENTITY column the counter of that column will remain

Option 2 is to use TRUNCATE TABLE tablename which will also delete all rows in the table, but the primary difference between this and DELETE is the logging.  TRUNCATE TABLE deallocates the data pages and only logs the deallocation of the pages.  Other traits of TRUNCATE includes:

1. It always locks the table, including a schema lock, and page, but not each row

2. Without exception all pages are deallocated from the table, including empty pages

3. Once truncated an IDENTITY column is reset to the seed column defined by the table, if no seed is defined the value is set to 1

Generally TRUNCATE is more efficient then DELETE as it has reduced logging and fewer locks, but there are some limitations.  TRUNCATE cannot be used with:

1. A table that is referenced by a foreign key

2. Participate in indexed views

3. Are published using transactional or merge replication

4. Truncate will not activate a trigger, due to how TRUNCATE is logged

5. Requires ALTER TABLE permission


One common misconception is that because of the way that TRUNCATE is logged it cannot be rolled back when included in an explicit transaction and this is not the case at all.  TRUNCATE is still logged just differently than DELETE.  In order for SQL to maintain its ACID properties of a database the ability to rollback, implicitly or explicitly, must be available.

To demonstrate this review the T-SQL code below:
<style=”font-size: 12px;”>

IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘LogGrowth’)



SELECT name,
CASE recovery_model
END AS recovery_model
FROM sys.databases
WHERE name = ‘LogGrowth’;

USE LogGrowth;

IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = ‘Transact’)
col1   CHAR(50)

–INSERT 50 rows in the Transact table
INSERT Transact
VALUES(‘This is gonna be gone’);
GO 50

FROM Transact;




Now explicitly begin a transaction to TRUNCATE the table and show the row count and immediately roll the transaction back:
<style=”font-size: 12px;”>BEGIN TRAN
FROM Transact;



After rolling back the transaction you can verify that the 50 records still exist by executing another SELECT statement with COUNT(*):

FROM Transact;




The T-SQL code above can be downloaded here.

Hit Counter provided by Sign Holders