importing data into ingres from sqlserver using Data transform service

• cusishoo

  SSC Rookie

  Points: 44

  More actions

  August 5, 2005 at 1:22 am

  #68240

  I have a staging table holding data that I need to import into a table in an ingres database. The column holding the data in sqlserver that is causing me grief, has been created as [datetime]  NOT NULL default '' .  When I populate this column with an empty string it converts it to the date(1/1/1900 - very annoying). 

  I ultimately want to transfer this data into an ingres table with the same structure - this column having been created as column_name date not null default ' '  in ingres. I have to populate this column with an empty string and  I am having problems transferring this data into this table due to this problem column..

  In the transform data task i have tried cast the sqlserver column as an empty string but then this causes a conversion error when i execute the step..

  Does anyone having any ideas how to solve this problem??

  APOLOGIES for posting this twice

• Richard Lee-Brown

  Newbie

  Points: 9

  More actions

  August 9, 2005 at 3:29 am

  #580631

  Hi, I posted a reply earlier but it has vapourised ?

  I would recommend against using empty strings etc in Ingres dates, or any other database for that matter.

  I would not consider an empty string to be a date, this is where you should be using a NULLs to indicate a non or missing value.

  Ingres versions 6.4, 1.2 and 2 each handle empty strings in dates differently.

  If you are not familiar with Ingres, read up about Ingres date data type handling because it is not quite like SQL server or Sybase, dates entered as '23/04/2005' behaves very differently to one entered as '23/04/2005 12:34:56'.

  Try to convert the blanks using a case statement into nulls, and create the tables' column as DATE WITH NULL NOT DEFAULT.

  I Hope this helps

  Richard