• Post Categories

  • Browse Blogs

  • Blog Stats

    • 634,118 hits
  • Syndications

    SQLServerPedia Contributor

.Net Code Camp Orlando 2013

CodeCamp

This past Saturday March 16th, I had the opportunity to speak once again the .Net Orlando Code Camp.  I presented a session under the SQL Server track titled “Change Data Capture, a developer’s best unknown friend”.  I had better than expected turn-out with about 20 attendees and overall very good participation.

I was glad to hear from more than one attendee that they were planning to use Change Data Capture in their environment and best of all to get rid of triggers!

I had the opportunity to catch-up with some of my friends and fellow SQL-ers from PASS. Also, met some new folks from the App/Dev community. These type of events are always great to expand your network, share your knowledge and learn something new!

Thanks to the organizers, sponsors, volunteers and attendees to make this event a success and for having me once more.

You can download my PowerPoint presentation and demo files at: http://sdrv.ms/15UUX64

Codeplex: SSIS Community Tasks and Components

I recently bumped into this very exhaustive list of SSIS tasks, components and samples in CodePlex http://ssisctc.codeplex.com. The list is maintained by SQL Server MVP (Blog|Twitter) and  grouped in the following sections:

  • Tools
  • Connection Managers
  •  Log Providers
  • Tasks (for Control Flow)
    • Foreach Enumerators
    • Script Task
    • Script Samples
  • Components (for Data Flow)
    • Script Component Samples
    •  Sources
    • Transforms
    • Destinations

I hope this list keeps being updated and more people contribute. Very useful.

The CASE of the missing non-NULL T-SQL Error: None of the result expressions in a CASE specification can be NULL.

Recently, while doing some data scrubbing for a customer I got an interesting error in SSMS with one of my CASE statements. The error received was:

None of the result expressions in a CASE specification can be NULL.

It was a long T-SQL script (and a long night) and could not understand the error message. After a couple minutes BinGling (Google +Bing) around the web, I still could not find the root cause, so I decided to comment out every single CASE statement and run one by one until I pinpointed the offending syntax.

 

To my surprise, the issue was very simple, yet undocumented. The T-SQL syntax I was writing was somewhat as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

END AS SerialNumber_Clean

FROM tblProduct

 

After executing this script the error mentioned above is raised. The same error is raised even if we rewrite the script as a simple CASE statement:

 

SELECT ProductID,

CASE SerialNumber

WHEN ‘0’ THEN NULL

WHEN  ‘-‘ THEN NULL

WHEN ‘Unknown’ THEN NULL

END AS SerialNumber_Clean

FROM tblProduct

 

So what if we add an ELSE statement as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

ELSE NULL

END AS SerialNumber_Clean

FROM tblProduct

 

We still get the same error. The issue as the raised error describes can be narrowed down in that you cannot explicitly return NULL for every resulting expression. There must be at least one non-explicit NUL in the resulting expression. For example, we can rewrite the script correctly as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

ELSE SerialNumber

END AS SerialNumber_Clean

FROM tblProduct

 

As you can observe, adding an ELSE statement that does not return another explicit NULL makes the script work and as a matter of fact, should be the correct syntax. Interestingly, ELSE is not necessary to make the script valid. The script will run even without an ELSE expression, but only if at least one of the resulting values is not an explicit NULL. The following script runs successfully (notice no ELSE):

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’  THEN NULL

WHEN SerialNumber = ‘No Serial’ THEN ‘Non-Serialized Product’

END AS SerialNumber_Clean

FROM tblProduct

 

Books on Line should include this caveat on their documentation. http://msdn.microsoft.com/en-us/library/ms181765.aspx

 

The following sections should be modified as follows:

THEN result_expression

Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid expression. If no else_result_expression is specified or if else_result_expression is set to return an explicit NULL, at least one result_expression has to be specified to return a non-explicit NULL.

 

ELSE else_result_expression

Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion and at least one of them should be specified to return a non-explicit NULL.

Todo lo que debes saber sobre SSIS en 1 hora!

Gracias a lo asistentes de mi charla Todo lo que debes saber sobre SSIS en 1 hora! en el evento 24HOP LATAM. La verdad me agrado mucho presentar en espanol. Aun cuando mi primera lengua es espanol, todas las presentaciones tecnicas en el pasado las he realizado en ingles.

Si deseas ver la presentacion PowerPoint puedes bajarla dando click en la imagen a continuacion.

Tambien puedes bajar el proyecto que utilize en mi presentacion dando click en la imagen a continuacion. Nota: Solo podras utilizar la base de datos y proyecto en SQL Server Denali, no servira en SQL Server 2008 o 2005.

T-SQL: Identifying, inserting and removing spaces in strings

Frequently, when working with strings you will need to identify, insert or remove spaces before, after or in between characters.

For example, you may want to show customer full names  by concatenating the columns that hold the different parts of customer names like FirstName, MiddleName, and LastName, separating each name part with a blank space. You may also want to remove spaces before, after or in between characters from email or website addresses. It is important to understand the different string functions available for you in T-SQL that may help you accomplish any of the tasks mentioned above:

Using single-quotes with space in between  ‘ ‘ versus char(32) to insert spaces between characters

Example #1:

DECLARE @FirstName varchar(25), @LastName varchar(25)
SET @FirstName = ‘Jose’
SET @LastName = ‘Chinchilla’
SELECT @FirstName + ‘ ‘ + @LastName — (one space between single-quotes)
–Result=> Jose Chinchilla

Example #2:

SELECT @FirstName + char(32) + @LastName — (single space special character represented by char(32))
–Result=> Jose Chinchilla

From these two examples we can observe that we get the same results by using single quotes with an empty space in between and char(32). The reason is because char(32) is the ASCII code value of a space character in T-SQL.

SELECT ASCII(‘ ‘)
Result=> 32
SELECT CHAR(ASCII(‘ ‘))
Result=> _  — (the underscore character ‘_’ represents a blank space for clarification purposes, no actual underscores will show)
SELECT CHAR(32)
Result=> _ — (the underscore character ‘_’ represents a blank space for clarification purposes, no actual underscores will show)

Using LEN vs DATALENGTH to get the number of characters in a string

Example #1:

DECLARE @FullName varchar(25)
SET @FullName = ‘ Jose Chinchilla ‘ — (notice spaces before and after the full name)
SELECT LEN(@FullName) –Result=>16

LEN will only count leading spaces and spaces in between the string but will not count trailing spaces.

Example #2:

SELECT DATALENGTH(@FullName)
–Result=>17

DATALENGTH will count spaces in between and both leading and trailing spaces as well.

Using LTRIM and RTRIM to remove leading and trailing spaces

DECLARE @FirstName varchar(25)
SET @FirstName = ‘ Jose ‘; — (notice space before and after the word Jose)
SELECT @FirstName
–Result=> _Jose_  — (the underscore characters ‘_’ represent a blank space for clarification purposes, no actual underscores will show) 
SELECT LTRIM(@FirstName)
–Result=> Jose_  –(only leading space is removed, the underscore character ‘_’ represent a blank space for clarification purposes, no actual underscore will show)
SELECT RTRIM(@FirstName)
–Result=> _Jose  — (only trailing space is removed, the underscore character ‘_’ represent a blank space for clarification purposes, no actual underscore will show)
SELECT LTRIM(RTRIM(@FirstName))
–Result=> Jose  — (both leading and trailing spaces are removed)

By nesting LTRIM and RTRIM you get similar results as the TRIM function in Excel and other programming languages. Unfortunately, there is no TRIM function in T-SQL.

Using CHARINDEX to find the position of all spaces in a string

In order for CHARINDEX to work properly you need to declare the data type length. For example: DECLARE @EmailAddress varchar(100) instread of DECLARE @EmailAddress  varchar. The same applies to nvarchar, char and nchar. You may use varchar(max) and nvarchar(max).

It is important to understand that CHARINDEX will only return the position of the first instance of the character you are looking for, in this case a space or char(32). To continue looking for the rest of the positions where you have empty spaces in your @EmailAddress variable you would need to loop through the entire string until no more spaces are found.

DECLARE @EmailAddress varchar(100), @SpacePositions varchar(max), @PositionIndex int
SET @EmailAddress = ‘ jchinchilla @ sqljoe.com ‘ — (notice leading and trailing spaces and spaces before & after the @ sign)
SET @SpacePositions = ”
SET @PositionIndex = CHARINDEX(char(32),@EmailAddress)
WHILE @PositionIndex > 0
BEGIN
SET @SpacePositions =
CASE
    WHEN CHARINDEX(char(32),@EmailAddress,@PositionIndex+1) > 0
THEN @SpacePositions + CONVERT(varchar,@PositionIndex) + ‘, ‘
ELSE @SpacePositions + CONVERT(varchar,@PositionIndex)
END
SET @PositionIndex = CHARINDEX(char(32),@EmailAddress,@PositionIndex+1)
END
SELECT @SpacePositions as  SpacePositions
–Result=> 1, 13, 15, 26

Using REPLACE to find and remove all spaces in a string

REPLACE will allow you to substitute all spaces in a string with an empty string, thus removing all spaces in between characters. In the following example, all spaces represented by char(32) will be replaced with an empty string ”.

DECLARE @EmailAddress varchar(max)
SET @EmailAddress = ‘ jchinchilla @ sqljoe.com ‘ — (notice leading and traling spaces and spaces before and after the @ sign)
SELECT REPLACE(@EmailAddress, CHAR(32),”)
–Result=> jchinchilla@sqljoe.com –(all spaces have been removed)

Using REPLICATE to insert ‘n’ number of spaces in a string

SELECT  ‘A’ + REPLICATE(‘ ‘,5)  + ‘Z’
Result=> A_____Z  — (the underscore characters ‘_’ represent a blank space for clarification purposes, no actual underscores will show)
SELECT  ‘A’ + REPLICATE(char(32),5)  + ‘Z’
Result=> A_____Z  — (the underscore characters ‘_’ represent a blank space for clarification purposes, no actual underscores will show)

SSIS Foreach File Enumerator returns more files than expected by appending a wildcard (*) to the file mask

While working on a recent project for a customer that involved importing both Excel 97-2003 and Excel 2007/2010 files, I was a little surprised to discover that the Foreach Loop File Enumerator will return both *.xls and *.xlsx files even if you only specify to return *.xls files.

I tested this behaviour with similar results with other file formats as well. During my testing, I created three files:

  1. File_txt.txt
  2. File_txts.txts
  3. File_txtsy.txtsy

See the screenshot of the three test files created below:

Next, I created an SSIS Package with a Foreach Loop Container with Foreach File Enumerator as the enumerator type and  specified *.txt as the file mask. The file names read by the Foreach Loop Container are being assigned to a user variable called varFileName. Then, inside the Foreach Loop Container I added a simple VB Script Task that returns the value of the user variable varFileName inside a message box.

See the Foreach Loop Containter configurations below:

See the VB script inside the Script Task below: 

You would expect only file_txt.txt to be returned, but as it turns out all three files were returned as can bee seen on the screen captures for the message boxes below:

It looks like the Foreach File Enumerator appends an asterisk (*) at the end of the extension portion of the file mask you specify. This means, that specifying *.txt or *.xls is the same as specifying *.txt* or *.xls*. As an additional test, I executed the <dir> command in D.O.S. to see the results of both masks. The two commands executed are shown below along with the results:

Interestingly, we get the same result in D.O.S. if we specify *.txt or *.txt*. I assume then, that the Foreach Loop file enumeration behaviour may be bound to the D.O.S. output of the Operating System. The current version of D.O.S. on my Windows 7 machine is 6.1.7600. With the introduction of support for more than three letter extensions, this little issue might have been overlooked in D.O.S. for the <dir> command.

The only reference I found regarding this issue with SSIS and the For Each Loop Container was by Douglas Laudenschlager (Blog), technical writer for Microsoft on the SQL Server Integration Services documentation team. The blog post where he mentions this as a gotcha can be found here: http://dougbert.com/blogs/dougbert/archive/2008/06/16/excel-in-integration-services-part-1-of-3-connections-and-components.aspx

Douglas correctly expresses,

 There appears to be no way to specify, “Give me .xls but not .xlsx”.

So, if you require to only limit your control flow execution for files with a specific extension, as in Douglas’ example .xls but not .xlsx, then your alternative will be to assign to a separate variable the extension portion of the file name contained in the varFileName variable of my SSIS package above. Once you capture the file extension in a variable, you can use a precedence constraint to restrict further control fow task execution for files of a specific file extension.

In my case, I declared a variable called varFileExtension and added the following line to the existing Script Task in my example:



I can now use a precedence constraint to check if the value contained in my varFileExtension is equal to “txt” as follows:

 By specifying a precedence constraint that evaluates for the exact file extension I’m looking to work with, I can limit the rest of the execution for only these files. Furthermore, if I wanted to have separate control flow logics for files of type “.xls” and “.xlsx” for example, I can split my control flow using two precedence constraints. In such case I could change the code inside my script to include the last 4 characters instead of just three. My two precedence constraint expressions would look something like this then:

  1. For XLS files -> @[User::varFileExtension]==”.xls” (period included since we are assigning the last 4 characters now)
  2. For XLSX files -> @[User::varFileExtension]==”xlsx”

The SSIS package would look something like this now:

 

Differences between Merge and Union All transformations in SSIS

 What are the differences between Merge and Union All transformations in SSIS ?

 The first and most obvious difference is that Merge can only accept two datasets while Union All can accept more than two datasets for input. The second difference is that Merge requires both datasets to be sorted while Union All does not require sorted datasets. Both transformations are considered to be partially-blocking.

As can be seen on the following image, Union All can accept more than two datasets as input while Merge is limited to only two input datasets. Also, notice that for the Merge transformation, the datasets need to be sorted before they can be accepted as valid input.

For simplification purposes,  a Sort transformation was used. Keep in mind that the Sort transformation is a full blocking operation because it needs to read all the rows in the data flow (buffer) in order to sort the data. No data will be sent downstream until all rows have been read first by the Sort transformation. A better approach would be to sort the datasets with an ORDER BY T-SQL clause at the data source component.

SSIS errors: Bulk Load failed. Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)

When working with the Bulk Insert Task in SSIS 2008 you may get the following error:

 The complete error message is:

[Bulk Insert Task] Error: An error occurred with the following error message: “Cannot obtain the required interface (“IID_IColumnsInfo”) from OLE DB provider “BULK” for linked server “(null)”.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.”

This error description can make you jump through hoops trying to figure out why is it detecting an error with the OLE DB provider or why does SSIS thinks you are trying to execute this operation on a linked server?

The real issue here has nothing to do with the first  sentence in the error description. Sentence 4 gives you the actual error:

Verify that the field terminator and row terminator are specified correctly.

You may be experiencing this error due to one or more of the following 3 reasons:

1) You may be specifying a wrong CommonDelimiter for your source file.
For example, You may be trying to do a Bulk Insert operation from a Comma Separated Value (CSV) file but did not change the CommonDelimiter property to Comma {,}. When you drag in the Bulk Insert Task the CommonDelimiter property default value is Tab.

2) You may be specifying a wrong RowDelimiter for your source file.
For example, you may be trying to do a Bulk Insert operation from a Comma Separated Value (CSV) file whose row delimiter character is different than the RowDelimiter property default value of {CR}{LF}. In some cases, you may receive a file with a very long stream of text with no Carriage Return (\r)  & Line Feed (\n) characters, commonly denoted as {CR}{LF} in between rows. These hidden {CR}{LF} row delimiter characters are placed on a text file each time you hit the ENTER key on your keyboard denoting the end of a row and beginning of the next row. You may read a little more about the Carriage Return and Line Feed characters in Pinal Dave’s blog: http://blog.sqlauthority.com/2009/07/01/sql-server-difference-between-line-feed-n-and-carriage-return-r-t-sql-new-line-char/

As seen on the image below, there are two properties,  CommonDelimiter and RowDelimiter, that you need to make sure  you specify the correct values for depending on your input or source file format:

.
3) You may be using a format file with an incorrect or invalid format defined.
Format files can be non-xml, commonly with an *.fmt extension or for SQL Server 2005 and later only you can also use xml format files. For more information about format files read MSDN Books on Line http://msdn.microsoft.com/en-us/library/ms191516.aspx

If you are using a format file make sure you are pointing to the right format file or that the format defined in your format file is correct. (Notice that the Format property value changes from Specify to Use File):

Phoenix User Group Presentation: 0 to SSIS

Thanks to the Phoenix User Group for hosting me tonight! The presentation went smooth and had good questions at the end. I will present anytime you need me and hopefully make it to Phoenix sometime soon.

Cheers my fellow Phoenix friends!

Phoenix User Group 2/9/2011 meeting attendees

Presenting at inaugural meeting for Wheeling, West Virginia User Group

I will be presenting my “Learning SSIS under 1 hour” session over live meeting for the Wheeling, West Virginia User Group on Thursday 1/20/2011. This meeting will kick-off the first meeting for the local user group put together by John Sterrett (Blog | Twitter). More details can be found at:

http://johnsterrett.com/2011/01/06/new-sql-user-group-in-wheeling-west-virginias-first-meeting-is-january-20th/