Data Transformation Services question.

[Dimka.m]

I am creating a DTS package for an import of data from MSAccess97 to SQLServer2000. I am quite new to the DTS so bear with me please.

Everything was fairly simple until I got stumped by the following problem:

In the source database there was a table with multiple fields (let's say A, B, C) for each record containing their values (let's say 1,2,3).

Now, in the destination database the table is built differently. It is a table containing the field data and definition.

So basically I need to turn this

ID       A  B  C
0        1  2  3
1        4  5  6

into this

ID       FieldName         FieldVal
0           A                 1
0           B                 2
0           C                 3
1           A                 4
1           B                 5
1           C                 6

I am not sure how to go about that... any thoughts?

[rmiao]

You may try activex for that.

[Dimka.m]

I was trying ActiveX but I can't find a good reference of the DTS Object model... so I am kind of poking in the dark... was wondering if anyone had to do something like this before.

[rmiao]

Take look at sqldts.com, may find something useful there.

[sjurkouich]

I like to keep things simple. I would load the access data into SQL as is, then use a stored procedure to put the data into its final destination on the SQL server. You can both clear out the "temporary load" table and call the SP from the package.

[Dimka.m]

Hmm.. thanks for the ideas!