locked
Stand alone for DTS package RRS feed

  • Question

  • Experts,

    Do we have any stand alone tool were I can open my DTS package?

    This is because I don't have installed SQL Server 2000. I have already have sql server 2005 (SSIS, SSRS) as default instance & sql server 2008 R2 ( SSAS, SSRS, SSIS ) as named instance.

    Please help me out. We need to open the DTS package understand the logic and migrate that package to 2008 SSIS for further analysis.

    Thanks in advnace.

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Wednesday, August 3, 2011 2:28 PM

Answers

  • Sorry for the delay (needed to step out).

    The messages above must be considered "normal" they are of information type, then once you have the DTS packages upgraded to SSIS (2005 I can see) you need now to create a proj in BIDS 2005 and edit-open each package to set the conn strings, verify, test run, etc.


    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by Eileen Zhao Monday, August 15, 2011 6:57 AM
    Wednesday, August 3, 2011 5:22 PM

All replies

  • Hello _Kumar,

    You can open the DTS (SQL Server 2000) packages in SSMS after installing
    Microsoft SQL Server 2000 DTS Designer Components from http://www.microsoft.com/download/en/details.aspx?id=15748

    But to migrate them you need BIDS.


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, August 3, 2011 2:44 PM
  • Hi ArthurZ,

    Wow thats perfect, I installed it and I was able to open the DTS package under DTS 2000 Package Designer from,

    1. Open Sqlwb

    2. Connected to the Database Engine

    3. Underneath "Management" folder -> "Legacy" folder -> Right click on "Data Transformation Services" folder --> Click on "Open Package" and
        selected my .DTS file which opened the package.

    Upto here everything is working fine, now my question is How to migrate to SSIS 2005 or to SSRS 2008 R2 version.

    You mentioned that "But to migrate them you need BIDS", yes I do have BIDS for both 2005 as well as for 2008 R2, Is their any way where I can declare migrate this DTS package to SSIS 2008 R2 package or I have to go through DTS -> SSIS 2005 -> SSIS 2008 R2.

    Please help me out.

    Thank You for your prompt responses.

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Wednesday, August 3, 2011 4:15 PM
  • Hi Again,

    I started using BIDS 2005 to migrate this DTS packgae to SSIS using below notes from the link:-
    http://www.simple-talk.com/sql/ssis/dts-to-ssis-migration/

    •On my PC I navigated to E:\Program Files\Microsoft SQL Server\90\DTS\Binn and located the executable DTSMigrationWizard.exe. 
    
    •From within an SSIS project, right click on the SSIS packages folder and click on Migrate DTS 2000 Package. 
    
    •In SSMS, expand the database server, expand management, and then expand legacy. Right click on the data transformation folder.


    But the wizard is getting stopped with below log errors:-

    LogID=1
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_WARN
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Not all the connections from SQL Server 2000 are migrated to the main SQL Server 2005 package. For example, Text (Source) and Text (Destination) are not migrated to the main package.
    
    LogID=2
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_WARN
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Not all tasks from SQL Server 2000 and SQL Server 7.0 are migrated to SQL Server 2005. For example, the migration of the Analysis Services Processing task and the Data Mining Prediction task is not supported.
    
    LogID=3
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_WARN
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.ConnectionsMigrationModule.ConnectionsMigration
    #Message=SSIS Migration Wizard could not migrate connection "DW DB" while migrating package "package-name".
    
    LogID=4
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_MSG
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Finished migrating connections for package: package-name
    
    LogID=5
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_MSG
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Started DTSExecuteSQLTask task migration for package: package-name
    
    LogID=6
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_MSG
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Completed DTSExecuteSQLTask task migration for package: package-name
    
    LogID=7
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_MSG
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Started DTSDataPumpTask task migration for package: package-name
    
    LogID=8
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_MSG
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Completed DTSDataPumpTask task migration for package: package-name
    
    LogID=9
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_MSG
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Started DTSDynamicPropertiesTask task migration for package: package-name
    
    
    LogID=10
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_MSG
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Completed DTSDynamicPropertiesTask task migration for package: package-name
    
    LogID=11
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_MSG
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Started DTSActiveScriptTask task migration for package: package-name
    
    LogID=12
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_MSG
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Completed DTSActiveScriptTask task migration for package: package-name
    
    LogID=13
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_MSG
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Started DTSActiveScriptTask task migration for package: package-name
    
    LogID=14
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_MSG
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Completed DTSActiveScriptTask task migration for package: package-name
    
    LogID=15
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_MSG
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=Finished migrating tasks for package: package-name
    
    LogID=16
    #Time=12:27 PM
    #Level=DTSMW_LOGLEVEL_ERR
    #Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
    #Message=System.Runtime.InteropServices.COMException (0x800403EB): Step 'DTSStep_DTSExecuteSQLTask_1' was not found.
      at DTS.Steps.Item(Object Index)
      at Microsoft.SqlServer.Dts.MigrationWizard.DTS8HelperUtility.Application.GetPrecedenceConstraints(DTS8Package pkg)
      at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.StartMigration(PackageInfo pInfo)


    Can anybody help me out for further going?

    Thanks

    Kumar


    Please do let us know your feedback. Thank You - KG, MCTS
    Wednesday, August 3, 2011 4:37 PM
  • Sorry for the delay (needed to step out).

    The messages above must be considered "normal" they are of information type, then once you have the DTS packages upgraded to SSIS (2005 I can see) you need now to create a proj in BIDS 2005 and edit-open each package to set the conn strings, verify, test run, etc.


    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by Eileen Zhao Monday, August 15, 2011 6:57 AM
    Wednesday, August 3, 2011 5:22 PM