SQL: Converting Column Datatype from DateTime to Uniqueidentifier

Another day for weird annoying things.

I had a table on which I'd inadvertantly set a columns datatype to datetime when it should have been uniqueidentifier. The table was empty.

Resetting the datatype via script won't work due as SQL knows it can't implicitly convert the (nonexistant) data. So I tried using the GUI to change the datatype and I got incompatible operand errors. Tried doing an ALTER TABLE command and got the same error. Finally I tricked the little bugger by converting it to varchar first then to uniqueidentifier. Boom! Done. Sorted.

SSIS Doesn't Play Nice with Special Characters

Oh SSIS you cantankerous old thing, you! I spent an hour today adding cleansing tasks into several data flows to replace special characters with other values ie "" to "-" and "[" to "(" etc. Only to have the content of those dataflows disappear when I closed then reopened the saved package. When I say disappear, I mean empty. Completely. Devoid of all steps. An empty shell.

Only dataflows which had the new special character cleansing step were empty - the rest were fine. This placed the blame squarely on the new steps. I should have twigged when I attempted to copy the step from one dataflow to another and got an error - something about HEX values being invalid. Stupid me, blindly continued creating them manually in each dataflow. Once done, I saved and closed. Then when I reopened, those dataflows were completely empty.

Fortunately being a concientous backer-upper-er-er, I had the previous version stored elsewhere. Sans special character cleansing of course.

So first, why do I need to do cleansing at all? These characters have passed through SSIS into the staging database with no problems. They have also passed through SSIS into the datamart database with no hiccups. Well the problem comes when I process the dimensions which use the datamart tables as their source. Then I get errors.

So, for whatever reason, cleansing ahoy!
But SSIS won't do it, as proven by my frustrating waste of an hour.
I tried using them directly in my source query with the same result. So I created udf_CleanseSpecialCharacters to handle it.


/**********************************************************************************************************************************************
HEADER
/***********************************************************************************************************************************************
FN Name: dbo.udf_CleanseSpecialCharacters
Author: Kristen Hodges
Date: 14 October 2010

-------------------------------------------------------------------------------------------------------------------------------------------
Purpose:
--------------------------------------------------------------------------------------------------------------------------------------------
Return a given string, cleansed of special characters

--------------------------------------------------------------------------------------------------------------------------------------------
OVERVIEW
--------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------
NOTES
--------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------
ASSUMPTIONS AND BUSINESS RULES
--------------------------------------------------------------------------------------------------------------------------------------------
It may be desirable in certain contexts to retain some characters whereas in others it is not
eg comma stored in table but not as a ssas dimension member unique key
The @iOptional_CharactersToCleanseList is an optional parameter which can be used to pass in a string of characters which should be stripped and
any others will be retained

The @iStripCharacters is a true/false flag which will strip the characters if true
or if false, the value will be replaced with particular values eg [ becomes -

--------------------------------------------------------------------------------------------------------------------------------------------
USAGE
--------------------------------------------------------------------------------------------------------------------------------------------
Called by:

Sample USage:
select dbo.udf_CleanseSpecialCharacters('John&kathy@somedomain.com.au',1,0,NULL)
select dbo.udf_CleanseSpecialCharacters('John&kathy@somedomain.com.au',1,1,NULL)
select dbo.udf_CleanseSpecialCharacters('John&kathy@somedomain.com.au',1,1,'&')

--------------------------------------------------------------------------------------------------------------------------------------------
CHANGE LOG
--------------------------------------------------------------------------------------------------------------------------------------------
AUTHOR DATE CHANGE
KHODGES 2010-10-14 Created

************************************************************************************************************************************************/

************************************************************************/

ALTER FUNCTION [dbo].[udf_CleanseSpecialCharacters](
@iStringToCleanse varchar(500)
,@iStripCharacters bit
,@iStripAllSpaces bit
,@iOptional_CharactersToCleanseList varchar(50)
)
RETURNS varchar(500) AS BEGIN

DECLARE @return varchar(500), @CurrentChar char(1), @CurrentReplacement varchar(10), @posn int

SET @return = @iStringToCleanse

set @CurrentChar = CHAR(5) --
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(58) -- :
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(59) -- ;
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(91) -- [
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(93) -- ]
set @CurrentReplacement = ''
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(37) -- %
set @CurrentReplacement = ''
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(38) -- &
if @iStripCharacters = 0 set @CurrentReplacement = '' else begin
set @CurrentReplacement = ' and '
IF @iStripAllSpaces = 1 set @CurrentReplacement = replace(@CurrentReplacement,' ','-')
end
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(124)-- |

