Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    12

    Unanswered: DTS help needed!

    I often use DTS to move databases between the servers. When you choose "Transform" data and “Column Mappings and Transformation” window opens, the default option is “Append rows to destination table”.
    Question: Is there a way to change the default option to “Delete rows in destination table”, so I wouldn’t have to go thru the transformation of dozens of tables every time I move databases around.

    P.S. I know that I can save DTS package with my settings and use it later. The problem is that every time it might be different database or objects might change. So, it is not a solution for me.

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Preceed the step with either DELETE or TRUNCATE script that you can easily generate yourself.

    create view dbo.vw_DELETE_ALL_TABLES as
    select 'DELETE ' + db_name() + '.dbo.[' + name + '] ' + char(13) + char(10) + 'go'
    from dbo.sysobjects where objectproperty(id, 'IsTable')=1
    and objectproperty(id, 'IsMSShipped')=0

    Below is the OS step that you will need to create:

    bcp <you_db>.dbo.vw_DELETE_ALL_TABLES out DELETE_ALL_TABLES.SQL -S <your_server> -T -c
    osql -S <your_server> -E -i DELETE_ALL_TABLES.SQL
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Posts
    12
    Thanks rdjabarov,

    It might work if you are dealing with a whole database.
    But what if user asks you to refresh development database with the production data except a few tables? In this case you will need to modify your script, or ...?
    That is why I was wondering if I could modify the default setting for DTS to "Delete rows in destination tables".

    Thanks again

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, then have an exception table and populate it with tables that don't need to be refreshed. Then modify the view to return only records that are not found in the exception table. Easy, right?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Posts
    12
    It might be easy if you don't have 230 databases to support.
    In my situation maintaining "exceptions" table in each database can be entire new project. So, I guess I will do it the old way - open and click to transform each table.

    Thank you for your help.

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Well what I would do is create a quick web app that will pass the parameters such as what tables to transform and weather to delete the data or not to the dts package and then execute it.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by arka
    It might be easy if you don't have 230 databases to support...
    I used to support 1500 databases, now I support over 3500. If you think you can't afford to invest 1.5 hours in designing the structure and the process that would take care of it...I rest my case
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    1500? How many servers?

    And that sound ludicrous...DTS..

    I would dump and RESTORE...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Posts
    12
    Calm down guys,

    You forgot my initial question: Is there a way to make a default one of the DTS options in "Column mappings and transformations" window to "Delete rows in destination table" instead of "Append..."? That's it.

    I'm pretty sure there are dozen of ways to transfer databases/data between the servers, as well as dozen of reason why you prefer to choose one way or another.

    Thanks a lot for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •