Generic DTS Package

Question

Generic DTS Package

maffan76

Ten Centuries

Points: 1256
More actions
February 3, 2003 at 12:06 pm

#105995

Hi All,
I am currently using Custome Made Data based Syncronization Routine (SPs) to import data from different system. I am using my own defined meta data table for this. i add all the field information its relationship and its basic data validation checks in my set of tables and then build dynamic queries based on the entered meta data, this helps me in making it generic that if in near future i want another field to be imported i just add that field information in my meta data tables and define proper velidation rules (PK check, NOT NULL, UNIQUE) in meta data and that field gets imported. Now i want to develop a generic DTS package to do the same thing. So i need some advice and guidance in this regard.
1. Can i use meta data (field information, integrity checks to import the tables) from any system?
2. Will i have to develop a package with defined columns and will have to modify the package accordingly once new field(s) requirement comes in.
3. How should i tackle this problem?
Actaully the meta data driven approach is quiet dynamic and i find it very handy, i am just wondering if i can do the same thing in DTS as well.
Thanks in advance
Kind Regards,
Affan

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

If you use the DataPump task to import your data, then you can write an ActiveXScript that manipulates the task properties before it executes.

If you're using SQL 2000, jump into disconnected edit to get an idea of what you need to modify. Also, check out 'Programming DTS Applications' in BOL.

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

juliekenny SSCommitted Points: 1558 More actions · Answer 2

You would probably find some useful features in the Dynamic Properties Task. This allows you to set a lot of the task objects from parameter files / tables etc but I think you would still need to use some activeX.

maffan76 Ten Centuries Points: 1256 More actions · Answer 3

Thanks for the suggestion, i'll look into that but can you guide me exactly how to go about it

kind regards,

Affan

philcart SSC-Forever Points: 47794 More actions · Answer 4

Here's a start. It shows how to reference the package, step and task objects within a script. All it really does is display the step and task name. Once you know how to get to the task you want, it's just a matter of browsing through disconnected edit to find the property you want to modify.

Function Main()

Const STEP_NAME = "DTSStep_DTSDataPumpTask_1"



Dim oPkg ' DTS Package object

Dim oStp ' DTS step object

Dim oTsk ' DTS Task Object



Dim sPkgName ' DTS Package name

Dim sTrns ' transformations item

Dim iStps ' number of steps in package

Dim iCntr ' loop counter



Set oPkg = DTSGlobalVariables.Parent



iStps = oPkg.Steps.Count



' initialise loop counter

iCntr = 1



'Find Step to Change

While (oPkg.Steps(iCntr).Name <> STEP_NAME) And (iCntr <= iStps)

iCntr = iCntr + 1

Wend



' set reference to step

Set oStp = oPkg.Steps(iCntr)

' set reference to task associated with step

Set oTsk = oPkg.Tasks(oPkg.Steps(iCntr).TaskName)



MsgBox oStp.Name

MsgBox oTsk.Name



Set oTsk = Nothing

Set oStp = Nothing

Set oPkg = Nothing



Main = DTSTaskExecResult_Success



End Function

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

maffan76 Ten Centuries Points: 1256 More actions · Answer 5

Thanks Phill,

Let me ask another question that can i use DTS to make a ETL tool with a user friendly user interface where user provides the source and destination database and defines the checks and relationships or even i can pull the relationships from source and then using DTS i can verify, transform and load the clean data in the destination DB.

Actually i want to make a tool which should be user friendly and configurable by the user. Thats why i am asking that whether is it possible to make a genaric package whihc can do this kind of Job for me

Secondly in DTS can i check and validate the data for foreign Keys?

Any thoughts on this by anyone?

Kind Regards,

Affan

maffan76 Ten Centuries Points: 1256 More actions · Answer 6

Thanks Phill,

Let me ask another question that can i use DTS to make a ETL tool with a user friendly user interface where user provides the source and destination database and defines the checks and relationships or even i can pull the relationships from source and then using DTS i can verify, transform and load the clean data in the destination DB.

Actually i want to make a tool which should be user friendly and configurable by the user. Thats why i am asking that whether is it possible to make a genaric package whihc can do this kind of Job for me

Secondly in DTS can i check and validate the data for foreign Keys?

Any thoughts on this by anyone?

Kind Regards,

Affan

philcart SSC-Forever Points: 47794 More actions · Answer 7

Yes it is possible.

To get an idea on what you can do with DTS, get the SQL Server Accelerator for BI.

http://www.microsoft.com/solutions/bi/

With this tool you define what you want your datawarehouse to look like, Dimensions, Facts, etc... Then it creates the database, the DTS packages to load the database and Analysis Services cubes to view the data. All driven from an Excel, yes Excel, spreadsheet.

As for foreign key checking, where you wanting to interrogate the actual database structure, or the data itself?

Thanks

Phill Carter

Edited by - phillcart on 02/18/2003 02:16:43 AM

--------------------
Colt 45 - the original point and click interface

maffan76 Ten Centuries Points: 1256 More actions · Answer 8

Thanks Phill i'll look into that.

Secondly i want to give user complete independence aboiut defining the FK/PK relationships or integrity checks.

Kind Regards,

Affan

philcart SSC-Forever Points: 47794 More actions · Answer 9

quote:
Secondly i want to give user complete independence aboiut defining the FK/PK relationships or integrity checks.

I don't think you could do that and maintain any sort of data quality

Thanks

Phill Carter

Edited by - phillcart on 02/19/2003 06:03:39 AM

--------------------
Colt 45 - the original point and click interface