• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 545
  • Last Modified:

Dynamic DTS transformation

I have a DTS package that needs to pump some data from one db to another, the problem is that the table names and columns are dynamically determined.

I have a table which contains all the table and column names that need to be transferred, its stucture is;

TABLE_NAME sysname,
sample data
[table1] [column1]
[table1] [column2]
[table1] [column3]
[table1] [column4]
[table2] [column1]
[table2] [column2] etc.....

I need some input on how to transfer this data, I tried to dynamically set the source and destination columns on the task via dynamic properties, but it seems this is not possible..
  • 7
  • 7
1 Solution
Hi Tacobell777, I'm from Brisbane also.

Is the destination table also dynamically determined?

Will the number of columns and datatypes be constant?
All you need to do is map the column
for column


Table 1                                      Table2
Column 1                                   column 1
column 2                                   column 2

and so on

table 1 data must be the same  data types
as column 2 - the col names dont have to be the same

if there is an identity field in table 1 do not map that column- table 2 will create its own identity column
if the database is structured to do so

Please let me know if you dont know how to create
a dts package.

Tacobell777Author Commented:
nmcdermaid  where in brisbane?

> Is the destination table also dynamically determined?
> Will the number of columns and datatypes be constant?

But if required I also have these in the same table, but I do not really want to build a dynamic SQL string, I really want to use DTS.

I already have my DTS package, I also have the data transformation, but I somehow need to dynamically set the table and columns on the data pump task, as I said, I tried it with dynamic properties, but it does not let you specify this, I am guessing it can all be done programatically.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

I'm doing BI consulting, all over the place. I live on the northside. I used to work at Suncorp about five years ago.

....so, you need to dynamically map a dynamic source table to a dynamic destination table.

I think DTS is great for basic needs but IMHO I think it quickly gets very impractical for this kind of thing.

You can certainly alter as many properties as you can get your hands on using VBScript/ActiveX tasks but I know that when I do things at that level of trickiness, DTS just crashes.

The way I see it working, you would need to build your DTS from scratch each time, as opposed to dynamically editing an existing one.

I've just exported a DTS as a VB module and I notice it takes 47 lines of code to set *one* transformation. So it seems to me that whatever advantage you get from developing something like this would be outwieghed by the sheer complexity of the code required to make it work.

You could certainly export a simple DTS as VB then use that as a guideline for creating something that does what you want. It sounds like you're up for a challenge!

An alternative option would be to try using OPENROWSET, and using that with a bulk insert (totaly T-SQL) though I know it also has problems with dynamically changing the source data.

Tacobell777Author Commented:
You just gave me an idea
I will export a package to VB and see what the code is and modify it..


I also live on the northside...
Tacobell777Author Commented:
Ooh ;-) it was your idea, I just read the whole comment. Cheers I will give it a go.
Tacobell777Author Commented:
OK I tried that, but unfortunately VBScript's syntax is a bit different than VBScript, its throwing errors.
Its not too hard to convert VB to VBScript, but how are you intending to run it, in an ActiveX script?

Here are some differences to get you started:

-There's no 'AS' keyword in VBScript
-Use CreateObject instead of 'As New'

If you are interested there is a VBScript help file download at Microsoft at


The link you want is Microsoft Windows Script 5.6 Documentation.

But as you can see there is a lot of code required to create package from scratch.
Basically the other two methods are OPENROWSET and BULK INSERT.

They both require that you build dynamic SQL, but I reckon they will be way lighter than using DTS.
Tacobell777Author Commented:
thanks for the help, I am giving up on this one though ;-)

I wish there was a DTS area on EE !!


PS. a T-SQL script is just not viable, I would have to deal with constraints etc. and we are talking about thousands of records on two different db servers.

The reason I am giving up is because its just a "Nice to have" for the developers over here, they want something that copies the data from LIVE to DEV, without affecting the structure on DEV, I have everything done except the PUMP from LIVE to DEV....
Well I guess this is the DTS area. I have also found SQLDTS.COM a handt site with some fantastic examples to get you 'in the thick of it'

Really the only way to do it is to explicitly define each source/dest table and asociated transformations. They sell ETL packages that probably do what you want but you need the $$$

I'm not sure what you mean about T-SQL being not viable. Whether you pump the data through T-SQL or DTS, you still have to deal with constraints on the destination table (if thats what you mean)

Tacobell777Author Commented:
With the transform data task you can specify whether to Check constriants or not, I'm not sure how you would handle that within a T-SQL script? Disable all the relationships and constraints do the insert and then put them back on?
Anything you can do in DTS you can do in T-SQL, including disabling CHECK constraints.

straight from BOL, ALTER TABLE:

ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap

Perhaps I'll see you around Brisvegas!

Tacobell777Author Commented:

Yeah I realized that, thats why I said "Disable all the relationships and constraints do the insert and then put them back on?"
It sounds like a mess ;-)

So where in Brissie do you work then?
I work for Inco, some mining company on Charlotte street...
ah, the only type of company with more money to throw around than the government; mining ! :) Today I'm at the Treasury Casino.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now