I'm rather new to DTS and have the following questions:

1) i need to develop (a) DTS packages that transfers data from an Oracle
(8.1) database to SQL Server 2000 db. However the user should have the
possibility to select the tables in the Oracle db that he wants to
transfer/trasform to the SQL Server db. What is the best way to achieve
this?
2) Since i don't know any vbscript, is it possible (and if so where can i
find more info) to use C# to launch DTS packages or create packages?

Thanks for any help.

Guy

1. You can do this but this makes it very dynamic and you will have to
write a lot of code to do this. DTS is not really this dynamic

2. You can certainly use C# to launch/write DTS packages and one of the
best examples is a cookbook produced by Gert Drapers here

http://www.sqldev.net/download/dts/DotNETCookBook.pdf

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Guy Dillen" <guy_dillen (AT) nospam (DOT) hotmail.com> wrote

Thanks Allan for your answers:

regarding to my first question:
would it be a good solution, creating a table in the SQL Server db with
TableName, UpdateYesNo, UpdateInterval, PackageName or
StoredProcedureNameTimeStamp and using this table e.g. in a C# app to start
the Package/StoredProcedure (starting directly the package or starting the
package via a stored procedure) if the UpdateYesNo = 'Y' and the
UpdateInterval is also ok?

To my 2nd question: i downloaded the document you mentioned, but there are
no examples (if i'm right) of writing DTS packages in C#?

Thanks.
Guy



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

The problem with #1 is that DTS is simply not this dynamic. You cannot just
point it at two tables and have it work and then point the same package
unchanged to a different set of tables.

2. As I sais building a package from scratch of any complexity would be non
trivial. A good starter is to save out a package to VB modeule, look at it
and try to emulate in C#. It is doable don't get me wrong.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Guy Dillen" <guy_dillen (AT) nospam (DOT) hotmail.com> wrote

1. Can't you use dynamic properties task for this kind of situations?

"Allan Mitchell" wrote:

No

Dynamic properties will set properties of existing objects not create new
ones/remove old ones which s what you would need to do if you dynamically
wanted to specify a different source and destination everytime.
The only other way around this would be to build a datapump for every table.
You then have the user choose tables they want to move which you then
Enable/Disable in your package. This will not be pretty if you are working
on a DB with 2000 tables

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"Mark" <Mark (AT) discussions (DOT) microsoft.com> wrote