if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = '-'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(44) -- ,
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = SPACE(1)
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(36) -- $
set @CurrentReplacement = ''
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(35) -- #
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = 'No.'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(35) -- #
if @iStripCharacters = 0 set @CurrentReplacement = '' else set @CurrentReplacement = 'No.'
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(64) -- @
if @iStripCharacters = 0 set @CurrentReplacement = '' else begin
set @CurrentReplacement = ' at '
IF @iStripAllSpaces = 1 set @CurrentReplacement = replace(@CurrentReplacement,' ','-')
end
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)

set @CurrentChar = CHAR(46) -- .
if @iStripCharacters = 0 set @CurrentReplacement = '' else begin
set @CurrentReplacement = ' dot '
IF @iStripAllSpaces = 1 set @CurrentReplacement = replace(@CurrentReplacement,' ','-')
end
IF PATINDEX('%'+@CurrentChar+'%',@iOptional_CharactersToCleanseList) >= 1 OR @iOptional_CharactersToCleanseList IS NULL
SET @return = REPLACE(@return,@CurrentChar,@CurrentReplacement)


IF @iStripAllSpaces = 1 SET @return = REPLACE(@return,' ','')

RETURN @return
END

TSQL: Programmatically Create DDL scripts for Foreign Keys

I'll admit it, I'm lazy... I hate typing exercises. I don't need the practice and I'm sure the person paying my somewhat exhorbitant fee doesn't want to fork out for something so mundane either. That means I'm always on the hunt for a shortcut or two, I mean, process improvement.

These two snippets will generate the DDL to create all the foreign keys in the database.
The first will check if it exists first, the second drops it first. Use whichever suits.

