SSMS copy and paste from results grid losing CR+LF - SQL Server 2016

by Darren Green 6 jun 2016 22:37
When you start using SQL Server Management Studio (SSMS) for SQL Server 2016, you may notice a new bug feature to do with the Results Grid. If you copy and paste one or more cells from the results grid the copied text loses any carriage returns (CR) and line feeds (LF). To avoid too much head scratching, just remember there is a new setting available. You can access this via Options dialog from the Tools menu (Tools –> Options). Navigate to the Query Results –> SQL Server –> Results to Grid section as shown below. The offending item is the Retain CR/LF on copy or save, which you proba... [Más]

File Watcher Task

by Darren Green 6 jun 2016 16:07
The task will detect changes to existing files as well as new files, both actions will cause the file to be found when available. A file is available when the task can open it exclusively. This is important for files that take a long time to be written, such as large files, or those that are just written slowly or delivered via a slow network link. It can also be set to look for existing files first ( The full path of the found file is returned in up to three ways: The ExecValueVariable of the task. This can be set to any String variable. The OutputVariableName when specified. T... [Más]

Checksum Transformation

by Darren Green 6 jun 2016 00:00
The Checksum Transformation computes a hash value, the checksum, across one or more columns, returning the result in the Checksum output column. The transformation provides functionality similar to the T-SQL CHECKSUM function, but is encapsulated within SQL Server Integration Services, for use within the pipeline without code or a SQL Server connection. As featured in The Microsoft Data Warehouse Toolkit by Joy Mundy and Warren Thornthwaite from the Kimbal Group. Have a look at the book samples especially Sample package for custom SCD handling. All input columns are passed through the tr... [Más]

RegexClean Transformation

by Darren Green 6 sep 2014 16:03
Use the power of regular expressions to cleanse your data right there inside the Data Flow. This transformation includes a full user interface for simple configuration, as well as advanced features such as error output configuration. Two regular expressions are used, a match expression and a replace expression. The transformation is designed around the named capture groups or match groups, and even supports multiple expressions. This allows for rich and complex expressions to be built, all through an easy to reuse transformation where a bespoke Script Component was previously the only alter... [Más]

Regular Expression Transformation

by Darren Green 6 sep 2014 16:01
The regular expression transformation exposes the power of regular expression matching within the pipeline. One or more columns can be selected, and for each column an individual expression can be applied. The way multiple columns are handled can be set on the options page. The AND option means all columns must match, whilst the OR option means only one column has to match. If rows pass their tests then rows are passed down the successful match output. Rows that fail are directed down the alternate output. This transformation is ideal for validating data through the use of regular expressio... [Más]

Row Count Plus Transformation

by Darren Green 6 sep 2014 16:00
As the name suggests we have taken the current Row Count Transform that is provided by Microsoft in the Integration Services toolbox and we have recreated the functionality and extended upon it. There are two things about the current version that we thought could do with cleaning up Lack of a custom UI You have to type the variable name yourself In the Row Count Plus Transformation we solve these issues for you. Another thing we thought was missing is the ability to calculate the time taken between components in the pipeline. An example usage would be that you want to know how ma... [Más]

Row Number Transformation

by Darren Green 6 sep 2014 15:56
The Row Number Transformation calculates a row number for each row, and adds this as a new output column to the data flow. The column number is a sequential number, based on a seed value. Each row receives the next number in the sequence, based on the defined increment value. The final row number can be stored in a variable for later analysis, and can be used as part of a process to validate the integrity of the data movement. The Row Number transform has a variety of uses, such as generating surrogate keys, or as the basis for a data partitioning scheme when combined with the Conditional ... [Más]

Trash Destination Adapter

by Darren Green 6 sep 2014 15:41
The Trash Destination and this article came from early experiences of using SSIS and community feedback at the time. When developing a package it is very useful to have a destination adapter that does nothing but consume rows with no setup requirement. You often want run a package part way through development, or just add a path so you can set a Data Viewer. There are stock tasks that can be used, but with the Trash Destination all columns are treated as selected automatically (usage type of read-only), so the pipeline knows they are required. It is also obvious that this is for development or... [Más]

Trace File Source Adapter

by Darren and Allan 6 sep 2014 15:39
The Trace File Source adapter is a useful addition to your SSIS toolbox.  It allows you to read profiler traces stored as .trc files and read them into the Data Flow.  From there you can perform filtering and analysis using the power of SSIS. There is no need for a SQL Server connection this just uses the trace file. Example Usages Cache warming for SQL Server Analysis Services Reading the flight recorder Find out the longest running queries on a server Analyze statements for CPU, memory by user or some other criteria you choose Properties The Trace File Source adapter has... [Más]

0xC0017011 and other error messages - what is the error message text?

by Darren Green 9 nov 2012 19:37
Recently there was a bug raised against BIDS Helper which originated in my Expression Editor control. Thankfully the person that raised it kindly included a screenshot, so I had the error code (HRESULT 0xC0017011) and a stack trace that pointed the finger firmly at my control, but no error message text. The code itself looked fine so I searched on the error code but got no results. I’d expected to get a hit from Books Online with the Integration Services Error and Message Reference topic at the very least, but no joy. There is however a more accurate and definitive reference, namely the heade... [Más]

Upgrading SSIS Custom Components for SQL Server 2012

by Darren Green 12 jun 2012 05:59
Having finally got around to upgrading my custom components to SQL Server 2012, I thought I’d share some notes on the process. One of the goals was minimal duplication, so the same code files are used to build the 2008 and 2012 components, I just have a separate project file. The high level steps are listed below, followed by some more details. Create a 2012 copy of the project file Upgrade project, just open the new project file is VS2010 Change target framework to .NET 4.0 Set conditional compilation symbol for DENALI Change any conditional code, including assembly versio... [Más]

Data Generator Source Adapter

by Darren Green 5 jun 2012 14:19
This component needs little explanation. It generates random integer (DT_I4) and string (DT_WSTR) data and places them in the pipeline. You specify how many columns of each you would like and for any string columns you pass a fixed length value. You then need to specify how many rows in total you require to be generated. This component is used by us to do testing of the pipeline and components downstream. Previously we would have used a script component (as a source) to generate the rows but found ourselves rewriting the code too often so created this component. Screenshots ... [Más]

Red Gate join the SSIS custom component club

by Darren Green 11 jul 2011 08:11
I recently noticed that Red Gate have launched themselves into the SSIS component market by releasing a new Data Cleanser component, albeit in beta for now. It seems to be quite a simple component, bringing together several features that you can find elsewhere, but with a suitable level  polish that you’d expect from them. String operations include find and replace with regular expressions, case formatting and trim, all of which are available today in one form or another, but will the RedGate factor appeal to people? Benefits include ease of use, all operations in one place, versus ... [Más]

SQLBits 9 goes to Liverpool

by Darren Green 21 jun 2011 22:12
SQLBits 9 has now been announced as 29th September to 1st October 2011 at the Adelphi Hotel in Liverpool. This will follow the now familiar three day format, a training day, and two full days of concurrent sessions. Saturday 1st October will of course be the free community day. Despite growing the event quite dramatically over the past four years, this is something we are all very proud to have maintained and is a key factor when planning the events. Plenty of more info to come, but in the meantime session submission is now open so why not submit an abstract?

Attunity Webcast on Real Time Optimisation For Microsoft BI

by Allan Mitchell 11 may 2011 19:15
On Wednesday 18th May 1Pm –2PM Eastern Time I am going to be helping Attunity deliver a webcast.  In it I want to highlight some of the challenges we face in getting data around our environments.  I want to highlight the Microsoft products that can help us realise Business Intelligence. It promises to be a good hour.  Hope to see you all there.  Here is the link Attunity Webcast

When does a Tumbling Window Start in StreamInsight

by Allan Mitchell 17 mar 2011 10:07
Whilst getting some courseware ready I was playing around writing some code and I decided to very simply show when a window starts and ends based on you asking for a TumblingWindow of n time units in StreamInsight.  I thought this was going to be a two second thing but what I found was something I haven’t yet found documented anywhere until now.   All this code is written in C# and will slot straight into my favourite quick-win dev tool LinqPad   Let’s first create a sample dataset   var EnumerableCollection = new [] { new {id = 1, StartTime = DateTime.Par... [Más]

Mouse Clicks, Reactive Extensions and StreamInsight Mashup

by Allan Mitchell 9 feb 2011 00:24
I had an hour spare this afternoon so I wanted to have another play with Reactive Extensions in .Net and StreamInsight.  I also didn’t want to simply use a console window as a way of gathering events so I decided to use a windows form instead. The task I set myself was this. Whenever I click on my form I want to subscribe to the event and output its location to the console window and also the timestamp of the event.  In addition to this I want to know for every mouse click I do, how many mouse clicks have happened in the last 5 seconds. The second point here is really i... [Más]

StreamInsight and Reactive Framework Challenge

by Allan Mitchell 6 feb 2011 23:09
In his blogpost Roman from the StreamInsight team asked if we could create a Reactive Framework version of what he had done in the post using StreamInsight.  For those who don’t know, the Reactive Framework or Rx to its friends is a library for composing asynchronous and event-based programs using observable collections in the .Net framework.  Yes, there is some overlap between StreamInsight and the Reactive Extensions but StreamInsight has more flexibility and power in its temporal algebra (Windowing, Alteration of event headers) Well here are two alternate ways of doing what Roman... [Más]

StreamInsight Precon at SQLBits 8 in Brighton

by Allan Mitchell 23 ene 2011 21:24
I am giving a full day seminar on StreamInsight at the SQLBits conference in Brighton, UK.  The seminar is happening on 7th April 2011.  Early bird discounts are available so get over to the site and register.  During this day I will be explaining exactly what StreamInsight is and we’ll be looking at how to solve some very interesting business problems with streaming data.  A complete rundown of what it is I will be covering is here.

SSIS and StreamInsight Working Together.

by Allan Mitchell 18 ene 2011 21:59
I have been thinking a lot recently about what it would be like to have StreamInsight and SSIS working together.  Well the CAT team have produced a paper on some of our options here. Here are some of my thoughts. There is of course a slight mismatch in their types of usage.  StreamInsight is an Event Stream processing engine capable of operating on new data in the sub second timeframe.  The engine allows you to do real time analytics and take decisions on events that have potentially only just happened.  SSIS on the other hand is a batch processing engine.  In... [Más]


Comment RSS
SQLIS | Expression Date Functions

Expression Date Functions

by Darren Green 28 Oct 2008 07:58

Date Parts

Expressions support a range of date related functions such as DATEADD, with the same basic syntax to that found in T-SQL. Whilst the familiarity is very helpful, the difference that catches me out is the format of date part which must be quoted.

T-SQL allows this:

 DATEADD(mi, -10, GETDATE())
 DATEADD(minute, -10, GETDATE())

The SSIS equivalent is:

 DATEADD("n", -10, GETDATE())
 DATEADD("mi", -10, GETDATE())
 DATEADD("minute", -10, GETDATE())

Related functions that use the same date part tokens -


Month Name Expressions

Here are some month name expressions, just waiting for a DATENAME function.

Get the month name, for the column RowDate:

 (MONTH(RowDate) == 1 ? "January" : MONTH(RowDate) == 2 ? "February" : MONTH(RowDate) == 3 ? "March" : 
  MONTH(RowDate) == 4 ? "April" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "June" : 
  MONTH(RowDate) == 7 ? "July" : MONTH(RowDate) == 8 ? "August" : MONTH(RowDate) == 9 ? "September" : 
  MONTH(RowDate) == 10 ? "October" : MONTH(RowDate) == 11 ? "November" : 
  MONTH(RowDate) == 12 ? "December" : "InvalidMonth")

Get formatted month and year, mmm (yyyy), from the column RowDate:

 (MONTH(RowDate) == 1 ? "Jan" : MONTH(RowDate) == 2 ? "Feb" : MONTH(RowDate) == 3 ? "Mar" : 
  MONTH(RowDate) == 4 ? "Apr" : MONTH(RowDate) == 5 ? "May" : MONTH(RowDate) == 6 ? "Jun" : 
  MONTH(RowDate) == 7 ? "Jul" : MONTH(RowDate) == 8 ? "Aug" : MONTH(RowDate) == 9 ? "Sep" : 
  MONTH(RowDate) == 10 ? "Oct" : MONTH(RowDate) == 11 ? "Nov" : MONTH(RowDate) == 12 ? "Dec" : 
  "ERR") + " (" + (DT_WSTR,4)YEAR(RowDate) + ")"


The common yyyy-mm-dd format is often used in file names, for example:


A sample expression to achieve this is:

 "C:\\Temp\\ErrorCodes\\" + (DT_WSTR,4)YEAR(GETDATE()) + "-" 
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" 
    + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".txt"

A similar expression, but this time deriving the file name based on yesterday’s date, useful for loading the previous day's data:

 "C:\\Temp\\ErrorCodes\\" + (DT_WSTR,4)YEAR(DATEADD("dd", -1, GETDATE())) + "-" 
    + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("dd", -1, GETDATE())), 2) + "-" 
    + RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE())), 2) + ".txt"

yyyy-mm-dd hh:nn:ss

Another simple time and date expression example:

 2006-06-22 11:48:52
 (DT_WSTR,4)YEAR(GETDATE()) + "-" 
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" 
    + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + " "
    + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + ":"
    + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)

Or alternatively:



 RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + "-"
    + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-" 


A simple yyyymmdd formatted string from a DateTime type variable

    + RIGHT("0" + (DT_WSTR,2)MONTH(@[User::DateTimeVar]), 2)
    + RIGHT("0" + (DT_WSTR,2)DAY(@[User::DateTimeVar]), 2)

An alternative yyyymmdd formatted string from a DateTime type variable.

 (DT_WSTR,8) (
   (YEAR(@[User::DateTimeVar]) * 10000) + 
    (MONTH(@[User::DateTimeVar]) * 100) + 

yyyymmdd hh:nn:ss.mi

 (DT_WSTR,8) (
    (YEAR(@[User::MaxStartDate]) * 10000) + 
      (MONTH(@[User::MaxStartDate]) * 100) + 
    ) + " " + 
    RIGHT("0" + (DT_WSTR,2)DATEPART("hh", @[User::MaxStartDate]), 2) + ":"
 + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", @[User::MaxStartDate]), 2) + ":"
 + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", @[User::MaxStartDate]), 2) + "."
 + (DT_WSTR,3)DATEPART("Ms", @[User::MaxStartDate])
 20070511 09:40:38.123

ISDATE() workaround for date values

This checks against a string value where the source system used "00/00/00" as a lack of date. There were also columns that were out of the normal range for date values (AD 1, 0600, etc). This expression NULLs those values out. I figured that someone may have been struggling with this and the lack of an ISDATE function for expressions. It could be easily adapted to handle other date formats and checking for out of range dates.

 (Date_To_Check == "00/00/00" || (DT_Date)Date_To_Check < (DT_DATE)"1753-1-1") ? NULL(DT_DATE) : (DT_Date)Date_To_Check 

Get Date - Remove Time

If you wish to return the date only, so setting the time to 00:00 you can easily do this by casting to DT_DBDATE. The data type has limited support, so casting it back to a DT_DATE will allow you to use it more readily.


Or, alternatively:


Calculate the Beginning of a Previous Month

This expression starts from today, moves back three months (as an example), subtracts the day-count from the current day-of-month to get the first day, then converts the expression to a DT_DBDATE type (which does not support a time component) then converts it back to a regular DT_DATE, which does have a time component - but now it's truncated the time to 00:00 AM.


Calculate the End of a Previous Month

To get Midnight on the last day of the previous month, we back up to the first day of the immediately following month, truncate the time to 00:00 (as in the previous example) and then subtract 1 minute to get the ending time of the previous day. (Note, in SQL Server 2005 SP2, subtracting 3ms to get the absolute last time-slice of the previous day did not work properly with the MONTH() function.)

 DATEADD("mi",-1,(DT_DATE)(DT_DBDATE) DATEADD("dd",0,DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-2,GETDATE()))))

Getting the Fiscal Year for a Date

To get the fiscal year for a given date, use the conditional operator to check the month part of the date, and return either the year part of the date, or the year part of the date plus one depending on the cutoff of the fiscal year definition. This sample assumes a fiscal year that ends June 30th:

 MONTH( @[User::InputDate]  ) <= 6 ? YEAR ( @[User::InputDate]  )  : YEAR ( @[User::InputDate]  )  + 1

Comments (4) -

5/26/2010 4:39:16 PM #

Darren Comeau

A very helpful article.  I have something to contribute for solving a problem of adding two values together one being a date and the other a time but in this case both are stored as timestamps with Midnight or 1st Jan where data is missing.

I solved this by converting date to dbdate, time to dbtime, converting both to a string which can be concatenated and finally converting the string back to a date.

The expression being (DT_DATE)((DT_STR,20,1252)((DT_DBDATE)date) + " " + (DT_STR,20,1252)((DT_DBTIME)time))

date 2010-05-21 00:00:00.000
time 2010-01-01 00:01:10.000
dbdate 21/05/2010
dbtime 00:01:10

date 2010-05-21 00:01:10.0000000

Darren Comeau United Kingdom

6/30/2010 7:01:41 PM #


Note: the colons are not vaild in a file name.

Eric United States

9/8/2010 12:53:01 AM #


Thanks for very interesting tips ... problem I have though is while my expression provides me with a previous date ... it fails when moving into another month for example on September 1, 2010 I would expect the expression to evaluate to August 31, 2010, instead it evaluates to September 30, 2010 ... it handles the date difference within a given month correctly e.g. for today 09/07/2010 it will correctly evaluate to 09/06/2010 but not when the current month goes into the next month ... just cant figure what the problem is... I'd appreciate your showing me where I'm going wrong ... Thanks.


"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", 0, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv" :

"X:\\WNTS\\Reporting\\List28old "+ RIGHT( "0"+ (DT_WSTR, 2) MONTH( DATEADD("mm", -1, GETDATE()) ) , 2 ) + "-"+ RIGHT( "0"+ (DT_WSTR, 2) DAY( DATEADD("dd", -1, GETDATE()) ) , 2 ) + "-"+ (DT_WSTR, 4) YEAR( GETDATE() )+".csv"

Gyhant Canada

10/1/2010 9:24:04 PM #


Thank you very much!!!!
You helped to delete yesterday files (as first step) when starting to run job today.

Isabella United States

Add comment

  Country flag

  • Comment
  • Preview


Comment RSS