Archive

Archive for December, 2010

Windows Server 2008 Image thumbnails not showing

20 December, 2010 1 comment

I noticed the other day that on one of my servers image thumbnails were not showing.

This is easy to fix, do the following:

  1. Click Start  and Right-Click on “Computer” and choose “Properties”, (or if like me you like your “Computer” icon on your desktop then just Right-Click on the computer icon on your desktop).
  2. Under “Tasks” on the left hand list of options click “Advanced System Settings”
  3. On the “Advanced” tab, in the “Performance” group, click “Settings”
  4. On the “Visual Effects” tab find and tick the following option “Show thumbnails instead of icons”
  5. Click Ok
  6. Click Ok

You should now be able to browse your images as thumbnails again.

Sigh! Thanks for turning that off by default Microsoft!

Advertisements
Categories: OS

SSIS Date conversion error going from Oracle to SQL Server

19 December, 2010 1 comment

Hi,

The other day I had the task to move a large amount of data from Oracle to SQL Server.
I had chosen to import the data via SSIS, doing a direct copy from one table in oracle to one table in SQL Server.

Everything was fine until I one of my tables crashed, with a data conversion error.

The issue is that the lowest date that SQL Server will accept is “1-Jan-1753”. There was some bad data in Oracle dated year 1049, consequently SQL server did no know how to handle it. How was I going to handle it?

The solution is to use a transformation script. In your data flow task, add a transformation script between your source and destination. When you drop in on the form it pops up a dialogue, leave it default for transform.

On the inputs column choose the date column in question (for the example lets assume its called CreatedDate).
Make sure you change its “UsageType” in the grid from “Read” to “Read-Write”.

Go to the script tab and click “Design Script” enter something like the following code:
Note: This is in vb .net. I think 2008 R2 allows c#, but you should get the idea:

        Try
            Dim dt As DateTime = Row.CreatedDate()
            Dim dtMin As DateTime = System.DateTime.Parse("1753-01-01 00:00:00")
            Dim dtMax As DateTime = System.DateTime.Parse("9999-12-31 23:59:59")
            If (Row.CreatedDate_IsNull = False) Then
                If (dt < dtMin Or dt > dtMax) Then
                    If (dt < dtMin) Then
                        Row.CreatedDate() = dtMin
                    Else
                        Row.CreatedDate() = dtMax
                    End If
                End If
            End If
        Catch ex As Exception
            Row.CreatedDate_IsNull = True
        End Try

This code will find any row that has a date that falls outside the bounds of what SQL server will accept and set it to the Max or Min date values.

Finally some general tips that I learned in attempting to move large amounts of data from Oracle to SQL.

1. Don’t use a linked server connection it is really slow.
2. The Microsoft OLEDB connector for Oracle is pretty fast.
3. When setting up the OLEDB Destination via “Table or View” make sure you choose the “Fast Load” option!! Why you would want it to run slow is beyond me but the “Table or View – fast load” is about 10 to 15 times faster.

Cheers

Try
Dim dt As DateTime = Row.CreatedDate()
Dim dtMin As DateTime = System.DateTime.Parse(“1753-01-01 00:00:00”)
Dim dtMax As DateTime = System.DateTime.Parse(“9999-12-31 23:59:59”)
If (Row.CreatedDate_IsNull = False) Then
If (dt < dtMin Or dt > dtMax) Then
If (dt < dtMin) Then
Row.CreatedDate() = dtMin
Else
Row.CreatedDate() = dtMax
End If
End If
End If
Catch ex As Exception
Row.CreatedDate_IsNull = True
End Try
Categories: SQL Server