Using SSIS Import Wizard - will not map db2 date to sql : Microsoft, SQL, 2005, SSIS import wizard - problem converting dates, The data type could not be assigned to the column "XXXXXXXX" in "Microsoft OLE DB Provider for SQL Server"., mapping db2 v9 to SQL

Consider the option of importing the data from DB2 into staging tables as character data and then converting the datatime values.

03.03.2008 at 10:56PM PST, ID: 21038831

Rank: Wizard

momi_sabag:

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

03.04.2008 at 04:51AM PST, ID: 21040367

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 ;^)

03.04.2008 at 06:06AM PST, ID: 21040971

Rank: 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.

03.04.2008 at 06:12AM PST, ID: 21041042

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.

03.04.2008 at 06:44AM PST, ID: 21041379

Rank: Wizard

And that is exactly what the staging tables are for. So that she can approve them before they get imported in.

03.04.2008 at 06:49AM PST, ID: 21041431

Rank: Wizard

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

03.04.2008 at 07:47AM PST, ID: 21042017

KDO:
We will have dates before 1970; Thanks for making it aware to me, I'll need to test it in the final solution.

03.04.2008 at 11:55AM PST, ID: 21044421

Rank: Wizard

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.

03.04.2008 at 12:08PM PST, ID: 21044554

Rank: Wizard

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

03.04.2008 at 12:23PM PST, ID: 21044696

Rank: Wizard

>>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.

03.04.2008 at 12:32PM PST, ID: 21044786

Rank: Wizard

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

03.04.2008 at 01:45PM PST, ID: 21045437

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?

03.04.2008 at 02:38PM PST, ID: 21045918

Rank: Wizard

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

03.11.2008 at 12:57PM PDT, ID: 21099472