SELECT 'IF NOT EXISTS (SELECT * FROM sys.FOREIGN_KEYS WHERE name = ''' + fk.Name + ''' AND Parent_Object_Id = object_id(''['+ s.name + '].[' + t.name + ']'')) BEGIN '
+ char(13) + char(9) + 'ALTER TABLE ' + s.name + '.' + t.name + ' WITH CHECK '
+ char(13) + char(9) + 'ADD CONSTRAINT ['+fk.Name +'] '
+ char(13) + char(9) + 'FOREIGN KEY(['+c.name+']) REFERENCES [' + fks.name + '].['+fkt.name+'] (['+fkc.name+']) '
+ char(13) + 'END'
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkcm on fkcm.constraint_object_id = fk.object_id
inner join sys.tables t on t.object_id = fkcm.Parent_Object_Id
inner join sys.tables fkt on fkt.object_id = fkcm.Referenced_Object_Id
inner join sys.columns c on c.object_id = t.object_id and c.column_id = fkcm.parent_column_id
inner join sys.columns fkc on fkc.object_id = fkt.object_id and fkc.column_id = fkcm.referenced_column_id
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.schemas fks on fks.schema_id = fkt.schema_id
ORDER BY 1

And the second one (ie does a drop first)...

SELECT DISTINCT 'IF EXISTS (SELECT * FROM sys.FOREIGN_KEYS WHERE name = ''' + fk.Name + ''' AND Parent_Object_Id = object_id(''['+ s.name + '].[' + t.name + ']'')) BEGIN '
+ char(13) + char(9) + 'ALTER TABLE ' + s.name + '.' + t.Name + ' DROP CONSTRAINT ' + fk.Name
+ char(13) + 'END'
+ char(13) + 'ALTER TABLE ' + s.name + '.' + t.name
+ char(13) + char(9) + 'WITH CHECK ADD CONSTRAINT ['+fk.Name +'] '
+ char(13) + char(9) + 'FOREIGN KEY(['+c.name+']) REFERENCES [' + fks.name + '].['+fkt.name+'] (['+fkc.name+']) '
+ char(13)
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkcm on fkcm.constraint_object_id = fk.object_id
inner join sys.tables t on t.object_id = fkcm.Parent_Object_Id
inner join sys.tables fkt on fkt.object_id = fkcm.Referenced_Object_Id
inner join sys.columns c on c.object_id = t.object_id and c.column_id = fkcm.parent_column_id
inner join sys.columns fkc on fkc.object_id = fkt.object_id and fkc.column_id = fkcm.referenced_column_id
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.schemas fks on fks.schema_id = fkt.schema_id
ORDER BY 1

SQL 2008: Using MERGE to do a Type 2 Change

I'm going to do a brief overview of the SQL 2008 MERGE statement. Primarily for my own purposes - being to document the usage for type 2 changes so I don't forget it.

First let's begin with the basic type 1 usage.


All fairly self-explanatory. We have a source and a target. We join the two tables, identify new records, changed records and deleted records. And deal with them appropriately.

So let's move onto the type 2 changes. Here we need to:

• Insert New Records
• Disable changed records
• Add a new record for the changed records
  

That's 2 inserts and an update. The MERGE won't allow that so we have to nest our merge inside an insert. Luckily this works because MERGE allows output!

Ignore the outer INSERT for the moment. We have a MERGE statement which is very similar to our type 1 MERGE. Compare the Source and Target, INSERT new records as required. The difference being that when the matched record contains a change, instead of updating the changed field, we set the CurrentFlag and the EndDate so as to disable the record.

Of course that means we still don't have a record which contains our updated values. So, now lets turn our attention to the OUTPUT clause.

This will be familiar to anyone who used it in SQL 2005 in much the same context - to identify rows which have been modified/added/deleted. Really the origin of MERGE seems to be strongly rooted in the SQL 2005 OUTPUT clause. On the upside, unlike the SQL 2005 version and UPDATE is an UPDATE and not a DELETE and an INSERT. Small but handy! Anyway, where were we? Let's return the columns we need to create a new record and a special $Action column - this tells us what actually happened ie UPDATE, INSERT or DELETE. We need this because the OUTPUT will return everything that occured in the MERGE.


Now we have a set of rows that have been actioned by the MERGE. Still, we haven't actually done anything with them.

To do that, we enclose the entire MERGE statement, including it's OUTPUT clause of course, into brackets so we can make it the FROM clause for an INSERT. Our OUTPUT clause is return the columns we need for the INSERT remember? So now we just add our INSERT INTO at the front, add an alias to the closed bracket of the FROM clause AND, drumroll... whack a WHERE clause on the end. This where clause just filters the OUTPUT from the MERGE so we only insert new records for those that were updated, ignoring those that may have been inserted etc in the MERGE.

Easy!


-------------------------------------------------------------------------------------------------

For the purposes of copy/paste-ability, here is a text version of the TSQL. Images have been used above for improved readability.

--A simple type 1 change
MERGE INTO DimCustomer c
USING StageCustomer stg ON c.CustomerId = stg.CustomerId
WHEN NOT MATCHED BY TARGET THEN --ie new
INSERT (CustomerId,CustomerName,IsActive) VALUES (
stg.CustomerId
,stg.CustomerName
,stg.IsActive
)
WHEN MATCHED AND c.CustomerName <> stg.CustomerName THEN --ie changed
UPDATE SET c.CustomerName = stg.CustomerName
WHEN NOT MATCHED BY SOURCE THEN --ie deleted
DELETE


--a type 2 change
INSERT INTO dbo.DimCustomer
SELECT CustomerId, CustomerName, IsActive, IsCurrent, Eff_Date, End_Date
FROM
( MERGE DimCustomer c
USING StageCustomer stg ON c.CustomerId = stg.CustomerId
WHEN NOT MATCHED THEN INSERT VALUES (
stg.CustomerId
,stg.CustomerName
,stg.IsActive
,1 --> IsCurrent
,GETDATE() --> Eff_Date
,NULL --> End_Date
)
WHEN MATCHED AND c.IsCurrent = 1 AND (c.CustomerName <> stg.CustomerName) THEN UPDATE SET
c.IsCurrent = 0
,c.End_date = GETDATE()
OUTPUT $Action as RowAction
,stg.CustomerId
,stg.CustomerName
,1 AS IsCurrent
,GETDATE() as Eff_Date
,NULL as End_Date
) m
WHERE m.RowAction = 'UPDATE'

SQL SSIS: Vote for data viewer suggestion

Vote for a feature request on Microsoft Connect for greater flexibility over when and where an SSIS data viewer is shown.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=465835

SQL: Retrieving the Metadata for a Query

Sometimes you need to know what the metadata for a query looks like before runtime. This is particularly relevant with SSIS.

I use it rarely but every time I do have a need for it, I can never remember the name of the option and it takes me an hour at least to track it down again. So I'm hereby cementing it in stone so I don't forget it again!

SET FMTONLY ON

That's the one. It will return the columns and data types to the query engine. Note: it does not actually run the query or return any results. It's purely to return metadata about the query.

It's surprisingly handy from time to time.