none
Problems with the location of the MsDtsSrvr.ini.xml file

    Question

  • I've installed a named instance of SQL Server 2005.  All seems to be well except I cannot access the SSIS packages.  According to the BOL, I should have a location: %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn.  No such location.  And there is no registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\ServiceConfigFile which as you know points to the location of the MsDtsSrvr.ini.xml file which apparantly is critical to the operation of SSIS.

    But I do have the file MsDtsSrvr.ini.xml file in  C:\Program Files\Microsoft SQL Server\90\DTS\Binn.

    I might also add there is an SQL 2000 instance (the default) on this same box.

    What have I done wrong and how would I go about fixing it?

    I would appreciate any help.
    Old COBOL programmer
    Tuesday, January 06, 2009 11:05 PM

Answers

  • Hi Jay,

    The default location you found on the hard drive is probably right...
        C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

    You can edit that file in notepad.

    You must have found the SQL 2008 article. The trick is, within every 2008 article, if there is an equivalent 2005 article, there is a hyperlink in the upper right which says "Other versions are also available". The BOL article for SQL 2005 is http://msdn.microsoft.com/en-us/library/ms137789(SQL.90).aspx

    Change the . in your xml file to ServerName\InstanceName or .\InstanceName

    As you've already found, SSIS 2005 is not going to be able to save SSIS packages into your SQL 2000 default instance, so the . that's in the XML file by default (local default instance) won't work.

    SSIS 2005 isn't smart enough at install time to fix this automatically, but 2008 is a bit smarter. Hope we can automate that in the future to avoid the manual configuration to save folks the confusion.

    Good luck, Jason H
    Wednesday, January 07, 2009 6:20 AM

All replies

  • Please elaborate on "cannot access the SSIS packages". Do you mean that you cannot see the SSIS server in SSMS? Keep in mind that the SSIS is not a named instance and has the same name as the server.
    Tuesday, January 06, 2009 11:32 PM
  • Jay Nichols said:

    I've installed a named instance of SQL Server 2005.  All seems to be well except I cannot access the SSIS packages.  According to the BOL, I should have a location: %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn.  No such location.  And there is no registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\ServiceConfigFile which as you know points to the location of the MsDtsSrvr.ini.xml file which apparantly is critical to the operation of SSIS.

    But I do have the file MsDtsSrvr.ini.xml file in  C:\Program Files\Microsoft SQL Server\90\DTS\Binn.

    I might also add there is an SQL 2000 instance (the default) on this same box.

    What have I done wrong and how would I go about fixing it?

    I would appreciate any help.


    Old COBOL programmer


    The folders with 100 in the name are for when using SQL 2008. So tje BOL you are reading is for SQL 2008. Same goes for the registry setting. In your case you should look under: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90

    Now up to the problem. Where do you have your SSIS packages deployed? If you used SQL server deployment you can find your packages with: "SQL Server Management Studio".

    SSIS Tasks Components Scripts | http://www.cozyroc.com/
    Wednesday, January 07, 2009 12:20 AM
  • Hi Jay,

    The default location you found on the hard drive is probably right...
        C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

    You can edit that file in notepad.

    You must have found the SQL 2008 article. The trick is, within every 2008 article, if there is an equivalent 2005 article, there is a hyperlink in the upper right which says "Other versions are also available". The BOL article for SQL 2005 is http://msdn.microsoft.com/en-us/library/ms137789(SQL.90).aspx

    Change the . in your xml file to ServerName\InstanceName or .\InstanceName

    As you've already found, SSIS 2005 is not going to be able to save SSIS packages into your SQL 2000 default instance, so the . that's in the XML file by default (local default instance) won't work.

    SSIS 2005 isn't smart enough at install time to fix this automatically, but 2008 is a bit smarter. Hope we can automate that in the future to avoid the manual configuration to save folks the confusion.

    Good luck, Jason H
    Wednesday, January 07, 2009 6:20 AM
  • Many thanks, Jason.  I'm working with named instances for the first time.  Also the hint about the reference to previous versions is helpful.  I appreciate you taking the time to help me.
    Old COBOL programmer
    Wednesday, January 07, 2009 2:20 PM
  • Similar problem here. I can't find that file anywhere in my folders. I have SSIS 2008. My question is at -

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1ee426a4-8c72-45a0-90f8-d1651b65f65f/cannot-find-the-file-msdtssrvrinixml

    Wednesday, September 25, 2013 12:09 AM
  • Similar problem here. I can't find that file anywhere in my folders. I have SSIS 2008. My question is at -

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1ee426a4-8c72-45a0-90f8-d1651b65f65f/cannot-find-the-file-msdtssrvrinixml

    Wednesday, September 25, 2013 12:09 AM