Monday, April 14, 2014

T-SQL Tips: Swap The Year Value of a Date

Here's a quick T-SQL tip for you.
Suppose you want to swap the year part of a date value in SQL server. Here's a quick and simple way in which you can do it.
Lets consider the below example

DECLARE @DateValue datetime ,@SwapYear int
SELECT @DateValue = '20140122',@SwapYear = 2000

So in the above case the requirement is to swap year value of datevalue which is 2014 with the passed year value ie 2000
This can be done using the below query

SELECT DATEADD(yy,@SwapYear - YEAR(@DateValue),@DateValue)  AS YearSwappedDate

Run this in SQL management studio and you can see result as below


Which clearly indicates that query returns date with year value swapped.
This is particularly useful when we want to determine birth date of a person on a year given the date of birth. Hope you find this tip useful.
Do let me know any comments/clarification you've on the above.

Sunday, April 13, 2014

T-SQL Tips: Get The Weekday Number of the Month

I've added a new utility function in Technet Wiki to retrieve the number of week day for the month for the date passed ie say 13 Apr 2014 is 2nd Saturday,15th jan 2013 is 3rd Tuesday  etc. Do check it out from below link and let me know any comments / clarification you may have on it.

http://social.technet.microsoft.com/wiki/contents/articles/23839.t-sql-get-weekday-number-of-the-month.aspx


Tuesday, April 1, 2014

SSIS Tips: Implementing Conditional Logging

The impetus for this post comes from a recent conversation I had with one of my colleagues. He asked me whether we could implement logging conditionally for a SSIS package. I told it should be possible keeping in mind that we will be able to use expression builder for the same. Anyways I wanted to see for myself how this can be implemented so thought of doing a sample to illustrate it which I'm sharing through this blog.
The package for the illustration looks like below
I wont be going into details of main package here as thats not the purpose of this article. From the logging perspective this is what I've done

1. Added two variables inside package as follows
             a. FileExists - Boolean type to indicate whether file path exists or not
             b. LogFilePath - String variable to store the full file path.
2. Configured Logging in the package using SSIS log provider for text files.
3. Made the connection string for the logfile dynamic based on FilePath variable using expression builder

4. Added the variable to the configuration to set values from outside
5. Added a script task as the first task in the package with LogFilePath variable passed in read only mode and 
FileExists in read write. The code block will be simple as below


Public Sub Main()
'
' Add your code here
        '
        Dts.Variables("FileExists").Value = IIf(Len(Dir(Dts.Variables("LogFilePath").Value)) > 0, True, False)
Dts.TaskResult = ScriptResults.Success
End Sub

What this does is to check the existence of the passed file path and sets boolean variable value accordingly ie True if file path exists else false. This variable would be used for setting the conditional value for logging in the package
6. Now we need to set conditional expression for logging property within package. For this right click anywhere on package body and select properties to launch property window on right side. Inside that set an expression for Logging property as shown

One thing to note here is that though the values you see for LoggingMode property are Enabled,Disabled etc they're just friendly names. While setting property through expressions you need to set the values as 0,1,2 etc as these are the actual values within the enumeration DTSLoggingMode used for setting the Logged property value. This is specified in the below link

Now that you've set up the expression for the LoggingMode property you can try running the package.
First set a valid file path for the variable and try running the package and you can see log file within the specified path.

Now change the value to a non-existent path and you can notice that the package will still run without any failure but with no file created for the logging data.
This provides an effective  method to implement conditional logging in SSIS packages.

Monday, March 24, 2014

Excel Tips: Issue with CSV Data Merging Inside Single Column in Excel

This is a really quick tip in Excel while opening CSV files
You've different methods available for the data export to csv like bcp,OPENROWSET etc.
One issue that I came across recently in this was the CSV file after getting generated, if you try to open it in excel all column values gets wrapped inside same cell in excel as below.


This was not what I expected as I always thought Excel would render CSV format properly onto different columns.
I sent the sheet to my colleague and thought I would clarify this issue with him to see if he has any ideas. But to my surprise I found sheet being displayed properly in his machine. This gave me an indication that its something to do with client machine configuration. I did some analysis and found out the solution.
Apparently regional settings of my machine had list separator set as ; as per below
I changed it to comma character (,) and then it worked as intended.

So key thing to understand is it uses this setting to identify column separator within the sheet.

Thursday, March 13, 2014

Checking for Integer or Decimal values using ISNUMERIC

Thanks to Erland I learned a cool way to validate integer or decimal values from varchar field having intermixed data which I'm sharing through this blog.
ISNUMERIC was a function which was always thought to be unreliable due to various formats in which it accepts values. There are lot of values for which it will return true even when value may not be purely numeric.Refer http://beyondrelational.com/modules/2/blogs/70/posts/19819/understanding-how-isnumeric-function-works.aspx
 But with the below discussed approach we can make it work based on our expectation to return only the valid numeric data.

Consider the below sample data for illustration

declare @t table
(
val varchar(20)
)

insert @t
values ('123'),
('161.65.65.6'),
('756.76'),
('yrytrt656757'),
('987'),
('yfrytr'),
('7656@546'),
('wrew235235dsnfgngf'),
('213d3')

Suppose if the requirement is to get valid integer values from it, then we can apply a logic like below for that

SELECT val
FROM @t
WHERE ISNUMERIC(val+'0.0E0') =1

See the output below

The technique is to make it in scientific format and check if decimal portion is 0.

Similarly to check for decimal format from above we can write condition as below


SELECT val
FROM @t
WHERE ISNUMERIC(val+'E0')=1

This will just check if its in valid scientific format and return the values
See output
This method is very cool and is much better than writing a series of conditions to account for all valid combinations to return valid numeric data.