Modification of MsDTSSrvr.ini.xml does not work in June CTP

Hi,

I thought msdtssrvr.ini.xml controls package browsing, registration, and general management within SSMS. It is controlled by the HKLM\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile registry key.

I've attempted to modify this config file so I can add some custom folders to group packages logically but they all failed. It seems that SSMS totally ignores the config file and only load File System and MSDB sub-folders under Stored Packages folder in Object Explorer. Below is a sample of changes I made to the config file:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>Dev Packages</Name>
<StorePath>c:\SSIS</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
Any ideas?

Thanks
Haidong Ji
http://www.sqlservercentral.com/columnists/hji/Did you restart the service?|||

Aha, that was it. After I restarted the service, it did read the additional folders I added to the config file. Darn it, I should have tried it myself;) But again, this is the beauty of community.

Thanks a lot Kirk. You made my day;) By the way, I also enjoyed your blogs. They are very informative.

Haidong Ji

|||

Hi,

1. How do i know if i'm using June CTP?

2. I'd like to change the default file system deployment folder when i dblclick the deploymentmanifest file from the '<programfiles>\MSSQL\DTS\packages...' to 'C:\SSIS\Packages'. Is this config file the file to edit?

I tried changing '..\Packages' to 'C:\SSIS\' and restarted my service. When i clicked the manifest and chose file system, the default was still '...DTS\Packages...'.

Need some help.

Modification of MsDTSSrvr.ini.xml does not work in June CTP

Hi,

I thought msdtssrvr.ini.xml controls package browsing, registration, and general management within SSMS. It is controlled by the HKLM\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile registry key.

I've attempted to modify this config file so I can add some custom folders to group packages logically but they all failed. It seems that SSMS totally ignores the config file and only load File System and MSDB sub-folders under Stored Packages folder in Object Explorer. Below is a sample of changes I made to the config file:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>Dev Packages</Name>
<StorePath>c:\SSIS</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
Any ideas?

Thanks
Haidong Ji
http://www.sqlservercentral.com/columnists/hji/Did you restart the service?|||

Aha, that was it. After I restarted the service, it did read the additional folders I added to the config file. Darn it, I should have tried it myself;) But again, this is the beauty of community.

Thanks a lot Kirk. You made my day;) By the way, I also enjoyed your blogs. They are very informative.

Haidong Ji

|||

Hi,

1. How do i know if i'm using June CTP?

2. I'd like to change the default file system deployment folder when i dblclick the deploymentmanifest file from the '<programfiles>\MSSQL\DTS\packages...' to 'C:\SSIS\Packages'. Is this config file the file to edit?

I tried changing '..\Packages' to 'C:\SSIS\' and restarted my service. When i clicked the manifest and chose file system, the default was still '...DTS\Packages...'.

Need some help.

Model Designer - ODBC Data Source

I want to create a Model against an ODBC data source.

This is not possible in the June CTP.

Will this facility be available in a future release?

Thanks,
JoeI've raised this as a call with Microsoft through our Partner Program.

Will post the result here when I get an answer.|||As promised here is Microsoft's reply:

Please find the answers to your queries below:

Query:

=======

You want to be able to create an Ad-hoc Report Model using the Model Designer in Visual Studio 2005.

You want to be able to use an ODBC data source for this model - currently the only option is to use the “SQL Client Data Provider”.

So you need to know whether this will be addressed in the final release of SQL Server 2005 (or any CTPs of SQL Server 2005 before then).

Solution:

==========

As of now only SQL and Analysis Services are directly supported as a data source for Report Builder.

However you can connect to the ODBC data source using SQL or Analysis Services. We have the following two ways to get this working:

Option I:

-

UDM (Unified Dimensional Model) is essentially a way that SQL Analysis Services 2005 can combine multiple sources of data into one model. Let’s say your “Sales” information is one an Oracle data store, your “HR” info is in SQL, and your “Inventory” is in DB2 – You would build a UDM which includes all three of these data sources, and then you could report against the UDM as if ALL of the information lived inside Analysis Services.

So, by using a UDM to point to the ODBC data source, you enable Report Builder to get at the data too, since Report Builder CAN report against Analysis Services.

Option II:

--

You could try this but the performance expected is not very good with this option:

1. create linked servers in order to reference the ODBC (or other) data sources

2. create a layer of views in a SQL Server database accessing the other database through the linked server mechanism. You could use just simple views to expose the contents of the underlying tables with 'select *'. For example:
create view MyODBCTableView as select * from LinkedServerName..SchemaOwner.TableX

3. build SQL Server views on top of these views as desired

4. build the Report Model against these SQL Views

5. use Report Builder to query the OBDC data source through this 'proxy'

