Friday, November 20, 2015

ISO8601 Date conversion with offset for T-SQL

Quick Description:  Attempting to convert a string value ISO 8601 date with a date time offset in T-SQLwill result in the error Conversion failed when converting date and/or time from character string.

When attempting to convert a string such as

   select convert(datetime, '2015-09-30T08:01:30.765-07:00')

It returns the error:

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting date and/or time from character string.
This seems odd since datetime supports ISO8601as style 126 and 127.  It doesn't, however, support the explicit full offset (in this case the -07:00, it supports the Z for UTC). 

This can be solved a number of ways, depending on your SQL Server version and what you're trying to do.

ISO8601 values with offset can't be directly converted to datetime, but they can be converted or cast to datetimeoffset (which can, in turn, be converted to datetime).

To convert a value inline for use as a datetime
   SELECT convert(datetime, CONVERT(datetimeoffset, '2015-09-30T08:01:30.765-07:00'))

To query the value directly
   select case
    when cast(getdate() as datetimeoffset) > cast('2015-09-30T08:01:30.765-07:00' as datetimeoffset) then 1 else 0
    end as Is_Today_Bigger

Thursday, October 29, 2015

Quick PowerShell Script for getting JSON.NET working with SQL 2012 Data tools on Windows 2012

Quick Description:  SQL Server Data Tools for SQL 2012 on Windows 2012 has JSON.NET installed, but can't find it at runtime.

NewtonSoft's super-awesome, saved my life JSON.NET Framework for .NET is so solid, but the SQL 2012 script component on 2012 is temperamental about remembering it's installed (when installed with NuGet.  On older versions of windows, the solution is to run a command-line GAC, but this proves difficult on Windows 2012.

What Worked for Me:
Script shamelessly (and gratefully) cribbed from this excellent TechNet article on using PowerShell to generally GAC a .dll on Windows 2012.

Download the package to the machine
Expand the package and drop the files to their final location

In PowerShell 2.0, for a script component referencing .Net 4.0 (the default for SQL 2012 Data Tools), this would be the script example.

[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")          
$publish = New-Object System.EnterpriseServices.Internal.Publish          

SQL/SSIS Data Tools Script Component Editor Doesn't Launch on SQL 2012/Windows 2012

Short Description:  When attempting to launch the script editor in an SSIS script component in SSIS Data Tools for 2012 on Windows 2012 a user in the local Admin user group gets the error Cannot show Visual Studio 2010 Tools for Applications Editor.

Full error message:  could not load file or assembly

Environment Note:  This was a SQL server dev box originally delivered without Data Tools.  It seems to be common thread with people reporting this issue that Data Tools was installed after the initial install of SQL 2012.

Things to Try First:
Try the solution in the link in this connect article first.  It solves almost everyone's problem (particularly if you are on Sql 2012 on an older OS, or migrating packages from SQL 2008 to SQL 2012 Data Tools).

What Was Different About My Environment:
My environment is Windows 2012/SQL 2012.  As of the current date, most of the reports of this issue are on Windows 2008R2/SQL 2012.

What Worked:
If after attempting to reinstall Data Tools and SSIS in the correct same order it still does not work, attempt to install the redists (below) for VSTA and the VisualStudioShell.  On 2012, even as an administrator running explorer in administrator mode this may not go well.  

The install files should be in 
  • %SQL 2012 install%\redist\VSTA\runtime\x86
  • %SQL 2012 install%\redist\VSTA\runtime\x64
  • %SQL 2012 install%\redistVisualStudioShell\VC10SP1\x86
  • %SQL 2012 install%\redist\VisualStudioShell\VC10SP1\x64
Right click the .msi file and select install (run all of these, you need both x86 and x64 for development and unit testing).  If you get a message that it can't find the file, (even though it's right there and you just clicked it, you need to make some changes).

Assuming this is a dev/sandbox, and you have administrator permissions, you just need to make a couple of changes, and then install the redistributables and you're home free!  
I also set the user account type to administrator in user accounts, but I'm not confident that had much to do with it.

If you're not an admin:
I'm reasonably confident the permissions are required for the installation, and not the launch of the script component, so if you're not an admin of the machine (I haven't tested that), it would be worth asking the admin to go through the steps.  The rest of SQL 2012 installs fine on windows 2012 even if installed after the initial install.

Thursday, June 25, 2015

SQL DatePart function equivalents for Oracle

Quick Description:  Quick example and where to find the documentation on Oracle's equivalents for DatePart and convert(datepart, datefield).

Looking for equivalents to SQL's DatePart or Convert (and then DatePart) for converting a string to a date part, I was pleasantly surprised to see that the functionality does, in fact, exist in Oracle/PL/SQL, and is relatively straightforward.  I was able to use it without messing with the NLS_DATE_FORMAT.  It has a funky syntax and is sometimes looks a lot like it belongs in a function (I think it technically is a function), but it's actually a reasonable substitute for the most basic portions of DatePart and using Convert to get a datepart from a string.  I haven't tried it across large amounts of data, but the claim is that it's written with performance in mind.  The method for a string even has less moving parts.

Kudos to whoever wrote the article at Oracle, it's very clear.  I like the little UML diagram.

A couple of examples:

To get a date part from a field that is already in the date datatype:
For the (relatively) equivalent functionality of 
SELECT DATEPART(month, MYFIELD) (Returns the month of each value in the field)
Use the syntax below to get a date part from a field that is already in the date datatype

         Returns the year of each value in the field 

To get a date part from a string that (in SQL) might need to be converted first, both can be done in one statement, but the Oracle one is cleaner.
For the (relatively) equivalent functionality of 

Returns 6

Use the syntax below to get a date part from a field that needs to be converted to date

Returns 6

Monday, June 22, 2015

Case Statement Fails Converting Varchar to Number Even Using isnumeric

Short Description:  A column containing only numbers and empty strings fails to convert using isnumeric as a condition in a case statement.  It returns Msg 8114, "Error converting data type varchar to numeric."

This statement should only attempt to convert strings that can be converted to numbers
 when isnumeric(StringValue) = 1 then (convert(numeric, StringValue))
 else StringValue
 end as ConvertStringValue
from myTable
However it fails with the error above.

Isnumeric appears to evaluate an empty string as numeric, but convert fails attempting the actual conversion.  To make the case statement work, add a condition to detect the empty string and do something with it (in this case make it null).

 when StringValue = '' then null
 when isnumeric(StringValue) = 1 then (convert(numeric, StringValue))
 else StringValue
 end as ConvertStringValue
from myTable

Wednesday, June 10, 2015

Using a Date Stamped File as a File Connection DataSource in SSIS

Quick Description:  How to set up an SSIS job to look for a file stamped with today's (or another day's) date. Also covering the difference between leading 0s/two digit day and month in SSIS expression date strings.

How To:  Create a flat file connection, initially hardcode the location of a sample file so that you can set datatypes, set the file connection's 'Connection String' property to a variable, populate the variable with an expression that pulls date parts out of the date you're looking for.

  • In a Data Flow task, create a Flat File Connection by dragging a Flat File Source into the Connection Manager pane.
  • Create a variable to hold the file name.  Right click the surface of the Data Flow task, and select Variables.
  • Open the Flat File connection, and in the File Name box, enter the name of a sample file and perform general setup of fields and datatypes
  • Create a variable of type string.
  • Close the dialog box, highlight the connection manager for the file in question, and go to the properties pane.
  • In the properties pane, select Expressions.
  • Select your variable and close the dialog.
  • To populate the variable with the correct file name, break down which date parts are in what order (year, month, day, hour, minute, second) and create the expression using the date parts as below.
  • Create a variable to hold the file name.  Right click the surface of the Data Flow task, and select Variables.

Below are examples for leading 0 and no leading 0 (always two digits regardless of whether the number is 10, vs. only one digit when under 10)

Assuming today's date is 2015-06-10 12:39
If you want the leading 0
"D:\\myFilePath\\" +
Right("0" + (DT_STR,4,1252) DatePart("yyyy",getdate()),4) +
Right("0" + (DT_STR,4,1252) DatePart("mm",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("dd",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("hh",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("mi",getdate()),2) +
Evaluates to:   D:\myFilePath\201506101239_MyFileName.txt

If you don't want leading 0s
"D:\\myFilePath\\" +
(DT_WSTR, 4)(DATEPART("yyyy", GETDATE())) +
This evaluates to:  D:\myFilePath\20156101239_MyFileName.txt

Friday, May 8, 2015

Powershell copyhere Behavior Differs between Integrated Development Environment and Integration Services Catalog

Quick Description:  in Powershell, shell.application.copyhere works in a script called by SSIS when executed from SQL Server Data Tools.  It does not work when executed from the SSIS catalog when deployed to SSIS/SQL Server.

Do not try to use shell.application copyhere as a way of unzipping a file in a powershell script called by SSIS.  It will work in the IDE, it will work executing from the Integration services catalog, and it will fail spectacularly as a sqlAgent job.  Apparently, copyhere only works when called as a script for regular files, and only works for zip files when it feels like it (I'd argue that executing from the SSIS catalog isn't exactly interactive, but apparently, I'd be wrong).