I've been googling around and haven't quite found what I need...

I'm trying to DTS some tables from one database (Pervasive 8.50
wrapping Btrieve files) to another (SQL Server 2000), but not
quite directly. The DTS is looping through a set of tables,
creating them in SQLServer from CREATE TABLE statements made
in a previous step.

What I'm wondering, is it possible to dynamically change the
transformations in a DTSDataPumpTask from the source and
destination table information somehow? I can change the source
and destination table easily enough, and I've found examples of
making transformations, but those examples assume that I know
the data types, field names, etc at design time, and I don't.

I guess what I'm looking for kind of falls under the category
of reflection... Is there any way to do that?

I don't find an easy way to change transformations but if you have defined
number of files formats you can probably use a Bulk Insert Task with format
file (file.fmt).
This Bulk Insert Task can be in a loop and you can select the format that
you want for each txt file to be import.
Rgds
JFB

"slapdash" <slapdash (AT) rcn (DOT) com> wrote

The downside to the Bulk Insert Task is this bit:

"The Bulk Insert task can transfer data only from a text file into a
SQL Server table or view."

I'd have to export from the source, then import into the destination.
I might end up doing that, but I'm not a big fan of it.

The reason I think what I'm after might be possible is that if I set up
a Transform Data Task, I choose a source and a destination and
SQLServer automagically knows the names and data types, even if I set
the source to a query rather than a table -- that tells me that
SQLServer knows this info just by querying a table: if only I had
access to that...

I think what I might do is make a table with the field information I
need -- it's still a bit indirect, but not as much as a text file. I
think I can do that without TOO much trouble, though I haven't done
this sort of thing before.

You can use a bit of dynamic sql, inserting the name of the table as part of
a sql statement which has this syntax:
declare @i varchar(1000)

set @i = 'select top 0 * into Newtemp from ' + TabletobeNamed
execute @i

This would create an empty table named Newtemp based on the datatypes of the
recordset you are using. It will be an empty table with the exact field
names and datatypes you will need for your datapump. Then, you set the
destination to the table NewTemp.

It's pretty cool, but can cause locks sometimes!

R

"slapdash" wrote: