Archive for the ‘SSIS’ Category

SSIS Convert between datetimeoffset and datetime

June 11th, 2014 No comments

The Problem

I had a sql table that was storing data in a DateTimeOffset(7) datatype, DateDeleted.

I was trying to use SSIS to sync the data to another table where the DateDelted column was a DATETIME datatype.

How do you cast/convert between a DateTimeOffset(7) datatype to a DateTime object

The Solution

In a derived column object use the following formula

DATEADD("hh", DATEDIFF("hh",((DT_DBTIMESTAMP)DateDeleted), DateDeleted), ((DT_DBTIMESTAMP)DateDeleted))

I will walk you through what this actually is.

Part 1 – Base Cast


This is just a standard type conversion.
The problem!!

When you try this you will receive a “Truncation Error”.

  1. Open the derived column transformation editor
  2. Click on “Configure Error Output”
  3. On this page for your derived column (in my case C_DateDeleted), set the Truncation value to “Ignore failure”

SSISConfigureError output

So now it will work, but the problem is the offset is lost. In my case I am in Australia, +10 UTC.

Part 2 – Add the Offset back

So I want to add 10 hours back so I changed my derived column to

DATEADD("hh", 10, ((DT_DBTIMESTAMP)DateDeleted))

This works great, but what about daylight saving time when the offset may alter to 11 hours etc.

Part 3 – calculate the offset

DATEDIFF("hh",((DT_DBTIMESTAMP)DateDeleted), DateDeleted)

This says get the hours difference between that DateTime version of the date ((DT_DBTIMESTAMP)DateDeleted) and the DateTimeOffset version of the date.

Part 4 – Final version

Now put it all together

DATEADD("hh", DATEDIFF("hh",((DT_DBTIMESTAMP)DateDeleted), DateDeleted), ((DT_DBTIMESTAMP)DateDeleted))

Easy :-)… not!!!

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: SSIS Tags:

SSIS 2012 Project Deployment – Set CheckpointFile with Parameters

July 23rd, 2013 No comments

The Problem

SSIS in SQL Server 2012 now supports a “Project Deployment” model. Adding things like Connection Strings is relatively intuitive.
I want to use a parameter to set my CheckPointFileName property.
How can you do this so it can be configured by the environment.

The Solution

  1. Create a parameter in your package and call it “CheckpointFileName”
  2. Click On the ellipses “…” on the Expressions property.
  3. In property Choose CheckPointFileName from the drop down
  4. Set the expression by opening the expression builder and choosing the parameter you just created, (sometimes you need to drag and drop the paramater into the expression box)


  5. Click OK

Now you can deploy your package and set this parameter as part of you environment setup and change the checkpoint file name as required

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: SQL Server, SSIS Tags: