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).

Thursday, May 7, 2015

SSIS Attunity Oracle Source SQL Command from Expression Difficulties

Short Description:  When attempting to use a variable/expression to populate the SQLCommand, the expressions property is not showing up when looking at source properties.

The post title is a mouthful, but the upshot is that I ran into the unfortunate situation in which, when attempting to use a variable as the SQL Command for an Attunity Oracle source, the expressions menu wasn't showing up in the Oracle Source properties.

I'm not sure if this is always the case.  It certainly seems that the expectation is that the expressions property will show up under 'Misc Properties' per a number of posts like this one If you're not specifically having trouble setting a variable as the SQL Command because the expression properties are not available, you may want to start with this post, it will answer the basic questions around setting the query from an expression.

Solution:  If you're stumped because you're used to setting variable expressions as the query for data sources, but you can't find where to enter the expression, then we have/had the same problem.  Right clicking the Oracle Source and combing through the properties comes up blank for misc properties and the expressions property in particular.

In this case, to get to the expressions property of the source,

  • Right click the working surface of the data flow task 
  • Click on the ... icon in the Expressions property of the data flow task.
  • The Property Expressions editor will come up
  • Click the drop-down arrow in the Property drop down
  • Select the [Oracle Source].[SQLCommand] property, and enter your variable.
I know...this would be easier to follow with screenshots.  I hope to pull some together when I have a chance.

Friday, April 24, 2015

SSIS Flat File Text Showing Up in Quotes Although Column is Set to Qualified

Short Description:  An SSIS package using a flat file datasource with columns set as TextQualified = true come through showing the text qualifier (values are in quotes or some other qualifier) in the destination (database, another flatfile, etc).

Problem:  This is just a case of a two part setting being in two oddly different places.  A column is set to TextQualified under Connection Manager Editor, Advanced, Column Name, TextQualified (true).  The Text Qualifier itself is set under The file connection's properties.  Text pulled from a flatfile will still show up in quotes (or surrounded by any other qualifier) even though TextQualified=true has been set.

Solution:  In addition to enabling the TextQualified setting at a column level.  The qualifier has to be specified at the connection properties level.

To set the Text Qualifier:

  • In SQL Server Data Tools.
  • Right click the Connection Manager and select Properties
  • In the Properties pane on the right hand side, under Misc
  • Enter your Text Qualifier (e.g. ") in the TextQualifier property.

Wednesday, April 22, 2015

SSIS Code Page Error Moving Flat File to OLE DB destination

Short Description:  When creating an SSIS dataflow moving data from a flat file directly to an OLE DB destination, the OLE DB destination throws a code page inconsistency error.

Problem:  An OLE DB destination that is fed by a Flat File Source which is in turn fed by a Flat File connection throws an error "Error 2 Validation error. Stage Sent Data: Stage Sent Data: The column "MyColumn" cannot be processed because more than one code page (65001 and 1252) are specified for it."  SQL server is by default Code Page 1252, and Unicode is 65001 for a text file. if you don't put the output columns through a Data Conversion transform before they get to the OLE DB destination, they will fail with this error.

Work Around:  I say work around because changing the Code page of the flat file connection does not actually result in the Code page staying saved, but it does result in the OLE DB destination accepting the input.

To Work Around:

  • Open the Flat File Connection, and change the Code Page from '65001 (UTF-8)' to '1252 (ANSI - Latin I)'
  • Click OK to save
  • Open the OLE DB Destination which is showing the error.
  • Close the OLE DB Destination which is showing the error.
  • The OLE DB Destination should now, now longer show the error.
  • For kicks, look at the Flat File Connection, and see that the Code Page is back to '65001 (UTF-8)'