The following link is useful for more information and resources on SQL Server 2005:

http://www.microsoft.com/uk/partner/sol_and_products/servers/sql/

|||Unfortunately the chaps from Partner Tech Support were misinformed.

This is a reply from MSAS newsgroup :

AS2005 is not going to support ODBC data source. However, you can try to
create a data source in SSIS project. Then, add the data source into new AS
project. You should be able to create DSV, cube and dimension, etc.

However, since this feature is not supported. Believe that this area has not
been tested enough and this may break engine or not working probably. It is
"AS IS" if you really want to use it.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Ken Kwok
SQL Server Analysis Services

Mixed mode to Windows mode without trace....

Hi folks,

I have a SQL 2005 OTC. CTP version running on Windows 2003 server.

I would like to find out how the SQL server option changed to Windows Authentication mode from Mixed mode over the weekend. From the SQL log, I don't see when it changed. I would like to see Date/Time and client IP. If I can see User ID (windows) that would be great. Where I can find these info in SQL server?

Thank you in advance...

SHJ

If you were not explicitly auditing server logins during that period, you cannot obtain this information later. Also, an authentication mode change will not take effect until the server is restarted. Was your server restarted over the week-end? You should be able to see this from the errorlogs because a new errorlog would be started.

Thanks
Laurentiu

Mixed mode to Windows mode without trace....

Hi folks,

I have a SQL 2005 OTC. CTP version running on Windows 2003 server.

I would like to find out how the SQL server option changed to Windows Authentication mode from Mixed mode over the weekend. From the SQL log, I don't see when it changed. I would like to see Date/Time and client IP. If I can see User ID (windows) that would be great. Where I can find these info in SQL server?

Thank you in advance...

SHJ

If you were not explicitly auditing server logins during that period, you cannot obtain this information later. Also, an authentication mode change will not take effect until the server is restarted. Was your server restarted over the week-end? You should be able to see this from the errorlogs because a new errorlog would be started.

Thanks
Laurentiu

Missing VarBinary(MAX) filestream storage attribute

Hi
I found the filestream attribute in Beta 1 new features list, but
cannot find any reference to it in the CTP documentation. It allows
direct access to word documents stored in a VarBinary(MAX) column. By
direct access I mean a file path\name to the file as stored in the
database using SQL Server FileStream Agent, also this removes the 2GB
restriction on file size.
Thanks in Advance
vihsFilestream was cut from the SQL Server 2005 shortly after Beta 1.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"vihs" <sanantram@.yahoo.com> wrote in message
news:1122310275.649266.69620@.g43g2000cwa.googlegroups.com...
> Hi
> I found the filestream attribute in Beta 1 new features list, but
> cannot find any reference to it in the CTP documentation. It allows
> direct access to word documents stored in a VarBinary(MAX) column. By
> direct access I mean a file path\name to the file as stored in the
> database using SQL Server FileStream Agent, also this removes the 2GB
> restriction on file size.
> Thanks in Advance
> vihs
>

Missing SqlDataReader.GetSqlMetaData() in April CTP

Where did the GetSqlMetaData method go? I am trying to compare the SQL data types of two different columns to ensure compatibility prior to data transfer. The closest thing I can find in the April CTP is the DataReader.GetSchemaTable() which describes the columns' data types in .NET data types, not SQL data types. Any suggestions?

SMX_Mark wrote:

Where did the GetSqlMetaData method go? I am trying to compare the SQL data types of two different columns to ensure compatibility prior to data transfer. The closest thing I can find in the April CTP is the DataReader.GetSchemaTable() which describes the columns' data types in .NET data types, not SQL data types. Any suggestions?

I believe it was cut at the same time as the merge between the server and client provider happened.
Niels

Missing SQL Server Project template

I have VS2005 Beta 2 (build 8.0.50215.44) and the June CTP of SQL Server 2005 Developer Edition installed.

Within VS2005, when I go to File -> New Project, I don't have the "Database" project type under either VB or C#. Which also means that the "SQL Server Project" template is not being offered.

Is there a manual way of adding the template, or some other workaround/solution?

Which SKU of Visual Studio are you using? Visual Studio Express does not have SQL Server project support.

Thanks,
-Vineet.

|||It's the full version of Visual Studio 2005, Beta 2, that was released in mid April.|||Actually database projects are not availble in VS Express and VS Standard editions. It sounds like that is what you have. Can you confirm?|||Which editions of VS.Net 2005 do have the "SQL Server" project templates? Or can they be downloaded from somewhere? (I have not been able to find such a place...)
Many thanks.
|||Nevermind. I have VS.Net 2005 Beta2 and didn't notice that if I expanded the Visual C# under the project types tree, there was a database node which contained the "SQL Server" project template.
Take care.