Post reply

Generic DTS Package

  • 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

  • February 3, 2003 at 2:25 pm

    #447894

    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

  • February 13, 2003 at 10:03 am

    #448889

    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.

  • February 14, 2003 at 12:37 pm

    #449031

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

    kind regards,

    Affan

  • February 16, 2003 at 4:06 pm

    #449074

    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

  • February 16, 2003 at 9:51 pm

    #449078

    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

  • February 16, 2003 at 9:52 pm

    #449079

    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

  • February 18, 2003 at 2:15 am

    #449165

    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

  • February 18, 2003 at 2:54 am

    #449168

    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

  • February 19, 2003 at 6:03 am

    #449256

    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

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

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