[RESOLVED] Datatype mapping across linked servers

[Tom Sawyer]

My company has an application where one of the things that we need to do is pull a bunch of data from an AS/400 database (the product is called Ingenium, if that would help any) and put it into SQL Server. Right now, we have the AS/400 create a bunch of text files with the data we need and then bulk import them into SQL Server in a DTS Package. It runs pretty slow and can hold a lot of stuff up if it every hangs for whatever reason, so we're looking into a way to get it to run faster.

The first thing that I've tried to do is create a linked server to the AS/400 db and pull in the data directly and cut the text files out of the process. That works fine, except when it comes to date fields. For some reason that is inexplicable (and the reasons why it can't be fixed in the AS/400 system are even more inexplicable), sometimes null date values are stored as NULL and sometimes they are stored as '0001-01-01'. When pulling the records over that have dates of '0001-01-01', an error is thrown that says:

Error converting data type DBTYPE_DBTIMESTAMP to datetime.

This appears to be because SQL tries to map the Ingenium DBTYPE_DBTIMESTAMP datatype to a datetime field and having this dummy value in it throws an out-of-range error. Any casting done on the field seems to happen after the conversion to the SQL Server datatype, so that doesn't help. For example:

SELECT POL_ID, CAST(POL_APP_SIGN_DT as varchar(50)) FROM ingenium_dev.NORDEV.IPRD552.TPOL

throws the error, while

SELECT * FROM OPENQUERY(ingenium_dev, 'SELECT POL_ID, CAST(POL_APP_SIGN_DT as varchar(50)) FROM IPRD552.TPOL')

works fine and pulls in the data as a varchar.

I don't want to use the OPENQUERY syntax and was wondering if anyone knew of a way to override SQL Server's automatic datatype conversions and force it to view DBTYPE_DBTIMESTAMP types as varchars instead of as datetimes or to be able to convert the data before it throws the out-of-range exception (CONVERT doesn't work either)?

Anyone have any ideas?

[Clanguage]

What about writting an ap that will read the text file address the condition and then do an insert into SQL. Do you have a lot of records? How often do you update data from AS400?

[Clanguage]

After re-reading your post a little more careful I realize my post is not going to help. Can you do a "Case When" and address the condition?

[Tom Sawyer]

After re-reading your post a little more careful I realize my post is not going to help. Can you do a "Case When" and address the condition?

Unfortunately, no. SQL Server pulls the data over and converts it to SQL types before it does any processing on it. That means it throws an error on bad data before I can even do a check to find out if the data is bad.

What I'm thinking of doing is putting in a step at the beginning of the package to update all of the bad date fields through an OPENQUERY call and then I'll know that the data is good by the time that I run the linked server calls. That seems like a less than optimal solution to me, though.

[Clanguage]

Looks like your choices are limited. When you modify the first step I believe that in the Transact SQL script you can make the data selection and transform it before passing it over.

[Jrogers]

Just adding my 2c worth.. I have had problems with oracle and I played around with the Collation Compatible property of the linked server.. might help

[Tom Sawyer]

Just adding my 2c worth.. I have had problems with oracle and I played around with the Collation Compatible property of the linked server.. might help

Ya, I tried that with no success.

What I'd like to find out is where SQL Server looks to see that if it finds a DBTYPE_DBTIMESTAMP column then it converts it to a DATETIME column. If I can override that and have it bring it in as a varchar, then I can do whatever checks I need once I get the data. I've looked through all the config files and master db tables I can see, though, and can't figure out how to do it.

[Clanguage]

Tom,
I found this link which may be of help to you.
http://www.mydatabasesupport.com/for...ql-server.html
Hope it helps

[szlamany]

If I'm not mistaken then that link is suggesting what I would suggest as well.

Import into a staging table. We do this a real lot. Then scrub the column data in the staging table using a stored procedure, for instance, and then load into the production table.

Also - have you thought out if a trigger might be useful in cleaning this data before insert? I've not done a lot of trigger stuff - maybe the datatype mapping issue will blow up a trigger anyway.

What about field defaults - could setting a default somehow get a real value if the conversion doesn't work.

Also - try this MS Technet site - post your question there

http://www.microsoft.com/technet/com...lserver.server

[Tom Sawyer]

Import into a staging table. We do this a real lot. Then scrub the column data in the staging table using a stored procedure, for instance, and then load into the production table.

This looks like it may be what I'll end up doing. The Import Wizard lets me fix the column mapping transformations manually, so I can just force all the dates into varchars and then do my converts and checks after that before I put them into the real tables.

Of course, this still adds the extra step of populating the staging tables and then going to the real tables, rather than just pushing the data directly into the real tables, but it's probably still quicker than importing the data from text files.

[Tom Sawyer]

Got it working.

It doesn't look like you can do anything with the calls in the linked server directly, but you can do it inside of the Import Wizard. You import the table and then hit the Transform button under the Transformations tab, you can select to Transform information as it is copied to the destination table.

Then you replace:

DTSDestination("POL_APP_SIGN_DT") = DTSSource("POL_APP_SIGN_DT")

with:

If IsDate(DTSSource("POL_APP_SIGN_DT")) Then
    DTSDestination("POL_APP_SIGN_DT") = DTSSource("POL_APP_SIGN_DT")
End If

Then if it's a valid date, that date is entered and if it's not, the column is left null. Problem solved.

Now, if only the AS/400 guys had ever heard of a concept called data normalization and didn't have tables with 187 columns with 51 date fields in them that I have to manually handle, this would be quite an easy task from here on out.

Thanks for everyone's help.