Link to home
Start Free TrialLog in
Avatar of peeksnib
peeksnibFlag for United States of America

asked on

Using SSIS Import Wizard - will not map db2 date to sql

Im using the SQL Server Import and Export Wizard that comes with SSIS.  The data source is ibm's 'IBM OLE DB Provider for DB2'.  I believe the MappingFiles that it should be using is 'IBMDB2ToMSSql.XML (see attached 'text' file; the SourceType="IBMDADB2".

When the wizard maps db2 file, for date fields I receive the following message.
"The data type could not be assigned to the column "xxxxxx" in "Microsoft OLE DB Provider for SQL Server".

Within the wizard, it say's it can't figure out how to map the db2 date fields.  If I go ahead and force it to map to datetime, it works.  However, I have over 200 tables and I would just as soon let the computer do the work for me. ;^)


IBMDB2ToMSSqlxml.txt
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Consider the option of importing the data from DB2 into staging tables as character data and then converting the datatime values.
what is the db2 data type ?
have you tried to change that xml file to import db2 dates as characters ? though it probably won't allow for the conversion you need

i think that in this case, you should do some additional work
what is the target data type you want to have in sql server ?

if changing the xml does not do the work, i will recommend creating a view on each db2 table and have that view convert the date column to the required format that sql server expects, and then have the SSIS work on those view
the view generation can be done dynamically using a query
you don't have to write a create view statement for each table manually
Avatar of peeksnib

ASKER

momi sabag:
According to the ssis server expolorer the input field is defined as DATE.  The target ms sql data field should be datetime.  Below is an example of three 'date' field being diplayed in the ssis server explorer.
1/1/0001 12:00:00 AM      2/29/2008 12:00:00 AM      NULL
11/2/1979 12:00:00 AM      2/29/2008 12:00:00 AM      NULL
7/15/1980 12:00:00 AM      2/29/2008 12:00:00 AM      NULL
7/30/1980 12:00:00 AM      2/29/2008 12:00:00 AM      NULL
12/15/1980 12:00:00 AM      2/29/2008 12:00:00 AM      NULL
3/23/1981 12:00:00 AM      2/29/2008 12:00:00 AM      NULL
7/1/1981 12:00:00 AM      2/29/2008 12:00:00 AM      NULL

I have not tried import the db2 dates as characters (thought it should just work)  Have you any suggestions on what I should put in the 'mapping' xml?

As to creating a view of the table, the DB2 souce is a 'hard to get anything out of them' data warehouse.  We don't have write access to their system.

acperkins:
I would consider the option if I can't get the import wizard to work.  (less steps means less mess - especially for the end user that will be creating/running the import wizard ;^)
>>less steps means less mess<<
Actually, I do not agree with you here.  You should never import directly to your production tables and always use staging tables.  This allows you to validate and transforming the data before committing it.  Rememer the old axiom: Junk in, junk out.
acperkins:
You got me there.  My client wants to see the junk.  She doesn't know what she's looking for until she sees it.  Her idea of data mining.
And that is exactly what the staging tables are for.  So that she can approve them before they get imported in.
Hi peeksnib,

I'm not familiar with SSIS so I'll defer all matters pertaining to it to the other experts.

But I will caution you on converting date fields from DB2 to SQL Server.  I've worked with a number of systems where this was a requirement and it has always been problematic.  The underlying issue is that DB2 uses an epoch data of January 1, 0001 (stored internally as 00010101) so that any date from January 1, 0001AD is valid.  SQL Server seems to apply a bias to the year so that the actual storage of the data requires a smaller data field than is used in DB2.  (The bias may be configurable.)  

I'm aware of several instances where the bias is 1970.  This has the potentially catastrophic affect of rendering all dates prior to January 1, 1970 as illegal dates since SQL Server can not record dates prior to its own epoch date.  This further complicates conversion efforts by requiring special handling on the DB2 side of dates that SQL Server deems to be illegal.  The DB2 side will have to convert these dates to valid dates or NULL (depending on the SQL Server side definitions).

Good Luck,
Kent


KDO:
We will have dates before 1970; Thanks for making it aware to me, I'll need to test it in the final solution.
In MS SQL Server the range for a datetime data type is  January 1, 1753 to December 31, 9999 (smalldatetime is January 1, 1900 to June 6, 2079)

If you are wondering what is the significance of the year 1753, that is the year that calendars world wide were standardized to follow the Gregorian calendar.  Prior to that all dates are suspect.
Hi ac,

My memory's a biz fuzzy.  Do you know if 1/1/1753 is a hard coded epoch date or if it's configurable?  And to which versions of SQL Server this is applicable?

I truly seem to recall a SQL Server 2000 database that was using 1970 as the epoch date.

Kent

>>Do you know if 1/1/1753 is a hard coded epoch date or if it's configurable?  And to which versions of SQL Server this is applicable?<< It has always been that way.  Well, at least since v6.x (and I have no reason to believe that 4.2 was any different)

>>I truly seem to recall a SQL Server 2000 database that was using 1970 as the epoch date.<<
UNIX use a January 1, 1970 base date.  Perhaps you are thinking of that.

Hmmm....

I guess that I need to do some more research as I distinctly recall a DTS package aborting because the DB2 date that is was importing was in the 1950s.


Kent
KDO:
So... Were you able to get the import/export wizard to work on a date field at all from db2 to sql, or just when it had a 1950s date?

Hi peeksnib,

As I said, I'm not familiar with SSIS.  But I have used DTS, MS Access, and other tools to convert DB2 data, including dates.


Kent
ASKER CERTIFIED SOLUTION
Avatar of peeksnib
peeksnib
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial