I am importing a paradox table into a MS SQL 2000 database. Both tables are
named Member. The paradox Member has a Customer ID (Varchar(4)) and User ID
(Varchar(3)). When I import the table to MS SQL, I want to combine this two
to become the UserID (Varchar(7)) of the destination table.

For e.g., I have Customer ID = C001 and User ID = ABC in the paradox Member
table, I want the destination table to have its UserID = C001ABC.

How to do that? I tried under Mappings and Transformation, I choose
Transform information as it is copied to the destination and I do something
like:

DTSDestination("UserID") = DTSSource("CustID")+DTSSource("UserID")

But it returns error and doesn't work.

Another thing... I have a LoginTime column in a paradox table that I want to
import to a SmallDateTime column in MS SQL. But, error occurs. It says Data
Overflows. But the value I stored are all like for e.g. 14:00, 15:00...
should I add a 1900-01-01 before the time? But how to do that?

DTSDestination("LoginTime") = "1900-01-01 "+DTSSource("LoginTime")

Error again...

And finally, I save the import information as a DTS Package. When I open it,
it's in a strange diagram that I don't know how to alter all the process. Is
there anyway to let me get back to something similar as the DTS Import/Export
Wizard, that's more user-friendly?

Thank you.

Hi

First problem is straightforward.
The transformations use VBScript to describe their actions, as per ActiveX
controls, therefore the syntax differs slightly from SQL.

col1 & col2

should work fine.

I've not dealt directly with Paradox data, but have had similar challenges.
You're on the right track with the time conversion to datetime by prefixing
with a date. Using the & should again help this.

Hope this helps
Andy H

--
Message posted via http://www.sqlmonster.com

Thanks. Problem fixed. But there's another problem... I found out that in my
paradox table, if I store "17:00:00" in a column, after I import it into my
SQL2000 table, the value becomes "5:00:00"... How would I know whether it's
AM or PM like this. Can I make it as 17:00:00 instead?

"Andy Hughes via SQLMonster.com" wrote:

Hi,

"wrytat" <wrytat (AT) discussions (DOT) microsoft.com> schrieb im Newsbeitrag
news:E17510CC-3D60-4B6D-88E2-8C14E047AACB (AT) microsoft (DOT) com...
how do you looked at the values?
What do you get if you query the login time like
SELECT CONVERT(varchar(10),LoginTime,8)

Helge

I see 5:00:00.

"Helge C. Rutz" wrote:

Sorry for sounding a bit vague. I meant that after performing that select
statement
SELECT CONVERT(varchar(10),LoginTime,8)
I see 5:00:00
Thank you

"Helge C. Rutz" wrote: