Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    163

    Red face Unanswered: Which option to use when saving a DTS package?

    Hi,

    When saving a DTS package, we get 4 options (listed below). Can someone please guide me which options should I used and what's the difference/advantage/disadvantage amongst these options:

    [list=1][*]SQL Server[*]SQL Server Meta Data Services (this appears dimmed/disabled - i don't know why [*]Structured Storage File[*]Visual Basic File[/list=1]


    Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Which option to use when saving a DTS package?

    Originally posted by Joozh
    Hi,

    When saving a DTS package, we get 4 options (listed below). Can someone please guide me which options should I used and what's the difference/advantage/disadvantage amongst these options:

    [list=1][*]SQL Server[*]SQL Server Meta Data Services (this appears dimmed/disabled - i don't know why [*]Structured Storage File[*]Visual Basic File[/list=1]


    Thanks.
    From BOL:

    Saving a DTS Package
    When you save a Data Transformation Services (DTS) package, you save all DTS connections, DTS tasks, DTS transformations, and workflow steps and preserve the graphical layout of these objects on the DTS Designer design sheet.

    You can save a package to:

    Microsoft® SQL Server™.
    With this default save option, you can store a package as a SQL Server msdb table, allowing you to: store packages on any instances of SQL Server on your network; keep a convenient inventory of saved packages in SQL Server Enterprise Manager; and create, delete, and branch multiple package versions during the package development process.

    To save a DTS package to SQL Server

    Enterprise Manager


    How to save a DTS package to SQL Server (Enterprise Manager)
    To save a DTS package to SQL Server

    In Data Transformation Services (DTS) Designer, on the toolbar, click the Save button.


    In the Location list, click SQL Server.


    Complete the rest of the required fields.


    Note If the DTS package has already been saved to Microsoft® SQL Server™, the Save DTS Package dialog box will not appear and a new version will be saved. If the package has been saved to a structured storage or a Microsoft Visual Basic® file, or to SQL Server 2000 Meta Data Services, click Save As to save to SQL Server.


    See Also

    Saving a DTS Package to SQL Server


    SQL Server 2000 Meta Data Services.
    With this save option, you can maintain historical information about the data manipulated by the package. However, Meta Data Services and the repository database must be installed and operational on your server. You can track the columns and tables that are used by the package as a source or destination. You also can use the data lineage feature to track which version of a package created a particular row. You can use these types of information for decision-support applications. For more information, see Sharing Meta Data.

    To save a DTS package to Meta Data Services

    Enterprise Manager


    How to save a DTS package to Meta Data Services (Enterprise Manager)
    To save a DTS package to Meta Data Services

    In Data Transformation Services (DTS) Designer, on the Package menu, click Save.


    In the Location list, click Meta Data Services.


    Complete the rest of the required fields.


    Note If the package has already been saved to Microsoft® SQL Server™ 2000 Meta Data Services, the Save DTS Package dialog box will not appear, and a new version will be saved. If the package has been saved to a structured storage or a Microsoft Visual Basic® file, or to SQL Server, click Save As to save to Meta Data Services.



    See Also

    Saving a DTS Package to Meta Data Services


    A structured storage file.
    With this save option, you can copy, move, and send a package across the network without having to store the file in a SQL Server database. The structured storage format allows you to maintain multiple packages and multiple package versions in a single file.

    To save a DTS package to a structured storage file

    Enterprise Manager


    How to save a DTS package to a structured storage file (Enterprise Manager)
    To save a DTS package to a structured storage file

    In Data Transformation Services (DTS) Designer, on the toolbar, click Save.


    In the Location list, click Structured Storage File.


    Complete the rest of the required fields.


    Note If the DTS package has already been saved to a structured storage file, the Save DTS Package dialog box will not appear, and a new version will be saved. If the package has been saved to Microsoft® SQL Server™, SQL Server 2000 Meta Data Services, or a Microsoft Visual Basic® file, click Save As to save to a structured storage file.



    See Also

    Saving a DTS Package to a Structured Storage File


    A Microsoft Visual Basic® file.
    With this save option, you can programmatically customize a package created in DTS Designer or the DTS Import/Export Wizard. The option scripts out the package as Visual Basic code, and you can later open the Visual Basic file and modify the package definition in your development environment. For more information, see Managing DTS Package Programs.

    To save a DTS package to a Visual Basic file

    Enterprise Manager


    How to save a DTS package to a Visual Basic file (Enterprise Manager)
    To save a DTS package to a Visual Basic file using the DTS Import/Export Wizard

    On the Save, Schedule and Replicate Package screen, select the Save DTS Package check box, and then click Visual Basic File.
    To save a DTS package to a Visual Basic file using DTS Designer

    On the Package menu, click Save As.


    In the Location list, click Visual Basic File.


    In the File Name box, type the name of the Microsoft® Visual Basic® file.

    See Also

    Saving a DTS Package to a Visual Basic File


    When you save a package to SQL Server or to a structured storage file, you can secure the package with one or more passwords. When you save a package to Meta Data Services or as a Visual Basic file, the DTS package security options are not available. However:

    You can keep packages saved to Visual Basic files secure through a source code control system such as Microsoft Visual SourceSafe™.


    Meta Data Services contains its own security, which can be used to secure DTS packages.

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Which option to use when saving a DTS package?

    Originally posted by Joozh

    [list=1][*]SQL Server[*]SQL Server Meta Data Services (this appears dimmed/disabled - i don't know why



    Thanks.
    for this to work Meta Data Services and the repository database must be installed and operational on your server.
    check if it is installed and working.

    Regards,
    Harshal.

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    com structured storage file *.dts
    absolutely the best option. it allows a level of portabliltiy that local packages dont offer as well as storage for multiple packages
    and contian every version of the package you have ever developed.
    you can also store them in a network share and access them from many servers at the same time.
    the metadata services are cool if you want to retrieve the lineages for package load retrieval but in the overal scheme of things not very portable to other servers
    vbs files are okay but one package per file is bulky at times.
    local is okay but relies too much on the physical server for it's location
    what if the server is unavailable?

    here is some unsolicited advice
    dont use direct connections in your packages create *.udl files for your connection objects and share them out in a folder on the network for all of you sql servers to see.
    and set your packages to obtain props from udls.
    it makes all of your stuff very very modular.
    Last edited by Ruprect; 04-12-04 at 18:38.

  5. #5
    Join Date
    Oct 2003
    Posts
    163
    Hi harsal_in,

    Thanks for the reply but what I was looking for is as the say "the recommended approach/best practive". Maybe there is none and that's why you did a copy/paste from BOL.

    Let me admit that I usually go through BOL first and if the question is not answered/the explanation in BOL is not clear then I make use of this forum.

    Nevertheless, thanks for your reply - you generally are the first one to reply to most of my questions so double thanks there

    Also thanks to Ruprect for his reply. It's not very clear since I'm kind of a started in SQL server...

    One last question: What information/features/facilities do we get if we install the Meta Data Services and the repository database.

    Regards.

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    if i wasnt clear, let me be

    in my opinion the best way to save your packages are in com structured storage files these files end in a .dts extension and can be saved to disk.

    ik personally use these because you can store many related packages within a single file and each package contains every version of itself.
    so if you have edited the package and saved changes 6 times you will have 6 different versions of the package.
    this is to your benefit in case your last version contains inconsistencies, you can run the previous version.

    portability is an issue
    when you create a package locally or in metatdata services, you lose the flexibility to easily move that package and it is convenient to be able to do that in large server farms.

    the packages can also be accessed directly from a network location instead of storing them locally. this also aids in recovery of packages as well as their portability.

    i have used all 4 methods of storage and com files just happen to have worked out the best for me....
    I hope that I was more clear.

Posting Permissions

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